Выберите значение, в котором оно не существует в другой таблице

У меня есть две таблицы

Таблица A:

Таблица B:

ID
1
2
3

У меня есть два запроса:

Я хочу выбрать все строки в таблице A, которые нет в таблице B, которая в этом случае является строкой 4.
Я хочу удалить все строки, которые нет в таблице B.

Я использую SQL Server 2000.

Ответы

Ответ 1

Вы можете использовать NOT IN:

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)

Однако, тем временем я предпочитаю NOT EXISTS:

SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)

Существуют и другие варианты, в этой статье очень хорошо описаны все преимущества и недостатки:

Должен ли я использовать NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT или NOT EXISTS?

Ответ 2

Для вашего первого вопроса есть как минимум три общих метода на выбор:

НЕ СУЩЕСТВУЕТ
NOT IN
LEFT JOIN

SQL выглядит следующим образом:

SELECT * FROM TableA WHERE NOT EXISTS (
    SELECT NULL
    FROM TableB
    WHERE TableB.ID = TableA.ID
)

SELECT * FROM TableA WHERE ID NOT IN (
    SELECT ID FROM TableB
)

SELECT TableA.* FROM TableA 
LEFT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL

В зависимости от используемой базы данных производительность каждого пользователя может отличаться. Для SQL Server (столбцы с нулевым значением):

NOT EXISTS и NOT IN предикаты - лучший способ поиска отсутствующих значений, если оба столбца не являются NULL.

Ответ 3

Это выберет 4 в вашем случае

SELECT ID FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)

Это удалит их

DELETE FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)

Ответ 4

select ID from A where ID not in (select ID from B);

или

select ID from A except select ID from B;

Ваш второй вопрос:

delete from A where ID not in (select ID from B);

Ответ 5

SELECT ID 
  FROM A 
 WHERE NOT EXISTS( SELECT 1
                     FROM B
                    WHERE B.ID = A.ID
                 )

Ответ 6

SELECT ID
  FROM A
 WHERE ID NOT IN (
      SELECT ID
        FROM B);

SELECT ID    
  FROM A a
 WHERE NOT EXISTS (
      SELECT 1 
        FROM B b
       WHERE b.ID = a.ID)

         SELECT a.ID 
           FROM A a    
LEFT OUTER JOIN B b 
             ON a.ID = b.ID    
          WHERE b.ID IS NULL

DELETE 
  FROM A 
 WHERE ID NOT IN (
      SELECT ID 
        FROM B)