Как я могу сделать выражение SQL, которое находит несвязанные записи?

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

tblCountry (countryID, countryCode)
tblProjectCountry(ProjectID, countryID)

Таблица tblCountry представляет собой список всех стран с их кодами, а таблица tblProjectCountry связывает определенные страны с определенными проектами. Мне нужен оператор SQL, который дает мне список стран со своим кодом страны, которые НЕ имеют связанную запись в таблице tblProjectCountry. до сих пор я добрался сюда:

SELECT tblCountry.countryID, tblCountry.countryCode
FROM tblProjectCountry INNER JOIN
 tblCountry ON tblProjectCountry.countryID = tblCountry.countryID
WHERE (SELECT COUNT(ProjectID)
 FROM tblProjectCountry 
 WHERE (ProjectID = 1) AND (countryID = tblCountry.countryID)) = 0

Вышеприведенное выражение анализирует как правильное, но не дает точного результата, который я ищу. Может ли кто-нибудь помочь?

4 ответа

Это работает?

SELECT countryID, countryCode 
 FROM tblCountry 
 WHERE countryID NOT IN ( SELECT countryID FROM tblProjectCountry )


Другая альтернатива:

SELECT outerTbl.countryID, outerTbl.countryCode 
 FROM tblCountry AS outerTbl
 WHERE NOT EXISTS 
 (
 SELECT countryID FROM tblProjectCountry WHERE countryID = outerTbl.countryID
 )

Здесь используется то, что называется коррелированный подзапрос

Обратите внимание, что я также использую ключевое слово EXISTS (см. также)

В SQL Server НЕ СУЩЕСТВУЕТ, как правило, считается более эффективным. В других RDMS ваш пробег может меняться.


Существует, по крайней мере, два способа поиска несвязанных записей.

1. Используя LEFT JOIN

SELECT DISTINCT -- each country only once
 tblCountry.countryID,
 tblCountry.tblCountry 
FROM
 tblCountry 
 LEFT JOIN
 tblProjectCountry
 ON
 tblProjectCountry.countryID = tblCountry.countryID
WHERE
 tblProjectCountry.ProjectID IS NULL -- get only records with no pair in projects table
ORDER BY
 tblCountry.countryID

В качестве erikkallen упоминалось, что не работает.

2. Используя NOT EXISTS

Различные варианты использования NOT EXISTS или IN были предложены rohancragg и другими:

SELECT
 tblCountry.countryID,
 tblCountry.tblCountry 
FROM
 tblCountry 
WHERE
 -- get only records with no pair in projects table
 NOT EXISTS (SELECT TOP 1 1 FROM tblProjectCountry WHERE tblProjectCountry.countryID = tblCountry.countryID) 
ORDER BY
 tblCountry.countryID

В зависимости от вашей СУБД и размера таблиц стран и проектов обе версии могут работать лучше.

В моем тесте на MS SQL 2005 не было существенной разницы между первым и вторым запросом для таблицы с ~ 250 странами и ~ 5000 проектами. Однако на столе с более чем 3-мегабайтными проектами вторая версия (с использованием NOT EXISTS) выполнялась намного, намного лучше.

Так что, как всегда, стоит проверить обе версии.


SELECT... WHERE ID NOT IN (SELECT...)

licensed under cc by-sa 3.0 with attribution.