Employees With No Real Department
Mediumsql
Some rows in employees have a department_id that does not correspond to any row in departments — a referential-integrity violation. Write a query returning id, name, and department_id for every such orphaned employee, ordered by id.
sqlCREATE TABLE departments ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, department_id INTEGER );
Sample data (this is what your query runs against when you press Run):
sqlINSERT INTO departments (id, name) VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Marketing'); INSERT INTO employees (id, name, email, department_id) VALUES (1, 'Asha', 'asha@co.com', 1), (2, 'Rohan', 'rohan@co.com', 1), (3, 'Neha', 'neha@co.com', 2), (4, 'Vikram', 'asha@co.com', 2), (5, 'Priya', 'priya@co.com', 99), (6, 'Karan', 'karan@co.com', 3), (7, 'Divya', 'karan@co.com', 3), (8, 'Aman', 'aman@co.com', 88);
Example 1
Input
(none)
Output
id name department_id 5 Priya 99 8 Aman 88
A LEFT JOIN to departments, filtered to rows where the department side is NULL, finds exactly the orphaned references. Reference: SELECT e.id, e.name, e.department_id FROM employees e LEFT JOIN departments d ON d.id = e.department_id WHERE d.id IS NULL ORDER BY e.id;