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
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0 | |
| PostgreSQL | Native | 9.5 | |
| SQL Server | Native | 2017 | |
| Oracle | Native | 12c | |
| SQLite | Native | - | 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: Native since 8.0
PostgreSQL: Native since 9.5
SQL Server: Native since 2017
Oracle: Native since 12c
SQLite: Native since ?
Per-Database Syntax & Notes
MySQL Native syntax
SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);
PostgreSQL Native syntax
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column WHERE table2.column IS NULL;
SQL Server Native syntax
SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column = table1.column);
Oracle Native syntax
SELECT * FROM table1 MINUS SELECT * FROM table2;