SQL Intermediate Lesson 5: Joins in Depth – Anti-Joins, Self-Joins, Cross Joins

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 5 of 10

Anti-join: rows with no match

“Delivered orders with no payment” is the classic accounts question:

SELECT o.order_id, o.order_date
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status = 'Delivered' AND p.payment_id IS NULL;
-- same with NOT EXISTS:
SELECT COUNT(*) FROM orders o
WHERE o.status = 'Delivered'
  AND NOT EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.order_id);

Self-join: a table joined to itself

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

LEFT JOIN keeps the director, who has no manager.

Cross join: every combination

A report should show North / August even if North sold nothing in August. Build every region × month pair, then LEFT JOIN the sales:

SELECT r.region, mo.month, COALESCE(SUM(s.amount), 0) AS sales
FROM (SELECT DISTINCT region FROM customers) r
CROSS JOIN (SELECT DISTINCT month FROM targets) mo
LEFT JOIN sales s ON s.region = r.region AND s.month = mo.month
GROUP BY r.region, mo.month;

Common mistakes

• Filtering the right table in WHERE (WHERE p.mode = 'UPI') silently turns a LEFT JOIN into an INNER JOIN. Put such conditions in the ON clause.
• Joining payments to orders and summing order values: an order paid in two parts is counted twice. Aggregate payments first.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.