найдите значения, которые не принадлежат 2 таблицам, используя SQL Server 2005

#sql #sql-server

Вопрос:

У меня есть таблица дочерних продуктов, в которой есть столбец с именем ItemId, который ссылается на столбец с именем ItemId в таблице Products.

Мне нужно иметь возможность быстро перечислить все идентификаторы элементов в дочерних элементах, которых нет в продуктах.

Каков наиболее эффективный способ сделать это?

Ответ №1:

 SELECT ItemID
FROM ChildItems
WHERE ItemID NOT IN (SELECT ItemID FROM Products)

NOT IN или EXISTS для этого будет выполняться быстрее, чем a JOIN , поскольку оба они имеют короткое замыкание.

Ответ №2:

 select c.ItemID 
from ChildProducts c left outer join Products p on c.ItemID = p.ItemID 
where p.ItemID IS NULL

РЕДАКТИРОВАТЬ: ответ JNK был бы лучшим выбором, см. Его Ниже.

Ответ №3:

Предложение NOT EXISTS выполнит работу:

     SELECT  *
    FROM    ChildItems c
    WHERE   NOT EXISTS (SELECT 'x' FROM Products p WHERE p.ItemID = C.ItemID)

Извините — синтаксическая ошибка, допущенная в спешке при ответе. Спасибо @JNK.

Ответ №4:

Попробуйте что-то вроде этого:

 SELECT  *
FROM    ChildItems c
WHERE   c.ItemID NOT IN(SELECT p.ItemID FROM Products p)

Если вы хотите удалить потерянные записи, вы можете сделать это:

 DELETE FROM ChildItems c
WHERE  c.ItemID NOT IN (SELECT p.ItemID FROM Products p)

Вот еще один способ найти потерянные записи:

 SELECT  *
FROM    ChildItems c
        LEFT OUTER JOIN Products p
           ON c.ItemID = p.ItemID
WHERE    p.ItemID IS NULL

Редактировать

Предполагая, что вы хотите устранить эту проблему после удаления потерянных записей, я бы предложил добавить ChildItems в таблицу внешний ключ и использовать каскадное удаление при удалении записей, которые удаляются из Products таблицы.

1. … итак, теперь это то же самое, что и мой ответ, за исключением опубликованного 2 минуты спустя?

2. Я уверен на 100%. Один из многих сообщений об этом от профессионалов: sqlinthewild.co.za/index.php/2010/03/23 /…

3. Наши ответы не идентичны. Мой ответ дает два варианта и вариант удаления потерянных записей. Существует всего несколько способов выполнения запроса, поэтому, очевидно, наши ответы будут похожими.

4. Джеймс это было действительно хорошее короткое обсуждение почему вы удалили свои комментарии?

5. @CodeMonkey12: Потому что я хотел, чтобы мой последний комментарий был виден людям, просматривающим страницу, потому что это был самый важный из моих комментариев. Очевидно, что здесь действует какая-то политика, учитывая, что мы оба опубликовали правильные ответы, и мой голос был отклонен, в то время как его голос был повышен. Я могу придумать 3 способа написания запроса — LEFT JOIN, NOT IN и NOT EXISTS . Очевидно, что ответы будут иметь некоторое сходство. Что угодно… на самом деле это не так важно.

Ответ №5:

Вы также можете использовать часто забываемый EXCEPT оператор для выполнения этого антисоединения.

 SELECT ItemID
FROM ChildItems
EXCEPT
SELECT ItemID 
FROM Products