Как я могу сделать оператор 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

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


sql inner-join exists
person William Calleja    schedule 22.03.2010    source источник

Ответы (4)


arrow_upward
3
arrow_downward

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

SELECT countryID, countryCode 
  FROM tblCountry 
  WHERE countryID NOT IN ( SELECT countryID FROM tblProjectCountry )
person tim_yates    schedule 22.03.2010
comment
Хотя это работает, я бы сказал, что это не самый правильный ответ. По-видимому, вам никогда не следует использовать IN In SQL для JOIN с другой таблицей в соответствии с sqlservercode.blogspot.com/2007/04/ Мой ответ показывает, как вместо этого использовать EXISTS. - person rohancragg; 22.03.2010

arrow_upward
3
arrow_downward

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

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

При этом используется так называемый коррелированный подзапрос.

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

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

person rohancragg    schedule 22.03.2010
comment
Внутренний SELECT countryID FROM ... можно заменить на SELECT TOP 1 1 FROM.... Доказательство существования достаточно хорошее. - person Grzegorz Gierlik; 22.03.2010
comment
спасибо, я никогда не думал об этом, хотя всегда думал, что бессмысленно выбирать то, что никогда не «используется» - person rohancragg; 23.03.2010

arrow_upward
1
arrow_downward

Есть, по крайней мере, два способа найти несвязанные записи.

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) работала намного лучше.

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

person Grzegorz Gierlik    schedule 22.03.2010
comment
SQL-сервер не распознает это как анти-объединение, поэтому он будет вынужден выполнить левое соединение + фильтр, что может быть или не быть лучше, но, вероятно, намного хуже. - person erikkallen; 22.03.2010
comment
Истинный. Я проверил это на MS SQL 2005 с таблицей с 253 странами, присоединившимися к таблицам с ~ 5 тыс. строк и более 3 млн строк. В случае таблицы с более чем 3 миллионами строк LEFT JOIN работал намного медленнее. Однако для таблицы с 5 тыс. строк стоимость была аналогична версии с NOT EXISTS (SELECT TOP 1 1 FROM...), предложенной @rohancragg. - person Grzegorz Gierlik; 22.03.2010

arrow_upward
0
arrow_downward

ВЫБЕРИТЕ... ГДЕ ID НЕ В (ВЫБРАТЬ...)

person Axarydax    schedule 22.03.2010
comment
по-видимому, вы никогда не должны использовать IN In SQL для JOIN с другой таблицей в соответствии с sqlservercode.blogspot.com/2007/04/ - person rohancragg; 22.03.2010
comment
это смешно, я не должен использовать какую-то языковую функцию, потому что могу сделать логическую ошибку?! - person Axarydax; 22.03.2010
comment
Вы никогда не должны ничего делать, потому что я видел сообщение в блоге, в котором говорилось, что вы не должны этого делать. - person erikkallen; 22.03.2010