How can I make an SQL expression that finds unrelated records?

Question

I have two tables:

tblCountry (countryID, countryCode)

tblProjectCountry(ProjectID, countryID)

The table tblCountryis a list of all countries with their codes, and the table tblProjectCountryassociates certain countries with certain projects. I need an SQL statement that gives me a list of countries with their country code that DO NOT have an associated table entry tblProjectCountry. so far i got here:

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

The above expression parses as correct, but does not give the exact result I'm looking for. Can anyone help?

Answer 1

It works?

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

Answer 2

Another alternative:

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

,

, EXISTS (. )

SQL Server , , . RDMS .

Answer 3

, , .

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

.

In my test for MS SQL 2005, there was no significant difference between the first and second query for a table with ~ 250 countries and ~ 5000 projects. However, on a table with more than 3 megabyte projects, the second version (using NOT EXISTS) performed much, much better.

So, as always, it's worth checking both versions.

Answer 4

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