选择子元素为零的行(联接)

Question

我有两张表。这两个表之间存在约束。

Table1 : Table2 = 1 : n

我想选择在Table2中没有条目的Table1元素的键。

示例：

|    Table 1         |
|Key |Col1 |Col2 |...|
|0001|.... |.... |...|
|0002|.... |.... |...|
|0003|.... |.... |...|
|0004|.... |.... |...|
|    Table 2         |
|Tab1|Col1 |Col2 |...|
|0001|.... |.... |...|
|0001|.... |.... |...|
|0003|.... |.... |...|
|0002|.... |.... |...|
Desired output:
0004

该输出是table1中唯一在table2中没有子条目的条目。

我试过很多不同的选择，比如：

SELECT 
    Key
    FROM Table1
    WHERE 
        ( SELECT COUNT(*) FROM Table2 
            WHERE Table1~Key = Table2~Tab1 
            GROUP BY Table2~Tab1 )
SELECT COUNT(Table2)
    Key
    FROM Table1
    INNER JOIN Table2 
        ON Table1~Key = Table2~Tab1.
...

但它们都不起作用。是否有人得到ABAP的有效查询？

Answer 1

select * from table1 where key not in (select tab1 from table2)

Answer 2

尝试此查询

select key from table1 where not EXISTS (SELECT *
              FROM table2
              WHERE table1.key=table2.tab1);

Answer 3

尝试以下操作，

SELECT Key FROM Table1
WHERE key not in (SELECT distinct tab1 FROM Table2)

Answer 4

无子选择的解决方案：

SELECT t1~Key FROM Table1 as t1
LEFT JOIN Table2 as t2
WHERE t2~Key is null