Employees with optional department

SQL data engineering interview problem. Difficulty: beginner. Pattern: Joins. About 8 minutes. Free to practice.

Sometimes an employee has a department_id that does not match any row in the departments table, for example because the department was deleted. HR still wants to see these people in the report. Return every employee's name together with their department_name. If an employee has no matching department, they must still appear, with a NULL department_name. Sort by name from A to Z.

Requirements

• Every employee in the employees table must appear in the result.
• Use a LEFT JOIN from employees to departments, so the employee side is always kept.
• When there is no matching department, department_name is NULL. Do not hide these rows.
• Sort by name from A to Z.

Constraints

• The 2 tables are employees and departments.
• Return only these 2 columns, in this order: name, department_name.

Examples

Input: employees departments name | department_id department_id | department_name Alice Chen | 1 1 | Engineering Omar Ali | 2 2 | Sales Max Reed | 2 3 | Marketing Output: name | department_name Alice Chen | Engineering Max Reed | Sales Omar Ali | Sales Why this passes: LEFT JOIN keeps every employee. In this seed every row matches; unmatched employees would show NULL department_name.

Topics: lakebench, sql, left join.

More SQL interview questions · All interview problems · Learn data engineering