SQL JOIN Types Practice

Go beyond INNER and LEFT JOIN with RIGHT JOIN, FULL OUTER JOIN, self joins, cross joins, and queries that join three tables.

Intermediate7 problemsUpdated October 23, 2026

Review first: RIGHT JOIN, FULL OUTER JOIN, SELF JOIN, CROSS JOIN, Multiple JOINs

employees (manager_id is the emp_id of the employee's manager)

emp_id	name	dept_id	manager_id
1	Asha	10	NULL
2	Ravi	20	1
3	Meera	20	2
4	John	30	1
5	Priya	NULL	2

departments

dept_id	dept_name
10	Management
20	Engineering
30	Sales
40	Research

projects

project_id	title	emp_id
1	Website	2
2	Mobile App	3
3	Campaign	4
4	Data Platform	3

shifts

shift_name
Morning
Night

Try your answers on these tables in the SQL Playground

1.Every department, with its people
Intermediate
Using a RIGHT JOIN from employees to departments, return every dept_name with the name of each employee in it. Departments with no employees must still appear. Sort by department, then name.
Show hint
In a RIGHT JOIN, every row of the right (second) table is kept.
Show solution
SQLRun
SELECT d.dept_name, e.name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id ORDER BY d.dept_name, e.name;
Output
dept_name | name ------------+------ Engineering | Meera Engineering | Ravi Management | Asha Research | NULL Sales | John
Research has no employees, so its name is NULL. Priya has no department, so a RIGHT JOIN drops her. Most people write this query as departments LEFT JOIN employees, which gives the same result and reads more naturally.
2.Everyone and everything
Intermediate
Return name and dept_name for every employee and every department, matched where possible. Unmatched employees and unmatched departments must both appear. Sort by emp_id.
Show hint
A FULL OUTER JOIN keeps unmatched rows from both sides.
Show solution
SQLRun
SELECT e.name, d.dept_name FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.dept_id ORDER BY e.emp_id;
Output
name | dept_name ------+------------ NULL | Research Asha | Management Ravi | Engineering Meera | Engineering John | Sales Priya | NULL
Priya appears with no department and Research appears with no employee. The Research row has a NULL emp_id, so where it is sorted depends on the database: SQLite, MySQL and SQL Server put NULLs first in ascending order, PostgreSQL and Oracle put them last. MySQL has no FULL OUTER JOIN; there you combine a LEFT JOIN and a RIGHT JOIN with UNION.
3.Employees and their managers
Intermediate
Return each employee's name as employee and their manager's name as manager. Include employees who have no manager. Sort by emp_id.
Show hint
Join employees to itself with two different aliases. Use a LEFT JOIN so Asha (no manager) is kept.
Show solution
SQLRun
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id ORDER BY e.emp_id;
Output
employee | manager ---------+-------- Asha | NULL Ravi | Asha Meera | Ravi John | Asha Priya | Ravi
4.Count direct reports
Intermediate
Return the name of every manager as manager and how many people report directly to them as reports. Sort by name.
Show hint
Self join so each row pairs an employee with their manager, then group by the manager.
Show solution
SQLRun
SELECT m.name AS manager, COUNT(*) AS reports FROM employees e JOIN employees m ON e.manager_id = m.emp_id GROUP BY m.emp_id, m.name ORDER BY m.name;
Output
manager | reports --------+-------- Asha | 2 Ravi | 2
5.Build a shift rota
Intermediate
Every department needs one person on each shift. Return every combination of dept_name and shift_name, sorted by department, then shift.
Show hint
A CROSS JOIN pairs every row of one table with every row of the other.
Show solution
SQLRun
SELECT d.dept_name, s.shift_name FROM departments d CROSS JOIN shifts s ORDER BY d.dept_name, s.shift_name;
Output
dept_name | shift_name ------------+----------- Engineering | Morning Engineering | Night Management | Morning Management | Night Research | Morning Research | Night Sales | Morning Sales | Night
4 departments × 2 shifts = 8 rows. A cross join has no ON condition, so the row count multiplies quickly; use it only when you really want every combination.

6.Project, person, department

Intermediate

For every project, return the project title, the employee's name and their dept_name. Sort by title.

Show hint

Join projects to employees, then join the result to departments. Each join has its own ON.

Show solution

SQLRun

SELECT p.title, e.name, d.dept_name
FROM projects p
JOIN employees e   ON p.emp_id = e.emp_id
JOIN departments d ON e.dept_id = d.dept_id
ORDER BY p.title;

Output

title         | name  | dept_name
--------------+-------+------------
Campaign      | John  | Sales
Data Platform | Meera | Engineering
Mobile App    | Meera | Engineering
Website       | Ravi  | Engineering

7.Employees with no project
Intermediate
Return the names of employees who are not working on any project.
Show hint
LEFT JOIN to projects and keep the rows where the project side is NULL.
Show solution
SQLRun
SELECT e.name FROM employees e LEFT JOIN projects p ON p.emp_id = e.emp_id WHERE p.project_id IS NULL ORDER BY e.name;
Output
name ----- Asha Priya
This "anti-join" pattern finds rows with no match. WHERE NOT EXISTS (SELECT 1 FROM projects p WHERE p.emp_id = e.emp_id) gives the same answer.

← All practice sets Learn SQL course →