How to write "not in ()" sql query using join

sql

join

best-practices

performance

byNikita Barsukov·Sep 28, 2024

Mimic NOT IN using a LEFT JOIN and isolate non-matching records using a WHERE clause that screens for NULL condition. This way, it filters out the rows similar to NOT IN list.

Here's an SQL shortcut:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

In this example, t1.* fetches all data from table1 without allies in table2, similar to NOT IN.

Beware of "Not in"

Usage of NOT IN may come with discrepancies if there's a NULL value in the list. Moreover, NOT IN struggles with efficiency, especially with big lists.

A better alternative : "Not exists"

As an alternative to LEFT JOIN, NOT EXISTS often delivers superior performance and effectiveness, especially when work involves complex subqueries or significant datasets.

Here's how you write NOT EXISTS:

SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (
  SELECT 1 
  FROM table2 t2 
  WHERE t1.id = t2.id
);

/* Anything that does not exist in table 2, you've been summoned by table1. */

Level up with "Except"

Another method, EXCEPT, comes handy when you aim to simply discard one set of rows from another, concurrently omitting duplicates.

Using EXCEPT:

SELECT id
FROM table1
EXCEPT
SELECT id
FROM table2;

/* Behave like a bouncer at the club door - if id exists in both tables, you're out! */

Note that EXCEPT may not be supported in all SQL dialects (MySQL, we're looking at you).

Leveraging Left Join

When leveraging the LEFT JOIN method, you must affirm NULL check is conducted on a non-nullable column of the right table. Choosing a nullable column can result in false positives.

And remember, Left join isn't the fastest horse on the race track, particularly with large data sets.