Employees with Department Names — INNER JOIN in SQL

The problem

Helix Systems' HR director wants a complete employee roster showing each employee's name alongside the name of their department.

Write a query to return one row per employee with the employee's name and their department's name.

Assumptions:

Both tables have a column called name, so every column reference must be qualified by its table alias.

Output:

One row per employee, with columns employee_name and department_name.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  e.name AS employee_name,
  d.name AS department_name
FROM
  employees e
  JOIN departments d ON e.department_id = d.id

The shape

Both employees and departments have a name column, so the aliases e and d are doing real work: they're the only way PostgreSQL knows which name belongs to whom. The join itself pairs each employee with their department row on e.department_id = d.id, and SELECT then pulls the right name from each side.

Clause by clause

FROM employees e reads from the employees table and aliases it as e. The alias is mandatory in a query like this; without it, every name reference would have to be written as employees.name or departments.name in full, which gets noisy fast.
JOIN departments d ON e.department_id = d.id pairs each employee with their department. For every row in employees, PostgreSQL finds the row in departments whose id equals that employee's department_id. The result is a combined row holding both the employee's columns and the department's columns.
SELECT e.name AS employee_name, d.name AS department_name picks the right name from each side. The aliases e.name and d.name are unambiguous — e.name is the employee, d.name is the department. The output aliases (employee_name, department_name) then label the columns by role so the roster reads cleanly. Drop either qualifier and PostgreSQL would reject the query with an "ambiguous column reference" error before it ran a single row.

You practiced disambiguating same-named columns across joined tables. The recurring rule: when two tables share a column name, every reference to either column must carry its table alias — otherwise PostgreSQL rejects the query as ambiguous.

Return one row per employee with the employee's name and their department's name

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.