Advanced Joins

By the end of this lesson you'll reach beyond INNER and LEFT JOIN to answer the questions they can't: "who has an order?", "who has no order?", "what's each customer's biggest order?". You'll master semi-joins, anti-joins, self-joins, CROSS JOIN , and LATERAL — and the NULL trap that silently breaks NOT IN .

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every query in this lesson runs against these two small tables. They share one link: orders.customer_id points back to customers.id . Notice Dan has no row in orders — that "missing" customer is the star of the anti-join section.

So Ava (1) has two orders, Ben (2), Cara (3) and Eve (5) have one each, and Dan (4) has none.

A semi-join keeps rows from the left table that have at least one match on the right — but it never adds the right table's columns and never duplicates a row. You're asking "is this customer on the orders list?", not "show me every order".

A semi-join is checking names off a guest list against the RSVP pile. You only care whether someone RSVP'd — not how many cards they sent. The guest appears once, ticked or not.

The same answer reads a little shorter with IN , which checks whether each customer's id appears among the order rows:

Fill in the one blank with the keyword that means "a match exists". The expected result is in the comments so you can check yourself.

An anti-join is the mirror image: keep the left-table rows that have no match on the right. This is how you answer "which customers have never ordered?", "which products never sold?", "which users never logged in?". The cleanest tool is WHERE NOT EXISTS (...) .

Back to the guest list: an anti-join finds everyone who didn't RSVP — the names with an empty spot in the reply pile. In our data that's exactly one person: Dan .

You can write it with NOT IN too — but only if you guard against NULLs in the subquery:

Fill in the blank with the two words that mean "no match exists", so the query returns customers with no orders.

A self-join joins a table to a second copy of itself using two aliases. It's how you compare rows within one table — find pairs, build employee→manager hierarchies, or compare consecutive rows. Here we'll find customers who placed more than one order by pairing each order with another order from the same customer.

A CROSS JOIN pairs every row of one table with every row of another — no ON clause. The output size is the two row counts multiplied . It's invaluable for generating every combination (sizes × colours, dates × products), but a missing join condition silently becomes one and explodes your result.

5 customers × 5 orders = 25 rows. Harmless here — but 1,000 × 1,000 = 1,000,000 rows. Always know why you're cross-joining.

A normal subquery in the FROM clause can't see the outer row. LATERAL removes that wall: it runs the subquery once for each outer row and lets it reference that row's columns — like a for-each loop. It's the cleanest way to get "the top N rows per group", here each customer's single biggest order.

EXISTS only checks whether a row comes back, not what's in it, so the select list is irrelevant. 1 is a conventional placeholder; SELECT * would behave identically.

For correctness with NOT IN , yes — NOT EXISTS avoids the NULL trap. For positive checks, modern optimisers often run IN and EXISTS the same way; EXISTS tends to win on large or correlated subqueries.

Q: When would I use a self-join instead of a window function?

Self-joins shine for pairing rows or walking a hierarchy. For "rank within a group" or "compare to the previous row", window functions (the next lesson) are usually clearer and faster.

PostgreSQL and MySQL 8+ use LATERAL ; SQL Server and Oracle use CROSS APPLY / OUTER APPLY . SQLite has neither — there you'd fall back to a correlated subquery or a window function.

Support is faded now — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary