Employees Without a Reachable Emergency Contact
Before the annual safety audit, HR checks that every current employee has someone to call in an emergency.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #225
Problem statement
Before the annual safety audit, HR checks that every current employee has someone to call in an emergency. A contact only counts if it has a phone number — rows where phone is NULL were saved half-filled from the onboarding form.
Return the emp_id and full_name of every employee whose status is not 'exited' and who has no emergency contact with a non-NULL phone. Exited employees are never in the answer. Return the rows in any order.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| status | enum(active, notice, exited) |
Primary key: emp_id.
One row per employee, past or present.
Table: EmergencyContact
| Column | Type |
|---|---|
| contact_id | int |
| emp_id | int |
| contact_name | varchar |
| relation | varchar |
| phone | bigint |
Primary key: contact_id.
An employee may list several contacts. phone is a 10-digit mobile number, or NULL when it was never filled in.
Examples
Example 1
Employee
| emp_id | full_name | status |
|---|---|---|
| 201 | Ananya Reddy | active |
| 202 | Ishaan Gupta | active |
| 203 | Pooja Mehta | notice |
| 204 | Farhan Khan | exited |
| 205 | Tanvi Bose | active |
EmergencyContact
| contact_id | emp_id | contact_name | relation | phone |
|---|---|---|---|---|
| 1 | 201 | Suresh Reddy | Father | 9845012345 |
| 2 | 202 | Lata Gupta | Mother | NULL |
| 3 | 203 | Amit Mehta | Spouse | 9123456780 |
| 4 | 203 | Kiran Mehta | Sibling | NULL |
| 5 | 205 | Nisha Bose | Spouse | NULL |
| 6 | 205 | Raj Bose | Father | NULL |
Output
| emp_id | full_name |
|---|---|
| 202 | Ishaan Gupta |
| 205 | Tanvi Bose |
How to solve Employees Without a Reachable Emergency Contact
We want employees for whom no usable contact exists — an anti join, with a twist: the partner rows must themselves pass a filter (phone IS NOT NULL).
With a LEFT JOIN the twist decides where the condition goes. Written in the ON clause, c.phone IS NOT NULL limits which contacts may match; an employee whose contacts all lack a phone then matches nothing and comes out once with NULLs, which c.contact_id IS NULL keeps. Written in WHERE instead, it would run after the join and also throw away the very NULL-extended rows we are looking for, so the query would return nobody.
NOT EXISTS with the phone test inside the subquery reads the same rule directly. NOT IN is safe here only because the subquery selects emp_id, which is never NULL on a contact; were it nullable, one NULL would make every NOT IN unknown. Exited people are filtered on the employee side in all three. Each form is one lookup per employee with an index on EmergencyContact.emp_id.
Reference solution (MySQL)
SELECT e.emp_id, e.full_name
FROM Employee e
LEFT JOIN EmergencyContact c ON c.emp_id = e.emp_id AND c.phone IS NOT NULL
WHERE e.status <> 'exited' AND c.contact_id IS NULLAnother way
SELECT emp_id, full_name FROM Employee e WHERE status <> 'exited' AND NOT EXISTS (SELECT 1 FROM EmergencyContact c WHERE c.emp_id = e.emp_id AND c.phone IS NOT NULL)Another way
SELECT emp_id, full_name FROM Employee WHERE status IN ('active', 'notice') AND emp_id NOT IN (SELECT emp_id FROM EmergencyContact WHERE phone IS NOT NULL)← On-Payroll Headcount by Department and Work Mode · Work E-mail Addresses for New Joiners →