What are the different types of SQL joins?
A join combines rows from two tables based on a related column. An inner join returns only rows that match in both tables. A left join returns all rows from the left table plus matches from the right. A right join does the opposite. A full outer join returns all rows from both tables, matched where possible.
- INNER JOIN returns only rows that match in both tables.
- LEFT and RIGHT JOIN keep all rows from one side and fill missing matches with null.
- FULL OUTER JOIN keeps all rows from both tables, matched where possible.
The four common joins
- Inner join: only rows with a match in both tables.
- Left join: all left rows, with matching right rows or nulls.
- Right join: all right rows, with matching left rows or nulls.
- Full outer join: all rows from both, matched where possible.
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- returns every customer, even those with no ordersInterviewers often ask when you would use a left join. A great example is listing all customers including those with no orders, which an inner join would drop. Tying the join type to a real need is what they want to hear.
Frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table plus matches, using null where no match exists.
What is a self join?
A self join is a table joined to itself, useful for comparing rows within the same table, such as employees linked to their managers.
Common follow up questions
Related interview questions
Want the full SQL guide?
Read every SQL concept with notes, diagrams, and code in one place. Track your progress as you go.
Open the SQL guide All SQL questions