How to find rows with no match in another table in SQL

1NOT EXISTS with a correlated subqueryRecommended

NOT EXISTS (subquery) is true when the subquery returns no rows. Make the subquery refer to the outer row (s.product_id = p.id) and it is checked once per product: if no sale points at that product, the product is kept. What the subquery selects doesn't matter, so SELECT 1 is the usual way to write it. Any extra rule about which sales count, such as a date range, goes inside the subquery.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    sold_on TEXT
);
INSERT INTO products (id, name) VALUES
    (1, 'Keyboard'), (2, 'Mouse'), (3, 'Monitor'), (4, 'Webcam'), (5, 'Headset');
INSERT INTO sales (product_id, sold_on) VALUES
    (1, '2026-02-10'), (2, '2026-03-02'), (2, '2026-03-15'), (4, '2026-02-20');

-- Products that have never been sold
SELECT p.id, p.name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM sales AS s
    WHERE s.product_id = p.id
)
ORDER BY p.id;

-- Extra conditions go inside the subquery: not sold in March 2026
SELECT p.name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM sales AS s
    WHERE s.product_id = p.id
      AND s.sold_on >= '2026-03-01' AND s.sold_on < '2026-04-01'
)
ORDER BY p.name;

The first query returns Monitor and Headset, the two products with no row in sales. The second keeps every product with no sale in March: Headset, Keyboard, Monitor and Webcam. Only Mouse sold in March. NOT EXISTS only asks whether a matching row exists, so a NULL in sales.product_id can't change its answer, and a product with several sales is still tested once. That is why it is the safe default.

2LEFT JOIN … WHERE … IS NULL (the anti-join)

A LEFT JOIN keeps every row from the left table. When a product has no sale, the columns from sales come back as NULL, so a WHERE s.product_id IS NULL filter keeps exactly the unmatched products. Test a column that can never be NULL in a real match; the join column is the best choice, because a row only matches when it equals p.id.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE sales (id INTEGER PRIMARY KEY, product_id INTEGER);
INSERT INTO products (id, name) VALUES
    (1, 'Keyboard'), (2, 'Mouse'), (3, 'Monitor'), (4, 'Webcam'), (5, 'Headset');
INSERT INTO sales (product_id) VALUES (1), (2), (2), (4);

-- A LEFT JOIN keeps every product; unmatched ones get NULLs from sales
SELECT p.name, s.id AS sale_id
FROM products AS p
LEFT JOIN sales AS s ON s.product_id = p.id
ORDER BY p.id, s.id;

-- Keep only those NULL rows: the anti-join
SELECT p.id, p.name
FROM products AS p
LEFT JOIN sales AS s ON s.product_id = p.id
WHERE s.product_id IS NULL
ORDER BY p.id;

The first query shows what the join builds: one row per sale, so Mouse appears twice, and a NULL sale_id for Monitor and Headset. The IS NULL filter then leaves those two. Keep any condition on sales in the ON clause, not in WHERE: a WHERE test on a sales column throws away the NULL rows you are looking for. This form is handy when you also want columns from a third joined table, but NOT EXISTS says what you mean more directly. Testing the join column also helps the planner: PostgreSQL 18 runs this query as a Hash Anti Join, the same plan it picks for NOT EXISTS, while WHERE s.id IS NULL gets an ordinary join followed by a filter.

3NOT IN, and the NULL that empties it

WHERE id NOT IN (SELECT product_id FROM sales) reads naturally and works, until the subquery returns a NULL. The query below runs it twice: once on clean data, then again after one sale is stored with an unknown product.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE sales (id INTEGER PRIMARY KEY, product_id INTEGER);
INSERT INTO products (id, name) VALUES
    (1, 'Keyboard'), (2, 'Mouse'), (3, 'Monitor'), (4, 'Webcam'), (5, 'Headset');
INSERT INTO sales (product_id) VALUES (1), (2), (2), (4);

-- Works while sales.product_id has no NULLs
SELECT name AS unsold
FROM products
WHERE id NOT IN (SELECT product_id FROM sales)
ORDER BY id;

-- One sale with an unknown product
INSERT INTO sales (product_id) VALUES (NULL);

-- The same query now returns no rows at all
SELECT name AS unsold
FROM products
WHERE id NOT IN (SELECT product_id FROM sales)
ORDER BY id;

Only one table prints. The first query lists Monitor and Headset; the second runs without an error and returns no rows, and this lane prints nothing for an empty result. That missing second table is the bug: there are still two unsold products, but NOT IN can no longer say so. No error, no warning, just an empty report, which is why this one is easy to ship.

4Why NOT IN breaks, and how to fix it

x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. Comparing anything with NULL gives NULL (unknown), and an AND that contains an unknown can never be true, so WHERE drops every row. Filter the NULLs out inside the subquery, or switch to NOT EXISTS.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE sales (id INTEGER PRIMARY KEY, product_id INTEGER);
INSERT INTO products (id, name) VALUES
    (1, 'Keyboard'), (2, 'Mouse'), (3, 'Monitor'), (4, 'Webcam'), (5, 'Headset');
INSERT INTO sales (product_id) VALUES (1), (2), (2), (4), (NULL);

-- Why it broke: comparing 3 with NULL is unknown, not true
SELECT 3 NOT IN (1, 2, 4) AS no_null,
       3 NOT IN (1, 2, 4, NULL) AS with_null;

-- The fix: drop the NULLs inside the subquery
SELECT name AS unsold
FROM products
WHERE id NOT IN (
    SELECT product_id FROM sales WHERE product_id IS NOT NULL
)
ORDER BY id;

3 NOT IN (1, 2, 4) is 1 (true), but 3 NOT IN (1, 2, 4, NULL) is NULL: SQL can't rule out that the unknown value is 3. With WHERE product_id IS NOT NULL in the subquery the result is back to Monitor and Headset. If the column is declared NOT NULL, plain NOT IN is safe, but the filter costs nothing and keeps the query correct if the schema changes.

5EXCEPT: compare two lists of ids

EXCEPT takes the rows of the first query and removes every row that also appears in the second. It is the clearest way to compare two id lists in either direction, and unlike NOT IN it treats two NULLs as equal. Both queries must return the same number of columns, and the result has no duplicates (SELECT NULL EXCEPT SELECT NULL returns no rows). Oracle calls the same operator MINUS.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE sales (id INTEGER PRIMARY KEY, product_id INTEGER);
INSERT INTO products (id, name) VALUES
    (1, 'Keyboard'), (2, 'Mouse'), (3, 'Monitor'), (4, 'Webcam'), (5, 'Headset');
INSERT INTO sales (product_id) VALUES (1), (2), (2), (4), (9), (NULL);

-- Product ids that never appear in sales
SELECT id FROM products
EXCEPT
SELECT product_id FROM sales
ORDER BY id;

-- The other direction: sales whose product id is not in products
SELECT product_id FROM sales
EXCEPT
SELECT id FROM products
ORDER BY product_id;

-- EXCEPT returns only the compared columns; join back for the names
SELECT name AS unsold
FROM products
WHERE id IN (
    SELECT id FROM products
    EXCEPT
    SELECT product_id FROM sales
)
ORDER BY id;

Products minus sales gives ids 3 and 5; the NULL sale doesn't get in the way. Sales minus products finds the orphans: the NULL product and id 9, which has no row in products. Because EXCEPT only returns the compared columns, the last query feeds it into IN to get the names back. IN is safe here because a NULL can only stop a NOT IN. MySQL supports EXCEPT from 8.0.31; on older MySQL use NOT EXISTS. To combine results instead of subtracting them, see UNION vs UNION ALL.

6The positive case: EXISTS vs IN

To find rows that do have a match, drop the NOT. EXISTS and IN return the same rows, and a NULL in the subquery is harmless for IN, so pick whichever reads better. A plain JOIN is different: it returns one row per match.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE sales (id INTEGER PRIMARY KEY, product_id INTEGER);
INSERT INTO products (id, name) VALUES
    (1, 'Keyboard'), (2, 'Mouse'), (3, 'Monitor'), (4, 'Webcam'), (5, 'Headset');
INSERT INTO sales (product_id) VALUES (1), (2), (2), (4), (NULL);

-- Products with at least one sale: EXISTS
SELECT p.name AS sold
FROM products AS p
WHERE EXISTS (SELECT 1 FROM sales AS s WHERE s.product_id = p.id)
ORDER BY p.id;

-- The same with IN (NULLs in the list don't matter here)
SELECT name AS sold
FROM products
WHERE id IN (SELECT product_id FROM sales)
ORDER BY id;

-- A plain JOIN repeats a product once per sale
SELECT p.name AS joined
FROM products AS p
JOIN sales AS s ON s.product_id = p.id
ORDER BY p.id;

EXISTS and IN both return Keyboard, Mouse and Webcam, each once. The JOIN returns Mouse twice because it sold twice. Use EXISTS or IN when you only want to know whether a match exists, and a JOIN when you need columns from the matching rows. IN also takes a literal list, as in WHERE id IN (1, 2, 4), which EXISTS can't.

7Which should you use?

All four run on PostgreSQL, MySQL (EXCEPT from 8.0.31), SQL Server and SQLite. The real difference is how they treat NULL and what they can return.