Thread: [SQL] Single SQL to find if any record exists across several tables
Hello, Using PostgreSQL 9.6.5 64bit running on Windows 10 64bit. There are no foreign key across tables in database. There is a table where company codes are kept. Most other tables includes company code value in a column. Column name is same for all tables which is "fk". Before deleting any company code row, I would like to be sure that it is not used in anywhere. Currently I am running a separate query for each table but that takes a lot of time and possibility to miss a table or two. Is it possible to build a single SQL statement and include all tables in it so that I receive single "count(fk)" sum across all tables? Several sample SQLs run at the moment are as follows: Select count(fk) from cari Select count(fk) from sipbaslik Select count(fk) from stkdepo Select count(fk) from stkkart Select count(fk) from stkhar Thanks & regards, Ertan Küçükoğlu -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Single SQL to find if any record exists across several tables
From
"David G. Johnston"
Date:
Is it possible to build a single SQL statement and include all tables in it
so that I receive single "count(fk)" sum across all tables?
Several sample SQLs run at the moment are as follows:
Select count(fk) from cari
Select count(fk) from sipbaslik
Select count(fk) from stkdepo
Select count(fk) from stkkart
Select count(fk) from stkhar
Without delving into dynamic SQL you can do either:
SELECT sum(count) FROM
(
SELECT count(fk) FROM can
UNION ALL
SELECT count(fk) FROM sipbaslik
) src
OR
SELECT
(SELECT count(fk) FROM can)
+
(SELECT count(fk) FROM sipbaslik)
David J.
Re: [SQL] Single SQL to find if any record exists across several tables
From
Feike Steenbergen
Date:
On 21 September 2017 at 14:45, Ertan Küçükoğlu <ertan.kucukoglu@1nar.com.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)