SQL selection join help

Question

I'm having problems figuring out a query for this scenario. I have two tables I want to use in this query, they are like this:

Units
  ID
  Other Data
People
  ID
  UnitID  <-- fk to Units
  Other Data

This is what I want to do:
I want to select all the units that do NOT have a row in the People table linked to them. How can I do this?

Answer 1

SELECT * FROM Units  
WHERE NOT EXISTS  
(SELECT * FROM People WHERE UnitID = Units.ID)

Answer 2

SELECT Units.* FROM Units LEFT JOIN People ON People.UnitID = Units.ID WHERE People.ID IS NULL

or alternatively

SELECT Units.* FROM Units WHERE NOT EXISTS (SELECT 1 FROM People WHERE People.UnitID = Units.ID)

or even

SELECT Units.* FROM Units WHERE Units.ID NOT IN (SELECT UnitID FROM People)

Answer 3

SELECT * FROM Units
WHERE ID NOT IN (SELECT UnitID FROM People)

Answer 4

You can use a not in (select...)

SELECT ID
FROM Units
WHERE ID NOT IN (SELECT UnitID FROM People);

Answer 5

SELECT U.*
FROM Units U
WHERE NOT EXISTS (
   SELECT 1
   FROM People P
   WHERE U.ID = P.UnitID
)

Please note that this is called an (anti) semi-join. It is an actual join and is not a correlated subquery.

Another method commonly used is:

SELECT U.*
FROM
   Units U
   LEFT JOIN People P ON U.ID = P.UnitID
WHERE
   P.UnitID IS NULL

Note that additional criteria on the join (say you wanted to only join to people who were Active) need to be in the join clause. It won't work to say WHERE P.UnitID IS NULL AND P.Active = 1.

In my experience, each of the different queries can prove to be the performance winner depending on the actual execution plan chosen,. The way the engine uses statistics to predict row counts can make it choose sub-optimal execution plans for some queries, even when statistics are properly updated.

Note: using "SELECT 1" in your semi-joins instead of "SELECT *" will save some cycles during query compilation, since the * is actually expanded out to the column list, then later dropped.