Re: Single SQL to find if any record exists across several tables

On 21 September 2017 at 14:45, Ertan Küçükoğlu <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr>
wrote:
>
> Before deleting any company code row, I would like to be sure that it is
not
> used in anywhere.

If you don't actually care about the count, but only if the value exists
(like the
subject suggests) you could do something like:

SELECT EXISTS (
SELECT FROM cari WHERE fk = $1
UNION ALL
SELECT FROM sipbaslik WHERE fk = $1
[UNION ALL ...]
);

This will stop executing as soon as it finds a record matching fk, it does
not have to scan subsequent tables if it hits on the first table.
Depending on your data set that might be very useful.

=# EXPLAIN ANALYZE
SELECT EXISTS (
SELECT FROM cari WHERE fk = 'a'
UNION ALL
SELECT FROM sipbaslik WHERE fk = 'a'
);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Result (cost=3.87..3.88 rows=1 width=1) (actual time=0.010..0.010 rows=1
loops=1)
InitPlan 1 (returns $0)
-> Append (cost=0.00..54.14 rows=14 width=0) (actual
time=0.009..0.009 rows=1 loops=1)
-> Seq Scan on cari (cost=0.00..27.00 rows=7 width=0) (actual
time=0.009..0.009 rows=1 loops=1)
Filter: (fk = 'a'::text)
-> Seq Scan on sipbaslik (cost=0.00..27.00 rows=7 width=0)
(never executed)
Filter: (fk = 'a'::text)