SQL JOIN Types

Easy5 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You are given two tables, employees and departments.

Table: employees

id	name	department_id
1	John	1
2	Alex	2
3	Lisa	1
4	Mia	1

Table: departments

id	name
1	HR
2	Finance
3	IT

Task

Name the SQL join types and say what each one returns.
Write one query per join type that returns the employee name and the department name.
Say how many rows each query returns for this data.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

INNER JOIN: only the rows that match in both tables.
LEFT JOIN: every row from the left table, plus matches from the right (NULL when there is none).
RIGHT JOIN: the mirror image: every row from the right table.
FULL JOIN: every row from both tables, NULL where there is no match.
CROSS JOIN: every left row paired with every right row.

1Basic

One query per join type

1-- INNER JOIN: matching rows only2SELECT e.name, d.name AS department3FROM employees e4INNER JOIN departments d ON e.department_id = d.id;5 6-- LEFT JOIN: every employee, even without a department7SELECT e.name, d.name AS department8FROM employees e9LEFT JOIN departments d ON e.department_id = d.id;10 11-- RIGHT JOIN: every department, even without employees12SELECT e.name, d.name AS department13FROM employees e14RIGHT JOIN departments d ON e.department_id = d.id;15 16-- FULL JOIN: every row from both tables17SELECT e.name, d.name AS department18FROM employees e19FULL JOIN departments d ON e.department_id = d.id;

Rows returned: INNER 4, LEFT 4, RIGHT 5, FULL 5
IT has no employees, so it only appears in RIGHT and FULL

2Optimized

Index the join key, return only what you need

1-- 1. Index the join key (PostgreSQL does not index foreign keys on its own)2CREATE INDEX idx_employees_department_id ON employees (department_id);3 4-- 2. Use the narrowest join and only the columns you need5SELECT e.name, d.name AS department6FROM employees e7JOIN departments d ON d.id = e.department_id;8 9-- 3. "Departments with no employees": anti-join instead of RIGHT JOIN + IS NULL10SELECT d.name11FROM departments d12WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

Tip: use INNER JOIN unless you really need the unmatched rows
MySQL has no FULL JOIN: combine LEFT JOIN and RIGHT JOIN with UNION

Spotted a mistake in these answers?

Report a problem