Identify Employees with Invalid Department References

EMPLOYEE

+-----+-----+--------+
| eid | did | ename  |
+-----+-----+--------+
| 1   | 10  | Alice  |
| 2   | 11  | Bob    |
| 3   | 99  | Carol  |
+-----+-----+--------+

DEPARTMENT

+-----+---------+
| did | dname   |
+-----+---------+
| 10  | Sales   |
| 11  | Finance |
+-----+---------+

Scenario

A company database holds EMPLOYEE and DEPARTMENT tables. Management needs to find employees referencing non-existent departments so the data can be cleaned.

Question

Using SQL, return all rows from EMPLOYEE whose did is not present in DEPARTMENT.

Hints

Think anti-join patterns such as LEFT JOIN … WHERE department.did IS NULL, or use NOT EXISTS/NOT IN.

Find employees with non-existent departments

Table EMPLOYEE

column	type	constraints
eid	INTEGER	PRIMARY KEY, NOT NULL
did	INTEGER	NOT NULL
ename	VARCHAR	NOT NULL

Sample data:

did	eid	ename
10	1	Alice
11	2	Bob
99	3	Carol

Table DEPARTMENT

column	type	constraints
did	INTEGER	PRIMARY KEY, NOT NULL
dname	VARCHAR	NOT NULL

Sample data:

did	dname
10	Sales
11	Finance

Expected output

All EMPLOYEE rows where did has no matching did in DEPARTMENT.

did	eid	ename
99	3	Carol

Hints

1. Use an anti-join: LEFT JOIN DEPARTMENT d ON d.did = e.did and filter WHERE d.did IS NULL.
2. Or use NOT EXISTS to check absence of a matching department row.
3. If using NOT IN, beware of NULLs in the subquery.