SQL Query to find missing rows between two related tables

Question

I have two tables:

Table A

• ID
• ABC_ID
• VAL

Table B

• ID
• ABC_ID
• VAL

These two tables are directly related to each other through the ABC_ID column.

I want to find all the VAL column values in table A which are not present in table B for the same ABC_ID.

Answer 1

SELECT A.ABC_ID, A.VAL FROM A WHERE NOT EXISTS     (SELECT * FROM B WHERE B.ABC_ID = A.ABC_ID AND B.VAL = A.VAL) 

or

SELECT A.ABC_ID, A.VAL FROM A WHERE VAL NOT IN      (SELECT VAL FROM B WHERE B.ABC_ID = A.ABC_ID) 

or

SELECT A.ABC_ID, A.VAL LEFT OUTER JOIN B      ON A.ABC_ID = B.ABC_ID AND A.VAL = B.VAL FROM A WHERE B.VAL IS NULL 

Please note that these queries do not require that ABC_ID be in table B at all. I think that does what you want.