SQL Joins

In well-designed databases, data is split across multiple tables to avoid repetition. This principle is called normalization. Instead of storing the department name on every employee row, we store a dept_id that references a separate departments table.

SQL JOINs let us combine these tables back together when we need the full picture. Here are our two sample tables:

Notice that Alice has dept_id = 1, which corresponds to "Engineering" in the departments table. Frank has dept_id = NULL — he hasn't been assigned yet. And there's a "Legal" department (id 5) with no employees. Joins let us combine these tables in different ways.

An INNER JOIN returns only the rows where there is a match in both tables. If an employee has no matching department, or a department has no employees, those rows are excluded.

Click on rows in each table to see which ones match:

INNER JOIN Result:

A LEFT JOIN returns all rows from the left table (employees), plus matching rows from the right table (departments). If there's no match, the right side columns are filled with NULL.

Toggle between LEFT JOIN and INNER JOIN to compare the results. Notice the extra rows with NULL values:

A RIGHT JOIN is the mirror of LEFT JOIN — it returns all rows from the right table and matches from the left. A FULL OUTER JOIN returns all rows from both tables, filling NULL where there is no match on either side.

Write your own JOIN queries against the sample tables. Supported syntax:

SELECT columns
FROM employees e
[INNER|LEFT|RIGHT] JOIN departments d
  ON e.dept_id = d.id;

Available columns: e.id, e.name, e.dept_id, d.id, d.dept_name, or * for all.

1. Which join type returns only rows that have matching values in both tables?

2. In a LEFT JOIN, what appears in the result when a left-table row has no match in the right table?

3. Which join returns all rows from both tables, even if there is no match?