SQL SQL查询以查找两个相关表之间的缺失行

Question

提问by Fazal

I have two tables:

我有两个表：

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

这两个表通过ABC_ID列直接相互关联。

I want to find all the VALcolumn values in table A which are notpresent in table B for the same ABC_ID.

我想找到VAL表 A 中所有列值，这些值在表 B 中不存在，用于相同的 ABC_ID。

Answer 1

SELECT A.ABC_ID, A.VAL 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 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 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.

请注意，这些查询根本不需要表 B 中的 ABC_ID。我认为这就是你想要的。