What are the different types of SQL joins? | Interview Question

Q: What are the different types of SQL joins?

**Target Audience:** Beginners who know SELECT and WHERE on one table and now need data from two tables. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL and SQL Server notes come from their documentation and are marked as such. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output was produced by actually running the queries; the **Verified** section quotes it in full. ## 1. Why This Even Matters — A Story First Picture a school dance with two lists: the students who signed up, and the dance cards with a student number written on each. Some students have two cards, one student has none, and one card has a number nobody recognises, and another card is blank. Now the teacher asks different questions: - "Show me each student with their cards." Students without cards are not i

The problem

Question presented to candidate: "We have a customers table and an orders table. Some customers never ordered, and a few orders point at a customer that is missing. Walk me through the join types SQL gives you, what each one returns for those rows, and when you would reach for each."

What a strong answer should cover:

INNER JOIN keeps only matching pairs; LEFT / RIGHT OUTER JOIN also keep unmatched rows from one side, filled with NULLs; FULL OUTER JOIN keeps unmatched rows from both sides
CROSS JOIN is every row paired with every row (a Cartesian product); a self join is a table joined to itself
Joins multiply rows: a one-to-many join repeats the "one" side once per match, so aggregates and counts must account for it
NULL join keys never match (NULL = NULL is not true), and unmatched rows are where NULLs appear in the output
Anti-join and semi-join patterns: LEFT JOIN ... WHERE right.key IS NULL or NOT EXISTS for "rows with no match", EXISTS for "rows with at least one match"
Engine support: SQLite has RIGHT and FULL OUTER JOIN only since 3.39.0; MySQL has no FULL OUTER JOIN

Code / implementation expected: Yes — write an INNER and a LEFT JOIN and say what happens to unmatched rows.

Schema used by the examples:

sql
joins

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know SELECT and WHERE on one table and now need data from two tables. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL and SQL Server notes come from their documentation and are marked as such.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. All output was produced by actually running the queries; the Verified section quotes it in full.

1. Why This Even Matters — A Story First

Picture a school dance with two lists: the students who signed up, and the dance cards with a student number written on each. Some students have two cards, one student has none, and one card has a number nobody recognises, and another card is blank.

Now the teacher as

The solution is hidden so you can try first

14 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLINNER JOIN vs LEFT JOIN: what happens to Dara, who has no orders

SQLRIGHT JOIN and FULL OUTER JOIN (SQLite 3.39.0 and later)

SQLHow many rows each join produces, and how a one-to-many join repeats the left row

SQLAnti-joins: customers with no orders, and orders with no customer

PostgreSQL: a LEFT JOIN with IS NULL on the join key becomes an anti join

EXPLAIN (COSTS OFF)
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.