Unmatched Records Query: Four Ways to Find Rows With No Match

An unmatched records query finds the rows in one table that have no partner in another. Plants that never sold and students who did not re-enroll are the classic cases. You expect activity for these rows, and none is there.

The Sample Tables

Six plants and six order lines give us something to match. Orchid, Bamboo and Mint never sold. Two details are on purpose: one order line has no plant, and another has no quantity. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Plants (PlantID INT PRIMARY KEY, PlantName VARCHAR(20) NOT NULL);
CREATE TABLE OrderLines (LineID INT PRIMARY KEY, PlantID INT NULL, Qty INT NULL);
INSERT INTO Plants (PlantID, PlantName) VALUES (1, 'Fern'), (2, 'Cactus'), (3, 'Orchid'), (4, 'Bamboo'), (5, 'Basil'), (6, 'Mint');
INSERT INTO OrderLines (LineID, PlantID, Qty) VALUES (1, 1, 2), (2, 1, 1), (3, 2, NULL), (4, 5, 3), (5, 5, 1), (6, NULL, 1);
GO

The Join That Hides Them

An INNER JOIN returns only plants with a matching order line. Orchid, Bamboo and Mint drop out, which is the opposite of what we want.

SELECT p.PlantName, l.LineID
FROM Plants AS p INNER JOIN OrderLines AS l ON l.PlantID = p.PlantID
ORDER BY p.PlantID, l.LineID;

A LEFT OUTER JOIN keeps every plant on the left. Where no order line matches, the columns from the right side come back as NULL.

SELECT p.PlantName, l.LineID
FROM Plants AS p LEFT OUTER JOIN OrderLines AS l ON l.PlantID = p.PlantID
ORDER BY p.PlantID, l.LineID;

That’s eight rows. Orchid, Bamboo and Mint each show NULL in LineID. This is the first half of the query.

Version 1: LEFT JOIN, Then Test for NULL

The second half is a WHERE clause that keeps the NULL rows. Test the key of the right-hand table, because a key can’t be NULL in a real match.

SELECT p.PlantID, p.PlantName
FROM Plants AS p LEFT OUTER JOIN OrderLines AS l ON l.PlantID = p.PlantID
WHERE l.LineID IS NULL
ORDER BY p.PlantID;

The query returns Orchid, Bamboo and Mint. Pick the column with care. This version tests Qty instead, and Qty allows NULL.

SELECT p.PlantID, p.PlantName
FROM Plants AS p LEFT OUTER JOIN OrderLines AS l ON l.PlantID = p.PlantID
WHERE l.Qty IS NULL
ORDER BY p.PlantID;

Now Cactus appears too. Its order line exists, but the quantity is NULL. The query can’t tell “no line” from “line without a quantity”.

Version 2: NOT EXISTS

NOT EXISTS reads like the question: show plants where no order line exists for them. It has no column to pick, so the Qty mistake can’t happen.

SELECT p.PlantID, p.PlantName
FROM Plants AS p
WHERE NOT EXISTS (SELECT 1 FROM OrderLines AS l WHERE l.PlantID = p.PlantID)
ORDER BY p.PlantID;

Version 3: NOT IN and the NULL Trap

NOT IN looks the simplest. It compares each plant with a list of plant numbers from the order lines.

SELECT PlantID, PlantName FROM Plants
WHERE PlantID NOT IN (SELECT PlantID FROM OrderLines)
ORDER BY PlantID;

It returns no rows at all. One order line has a NULL plant, so the list contains a NULL. SQL Server can’t say that a plant is different from an unknown value, so no row passes the test. Remove the NULL from the list and it works.

SELECT PlantID, PlantName FROM Plants
WHERE PlantID NOT IN (SELECT PlantID FROM OrderLines WHERE PlantID IS NOT NULL)
ORDER BY PlantID;

Version 4: EXCEPT

EXCEPT subtracts one list from another. A reader asked why EXCEPT wasn’t in the old version of this post. It’s a fair question, because EXCEPT treats NULL values as equal and isn’t fooled by the NULL in the list.

SELECT PlantID FROM Plants
EXCEPT
SELECT PlantID FROM OrderLines
ORDER BY PlantID;

It returns the keys 3, 4 and 6. EXCEPT compares whole rows, so you only get the columns you list. To see plant names, join the result back to Plants.

Which One to Use

On this small sample, the plan text showed a Left Anti Semi Join for all three working versions. I’d pick NOT EXISTS for readability: it has no NULL trap and no column to choose. Use the LEFT JOIN version when you also need columns from the other table. Always check the row count against what you expect.

Clean Up

Remove the sample tables when you finish.

DROP TABLE OrderLines;
DROP TABLE Plants;

What to Remember

An unmatched records query keeps the rows that have no partner. LEFT JOIN with a NULL test on the right key works, NOT EXISTS works, and EXCEPT works for keys. NOT IN works only when the list can’t hold a NULL.

To go further, read Loading Only New Rows: INSERT SELECT With NOT EXISTS, FULL JOIN: Preserve Unmatched Rows on Both Sides and Finding Orphaned Child Rows Before Adding a Foreign Key.

A missing row is not an empty result, it is a question the join has to ask on purpose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.