Every Employee, Department or Not

Easysql

Write a query returning id, name (employee), and department for EVERY employee, showing NULL for employees with no department, ordered by employee id.

sql
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,
  manager_id INTEGER,
  salary INTEGER NOT NULL
);

Sample data:

sql
INSERT INTO departments (id, name) VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing');
INSERT INTO employees (id, name, department_id, manager_id, salary) VALUES
(1, 'Asha', 1, NULL, 90000),
(2, 'Rohan', 1, 1, 70000),
(3, 'Neha', 2, 1, 65000),
(4, 'Vikram', NULL, 1, 50000);

Example 1

Input

(none)

Output

id	name	department
1	Asha	Engineering
2	Rohan	Engineering
3	Neha	Sales
4	Vikram	NULL

LEFT JOIN preserves every employee row regardless of match. Reference: SELECT e.id, e.name, d.name AS department FROM employees e LEFT JOIN departments d ON e.department_id = d.id ORDER BY e.id;

Every Employee, Department or Not

Related Problems

Input (stdin)

Output