How to select all records from one table that do not exist in another table?

To select all records from one table that do not exist in another table, you can use several SQL techniques. Below are detailed explanations with examples for the most common approaches:

1. Using LEFT JOIN + WHERE IS NULL

Concept: Left join the first table to the second table and filter rows where the second table’s key is NULL.
Best for: Most databases, optimal performance with indexes.

SELECT TableA.*
FROM TableA
LEFT JOIN TableB 
  ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL;

Example:

-- Find employees not in the managers table
SELECT e.*
FROM employees e
LEFT JOIN managers m 
  ON e.employee_id = m.employee_id
WHERE m.employee_id IS NULL;

2. Using NOT EXISTS

Concept: Correlated subquery to check for non-existence in the second table.
Best for: Readability, handles NULL values safely.

SELECT *
FROM TableA
WHERE NOT EXISTS (
  SELECT 1
  FROM TableB
  WHERE TableA.ID = TableB.ID
);

Example:

-- Find products never ordered
SELECT p.*
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE p.product_id = o.product_id
);

3. Using NOT IN

Concept: Direct exclusion using a subquery.
Caution: Fails if subquery returns NULL values.

SELECT *
FROM TableA
WHERE ID NOT IN (
  SELECT ID 
  FROM TableB
  WHERE ID IS NOT NULL  -- Prevent NULL issues
);

Example:

-- Find customers without orders
SELECT *
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
  WHERE customer_id IS NOT NULL
);

4. Using EXCEPT (ANSI Standard)

Concept: Set operation to subtract matching rows.
Best for: Databases supporting EXCEPT (e.g., PostgreSQL, SQL Server).

SELECT ID, Column1, Column2 
FROM TableA
EXCEPT
SELECT ID, Column1, Column2 
FROM TableB;

Example:

-- Find unique cities in employees not in offices
SELECT city FROM employees
EXCEPT
SELECT city FROM offices;

Performance Comparison

Method	Performance	Handles NULL	Readability	Cross-DB Support
LEFT JOIN	⭐⭐⭐⭐⭐ (with indexes)	✅	⭐⭐⭐⭐	✅
NOT EXISTS	⭐⭐⭐⭐	✅	⭐⭐⭐⭐⭐	✅
NOT IN	⭐⭐	❌ (unless filtered)	⭐⭐⭐⭐	✅
EXCEPT	⭐⭐⭐	✅	⭐⭐⭐⭐⭐	⚠️ (Not in MySQL)

Key Considerations

1. Indexes:
   Always index join/where columns (e.g., TableA.ID, TableB.ID).
2. NULL Values:

• NOT IN fails if subquery returns NULLs.
• NOT EXISTS and LEFT JOIN handle NULLs safely.

1. Column Matching:
   Ensure compared columns have compatible data types.

Advanced Scenarios

Composite Keys

SELECT *
FROM Orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM Shipments s
  WHERE o.order_id = s.order_id
    AND o.product_id = s.product_id
);

Partial Matching

-- Find employees without "@company.com" emails
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM company_emails ce
  WHERE e.email = ce.email
    AND ce.email LIKE '%@company.com'
);

Temporal Data

-- Find active products not sold in 2023
SELECT p.*
FROM products p
LEFT JOIN orders o 
  ON p.product_id = o.product_id
  AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
WHERE o.order_id IS NULL;

MySQL-Specific Tip

MySQL lacks EXCEPT, but you can emulate it with a LEFT JOIN:

SELECT a.*
FROM TableA a
LEFT JOIN TableB b USING (id)
WHERE b.id IS NULL;

Common Pitfalls & Fixes

1. NOT IN Returns Nothing:
   Caused by NULLs in subquery.
   Fix: Add WHERE ID IS NOT NULL to subquery.
2. Performance Issues:
   Fix: Index join columns and avoid SELECT * in subqueries.
3. Case Sensitivity:
   Fix: Use COLLATE or consistent casing:

   ON TableA.name = TableB.name COLLATE SQL_Latin1_General_CP1_CI_AS

Examples in Different Databases

PostgreSQL:

-- Use EXCEPT
SELECT product_id FROM inventory
EXCEPT
SELECT product_id FROM sales;

SQL Server:

-- Use NOT EXISTS with schema prefix
SELECT *
FROM dbo.TableA a
WHERE NOT EXISTS (
  SELECT 1
  FROM dbo.TableB b
  WHERE a.ID = b.ID
);

SQLite:

-- LEFT JOIN approach
SELECT a.*
FROM TableA a
LEFT JOIN TableB b 
  ON a.id = b.id
WHERE b.id IS NULL;

By choosing the appropriate method based on your database and data characteristics, you can efficiently find non-matching records while avoiding common pitfalls.