All problemsJoin Two Tables

36. Join Two Tables

MediumDatabases~12 min

The employees table stores a department_id, but the readable department name lives in the departments table.

Return each employee's name alongside their department name. Employees with no department should not appear.

Examples

Example 1

Input:: employees + departments
Output:: Ana Reyes | Engineering, Carla Diaz | Sales, …

Constraints

Return exactly two columns: the employee name, then the department name.
The employee with no department is excluded.
Row order does not matter.

Database

Loading tables…

Show CREATE statements

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id            INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  department_id INTEGER,
  salary        INTEGER NOT NULL,
  hired_on      TEXT NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales'),
  (3, 'Support');

INSERT INTO employees (id, name, department_id, salary, hired_on) VALUES
  (1, 'Ana Reyes',    1, 72000, '2021-03-15'),
  (2, 'Ben Cruz',     1, 68000, '2020-07-01'),
  (3, 'Carla Diaz',   2, 54000, '2022-01-20'),
  (4, 'Dino Lim',     2, 51000, '2019-11-05'),
  (5, 'Elena Santos', 3, 47000, '2023-06-12'),
  (6, 'Frank Uy',     1, 95000, '2018-02-28'),
  (7, 'Grace Tan',    3, 49000, '2022-09-30'),
  (8, 'Hector Ramos', NULL, 60000, '2024-01-08');

SQLite

Loading editor…

⌘/Ctrl + ↵ to run