Запрос SQL для поиска записи с идентификатором не в другой таблице

У меня есть две таблицы с обязательным первичным ключом в базе данных, и я хочу найти между ними непересекающийся набор. Например,

Table1 имеет столбцы (ID, Name) и примеры данных: (1 ,John), (2, Peter), (3, Mary)
Table2 имеет столбцы (ID, Address) и данные образца: (1, address2), (2, address2)

Итак, как мне создать SQL-запрос, поэтому я могу получить строку с идентификатором из Table1, которая не находится в Table2. В этом случае следует вернуть (3, Mary)?

Ps. Идентификатор является основным ключом для этих двух таблиц.

Спасибо заранее.

Ответ 1

Попробуйте это

SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)

Ответ 2

Используйте LEFT JOIN

SELECT  a.*
FROM    table1 a
            LEFT JOIN table2 b
                on a.ID = b.ID
WHERE   b.id IS NULL

Ответ 3

Быстрая альтернатива

Я провел несколько тестов (на Postgres 9.5), используя две таблицы с ~ 2M строк в каждой. Этот запрос ниже по крайней мере на 5 * лучше, чем другие предложенные запросы:

-- Count
SELECT count(*) FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2;

-- Get full row
SELECT table1.* FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2 JOIN table1 ON t1_not_in_t2.id=table1.id;

Ответ 4

Имея в виду пункты, сделанные в комментарии @John Woo comment/link выше, я обычно обрабатывал это:

SELECT t1.ID, t1.Name 
FROM   Table1 t1
WHERE  NOT EXISTS (
    SELECT TOP 1 NULL
    FROM Table2 t2
    WHERE t1.ID = t2.ID
)

Ответ 5

Существует три основных подхода к этому: not exists, not in not exists и left join/is null.

Осталось присоединиться к NULL

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

НЕ В

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

НЕ СУЩЕСТВУЕТ

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

Какой из них лучше? Ответ на этот вопрос может быть лучше разбить на крупных конкретных поставщиков СУБД. Вообще говоря, следует избегать использования select... where... in (select...) когда величина количества записей в подзапросе неизвестна. Некоторые поставщики могут ограничивать размер. Oracle, например, имеет ограничение 1000. Лучше всего попробовать все три и показать план выполнения.

В частности, форма PostgreSQL, план выполнения NOT EXISTS и LEFT JOIN/IS NULL одинаковы. Лично я предпочитаю вариант NOT EXISTS потому что он лучше показывает намерение. Ведь семантика в том, что вы хотите найти записи в A, которые не существуют в B.

Старое, но все же золото, специфичное для PostgreSQL: https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/

Ответ 6

SELECT COUNT(ID) FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For count


SELECT ID FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For results