ANTI-JOINJoins
Buy Me A Coffee

The NOT EXISTS pattern or LEFT JOIN ... IS NULL form for a performance advantage.

A join pattern that returns rows from the left table with no match in the right table, typically implemented with NOT EXISTS or NOT IN. Useful for finding records without related entries (e.g., customers who have never placed an order).

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0
PostgreSQL ✓ Supported 9.5
SQL Server ✓ Supported 2017
Oracle ✓ Supported 12c
SQLite ✓ Supported — SQLite supports NOT EXISTS correlated subqueries.

Details

Anti-joins can be a powerful tool for optimizing performance by reducing the number of rows scanned.

Standard Syntax

SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);

Version Support

MySQL: Since 8.0 PostgreSQL: Since 9.5 SQL Server: Since 2017 Oracle: Since 12c SQLite: Since ?

Per-Database Syntax & Notes

MySQL

SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);

PostgreSQL

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column WHERE table2.column IS NULL;

SQL Server

SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);

Oracle

SELECT * FROM table1 MINUS SELECT * FROM table2;