0
I have 3 table A,B,C,D with the same columns and I'm trying to retrieve the 
user_id that are in table a and b but not c.

For instance:
Table A                       
user_id     closed acct
10102345     Yes
12456786     Yes

Table B             
user_id  closed acct  
10102345     Yes
12456786     Yes  

Table C
user_id  closed acct
45345696     No
45698788     Yes
12456786     Yes

Here is what the code that I try but it taking so long to execute: SELECT user_id FROM TableA WHERE user_id NOT IN (SELECT DISTINCT user_id
FROM TableB) OR user_id NOT IN (SELECT DISTINCT ID FROM Table C)

2 Answers 2

1
SELECT A.*, B.*
FROM A 
JOIN B ON A.user_id = B.user_id
WHERE NOT EXISTS ( SELECT NULL
                   FROM C
                   WHERE C.user_id = B.user_id )

or

SELECT A.*, B.*
FROM A 
JOIN B ON A.user_id = B.user_id
LEFT JOIN C ON C.user_id = B.user_id
WHERE C.user_id IS NULL
1

Depending on the rows number in tables TableA and TableB versus the rows number in table TableC, you can use the EXCEPT clause with SQL Server / TSQL :

Version 1 corresponding to your first remark :

I'm trying to retrieve the user_id that are in table a and b but not c.

WITH USERS (user_id) AS (SELECT user_id FROM TableA
                          UNION ALL
                         SELECT user_id FROM TableB)
SELECT DISTINCT user_id FROM USERS
EXCEPT 
SELECT user_id FROM TableC;

EDIT: Thanks to @ypercubeTM in the comments, the corresponding simplified SQL query :

( SELECT user_id FROM TableA 
  UNION 
  SELECT user_id FROM TableB
)
EXCEPT
  SELECT user_id FROM TableC ;

See 1 for the DISTINCT clause.

Version 2 corresponding to SQL Statement :

SELECT user_id FROM TableA WHERE user_id NOT IN (SELECT DISTINCT user_id FROM TableB) OR user_id NOT IN (SELECT DISTINCT ID FROM Table C)

(Which hasn't the same meaning as your first remark, this statement asks for user_ids from TableA that don't exist in TableB nor TableC)

WITH USERS (user_id) AS (SELECT user_id FROM TableB
                          UNION ALL
                         SELECT user_id FROM TableC)
SELECT user_id FROM TableA
EXCEPT 
SELECT DISTINCT user_id FROM USERS;

EDIT: and again the corresponding simplified SQL query :

SELECT user_id FROM TableA
EXCEPT
( SELECT user_id FROM TableC
  UNION
  SELECT user_id FROM TableB
) ;

See 1 for the DISTINCT clause.

If you can expect very few rows in :

  • (version 1) TableC proportionally to TableA + TableB
  • (version 2) TableB + TableC proportionally to TableA

This way will be very efficient on the performance side and not too costly on resources side. I recommend the same approach on other RDBMS (EXCEPT or MINUS clause) in theses cases, far better when applicable than NOT IN / NOT EXISTS / LEFT JOIN .. WHERE NULL.

In your case, as user_id is clearly the PK of theses 3 tables, i choose UNION ALL versus UNION in the WITH clause for performance. As we can't guess if user_id could be in TableA and also in TableB (version 1) / TableB and also in TableC (version 2), we have to DISTINCT1 them on the SELECT FROM USERS. So there is no gain to deduplicate them in the WITH clause.

EDIT: 1 As states @ypercubeTM in the comments, the DISTINCT has no use here in version 2, and would only have an effect on version 1 if there was some identical user_ids in TableA and TableB joined with an UNION ALL (no use of DISTINCT using UNION). The DISTINCT clause could be removed from the queries, i let them be here as a way to provide awareness on the data / DML on top of the schema / DDL.

3
  • You don't need CTEs, you know. Neither the DISTINCT. The 1st query for example can be simplified to: (SELECT user_id FROM TableA UNION SELECT user_id FROM TableB) EXCEPT SELECT user_id FROM TableC ; and similarly the 2nd query. Mar 15, 2021 at 22:39
  • @ypercubeᵀᴹ (Trademark, really ? ^^) For the intended readers, CTEs seems useful for clarity of the explanations here, don't you think ?. I'm still today a one liner lover even before Perl, but it seems better to prime readability in an answer, no ? (I agree there's no use of the DISTINCT here, but it is here a way to instructs readers about considering volume, granularity and nature of the data they're dealing with on a RDBMS. If you agree, i'll edit this answer to mention your correct point on this.)
    – Zilog80
    Mar 15, 2021 at 22:48
  • No, I don't disagree that CTE has its place. Just my preference for simple queries (these look simple enough for me), not to use them. You could add the simplified queries as options to use (without removing the CTE ones) Mar 15, 2021 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.