查找表中不存在的值

Question

假设我有一张桌子

ID   ACCNT   DESC
1    123     aaa
2    234     bbb
3    345     ccc

我有一张账号为123,345,555,777的清单。

这样我就能得到一个列表

SELECT * FROM MY_TABLE WHERE ACCNT IN (...my list...)

现在我想完成相反的事情。

从我的列表中，我想要一个查询来显示不在表中的帐号。

因此，在我的例子中，我想报告表中不存在555和77。

如何用Oracle SQL编写此查询？

Answer 1

select column_value as missing_num
from   table (sys.odcinumberlist (123,345,555,777))
where  column_value not in (select accnt from my_table);

Answer 2

以下方法可能适用于Oracle。您可以使用UNION创建子查询，该查询有一个包含帐户ID列表的列。然后，您可以将其LEFT JOIN到您的表中，并隔离与MY_TABLE中的任何内容不匹配的ID。

SELECT t1.ACCNT
FROM
(
    SELECT 123 AS ACCNT FROM DUAL
    UNION ALL
    SELECT 345 FROM DUAL
    UNION ALL
    SELECT 555 FROM DUAL
    UNION ALL
    SELECT 777 FROM DUAL
) t1
LEFT JOIN MY_TABLE t2
    ON t1.ACCNT = t2.ACCNT
WHERE t2.ACCNT IS NULL

这种方法的一个优点是，它实际上并不创建查询后可能不想要的表。

Answer 3

这取决于您的帐户号码列表的来源。例如，可以使用集合类型和表运算符：

-- Test case setup
CREATE TABLE ACCNT(ID,ACCNT,DESCR) AS
SELECT 1,123, 'aaa' FROM DUAL UNION ALL
SELECT 2,234, 'bbb' FROM DUAL UNION ALL
SELECT 3,345, 'ccc' FROM DUAL;

-- Create a collection type to hold account numbers
CREATE TYPE tab_numbers AS TABLE OF NUMBER;
/

-- Select account numbers that does not occur on the table
SELECT *
  FROM TABLE(TAB_NUMBERS(123,345,555,777))
 WHERE COLUMN_VALUE NOT IN (SELECT ACCNT FROM ACCNT);