Employees with Matching Departments
Problem statement
Return the employee ID, employee name and department name for every matching pair of rows in employees and departments. A pair matches when their non-NULL department_id values are equal.
employees(employee_id, employee_name, department_id) has a unique, non-NULL integer employee ID, a non-NULL text name and a nullable integer department ID. departments(department_id, department_name) has a nullable integer department ID and nullable text name. Department rows and keys may repeat.
Return columns employee_id, employee_name, department_name in that order. Preserve one result row per matching pair, including identical duplicates and NULL department names. Exclude unmatched rows. A NULL join key never matches, even with another NULL. Result rows may be in any order.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
employees
| Column | Type | Nullable | Description |
|---|---|---|---|
| employee_idPK | Integer | No | — |
| employee_name | Text | No | — |
| department_id | Integer | Yes | — |
departments
| Column | Type | Nullable | Description |
|---|---|---|---|
| department_id | Integer | Yes | — |
| department_name | Text | Yes | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| employee_id | Integer | No | — |
| employee_name | Text | No | — |
| department_name | Text | Yes | — |
Row order: any order is accepted. Numeric tolerance: 0.
Constraints
- Each input table has between 0 and 10 rows.
- Non-NULL IDs are integers from -1000000 to 1000000.
- Each name has at most 10 ASCII letters, digits or spaces.