Sql Count(*) from multiple tables but intersection should come-CodePudding

Select distinct(job_id) from A where condition
Select distinct(job_id) from B where condition
Select distinct(job_id) from C where condition

Here may be 1,2 and 3 have some common jobId What I need is count of all job_id which may not get repeated.

CodePudding user response：

With UNION ?

Select job_id from A where condition
UNION
Select job_id from B where condition
UNION
Select job_id from C where condition

CodePudding user response：

I believe EXCEPT is what you might be looking for here which provides a disjoint result set across multiple queries. This could probably be handled with JOIN logic also but it's a little hard to tell with the limited detail in the question.

Here is the Postgres documentation on the EXCEPT keyword.

WITH data AS
(
    SELECT job_id FROM A WHERE condition
    EXCEPT
    SELECT job_id FROM B WHERE condition
    EXCEPT
    SELECT job_id FROM C WHERE condition
)
SELECT COUNT(*) FROM data;

Here is a solution that works but is not scalable. I'm hesitant to post it because I feel like it's ugly and there must be a better way but maybe this will point you in the right direction.

WITH a_b_dups AS
(
  SELECT job_id FROM a
  INTERSECT
  SELECT job_id FROM b
), b_c_dups AS
(
  SELECT job_id FROM b
  INTERSECT
  SELECT job_id FROM c
), a_c_dups AS
(
  SELECT job_id FROM a
  INTERSECT
  SELECT job_id FROM c
), dups AS
(
  SELECT job_id FROM a_b_dups
   UNION
  SELECT job_id FROM b_c_dups
   UNION
  SELECT job_id FROM a_c_dups
), uniques AS
(
  SELECT job_id FROM a
   UNION
  SELECT job_id FROM b
   UNION
  SELECT job_id FROM c
  EXCEPT
  SELECT job_id FROM dups
)
SELECT COUNT(*) FROM uniques;

DBFiddle to show a working example.