SQL Join Dark Arts: Anti Joins, Semi Joins, and Beyond

SQL Join Dark Arts: Anti Joins, Semi Joins, and Beyond “Beyond INNER and OUTER lies the realm of the JOIN masters.” Modern SQL isn’t just about fetching related rows—it’s about expressing logic concisely, filtering precisely, and maximizing performance. In this article, we’ll explore the most underused join strategies in SQL, often called the “dark arts”: Anti joins (NOT EXISTS, NOT IN, EXCEPT) Semi joins (EXISTS, IN, INTERSECT) Cartesian joins (CROSS JOIN) We’ll walk through practical use cases and advanced examples that turn verbose logic into elegant queries. Data Setup: Customers and Orders CREATE TABLE Customers ( id INT PRIMARY KEY, name TEXT ); CREATE TABLE Orders ( id INT PRIMARY KEY, customer_id INT REFERENCES Customers(id), total DECIMAL(10,2) ); INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol'); INSERT INTO Orders VALUES (1, 1, 120.00), (2, 1, 80.00), (3, 2, 50.00); Semi Join with EXISTS Use Case: Get customers who placed at least one order. SELECT * FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.customer_id = c.id ); ✅ Fast, short-circuiting filter. Equivalent with IN: SELECT * FROM Customers WHERE id IN (SELECT customer_id FROM Orders); Anti Join with NOT EXISTS Use Case: Get customers who never placed an order. SELECT * FROM Customers c WHERE NOT EXISTS ( SELECT 1 FROM Orders o WHERE o.customer_id = c.id );

May 12, 2025 - 21:31

SQL Join Dark Arts: Anti Joins, Semi Joins, and Beyond

“Beyond INNER and OUTER lies the realm of the JOIN masters.”

Modern SQL isn’t just about fetching related rows—it’s about expressing logic concisely, filtering precisely, and maximizing performance.

In this article, we’ll explore the most underused join strategies in SQL, often called the “dark arts”:

Anti joins (NOT EXISTS, NOT IN, EXCEPT)
Semi joins (EXISTS, IN, INTERSECT)
Cartesian joins (CROSS JOIN)

We’ll walk through practical use cases and advanced examples that turn verbose logic into elegant queries.

Data Setup: Customers and Orders

CREATE TABLE Customers (
  id INT PRIMARY KEY,
  name TEXT
);

CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES Customers(id),
  total DECIMAL(10,2)
);

INSERT INTO Customers VALUES
(1, 'Alice'), (2, 'Bob'), (3, 'Carol');

INSERT INTO Orders VALUES
(1, 1, 120.00), (2, 1, 80.00), (3, 2, 50.00);

Semi Join with EXISTS

Use Case: Get customers who placed at least one order.

SELECT *
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o WHERE o.customer_id = c.id
);

✅ Fast, short-circuiting filter. Equivalent with IN:

SELECT * FROM Customers
WHERE id IN (SELECT customer_id FROM Orders);

Anti Join with NOT EXISTS

Use Case: Get customers who never placed an order.

SELECT *
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.customer_id = c.id
);