0

I have one table "Cells" with Primary key "Cell_ID". There are 160 ID's/records. There is one other table, for example "Customers" where I use a field "CellID", which is a table list using row source the field "Cell_ID" from the table "Cells".

I would like to create a query that will return me all the Cell_ID values that are not used on the Customers.CellId field.

My first thought was to use something like the following:

SELECT Cells.Cell_ID
FROM Cells
WHERE Cells.Cell_ID NOT IN (
SELECT Customers.CellID
FROM Customers);
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
ΝΙΚΟΣ ΠΑΠΑΣΤΕΦΑΝΟΥ
  • 55
  • 6

1 Answers1

1

Your method is conceptually okay. But there is a problem if Customers.CellId is ever NULL. If that is the case, then the query will return no rows.

For this reason, I recommend never using NOT IN with a subquery. Instead, use NOT EXISTS:

SELECT c.Cell_ID
FROM Cells as c
WHERE NOT EXISTS (SELECT 1
                  FROM Customers as cu
                  WHERE c.Cell_ID = cu.CellID
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786