Solving tips
- An INNER JOIN keeps only rows that match on both sides, so unmatched employees and empty departments disappear.
- Join on the foreign key (employees.dept_id) equaling the primary key (departments.id), not on names.
- Rows where the join key is NULL never match in an INNER JOIN, so an employee with no dept_id is dropped.
Pair each employee with the department they belong to.
Schema
CREATE TABLE departments (
id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT REFERENCES departments(id)
);
departments
| id | dept_name |
|---|
| 10 | Engineering |
| 20 | Sales |
| 30 | Marketing |
| 40 | Finance |
employees
| id | name | dept_id |
|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Carol | 10 |
| 4 | Dave | 30 |
| 5 | Eve | NULL |
Task
Return one row per employee who is assigned to a department, with columns name (the employee) and dept_name. Order by name ascending.
Expected output
| name | dept_name |
|---|
| Alice | Engineering |
| Bob | Sales |
| Carol | Engineering |
| Dave | Marketing |
Approach
Use an INNER JOIN between employees and departments on the foreign-key relationship employees.dept_id = departments.id. INNER JOIN emits a row only when a matching department exists, which naturally excludes employees with a NULL dept_id and departments with no employees.
Query
SELECT e.name,
d.dept_name
FROM employees AS e
INNER JOIN departments AS d
ON e.dept_id = d.id
ORDER BY e.name;
Walkthrough
- Alice (dept_id 10) and Carol (dept_id 10) match Engineering.
- Bob (dept_id 20) matches Sales; Dave (dept_id 30) matches Marketing.
- Eve has
dept_id = NULL, and NULL = 10 is never true, so she is dropped.
- Finance (id 40) has no employees pointing at it, so it never appears.
ORDER BY e.name sorts the four surviving rows alphabetically: Alice, Bob, Carol, Dave.
Complexity & notes
- With a foreign-key index on
employees.dept_id and the primary key on departments.id, the planner can use an index/hash join; on this tiny data set it is a trivial scan.
- Common pitfall: expecting Eve or Finance to appear. If you need every employee regardless of a matching department, switch to
LEFT JOIN; if you need every department including empty ones, put departments on the left of a LEFT JOIN.
INNER JOIN and plain JOIN are synonyms in PostgreSQL and standard SQL.