Students With Invalid Departments - Problem

Given two tables Departments and Students, find all students who are enrolled in departments that no longer exist.

The Departments table contains information about university departments with columns:

  • id - Primary key identifying each department
  • name - Name of the department

The Students table contains information about students with columns:

  • id - Primary key identifying each student
  • name - Name of the student
  • department_id - Foreign key referencing the department

Return the id and name of all students whose department_id does not exist in the Departments table. The result can be returned in any order.

Table Schema

Departments
Column Name Type Description
id PK int Primary key identifying each department
name varchar Name of the department
Primary Key: id
Students
Column Name Type Description
id PK int Primary key identifying each student
name varchar Name of the student
department_id int Foreign key referencing the department
Primary Key: id

Input & Output

Example 1 — Student with Invalid Department
Input Tables:
Departments
id name
1 Electrical Engineering
7 Computer Engineering
13 Business Administration
Students
id name department_id
23 Alice 1
1 Bob 7
5 Jennifer 13
2 John 14
4 Jasmine 77
3 Steve 74
6 Luis 1
8 Jonathan 7
7 Daiana 33
Output:
id name
2 John
4 Jasmine
3 Steve
7 Daiana
💡 Note:

Students John (id=2), Jasmine (id=4), Steve (id=3), and Daiana (id=7) are enrolled in departments with IDs 14, 77, 74, and 33 respectively. These department IDs do not exist in the Departments table, so these students are enrolled in invalid departments.
Example 2 — All Students Valid
Input Tables:
Departments
id name
1 Mathematics
2 Physics
Students
id name department_id
1 Alice 1
2 Bob 2
Output:
id name
💡 Note:

All students are enrolled in valid departments (Alice in Mathematics, Bob in Physics), so no students have invalid departments. The result is empty.
Example 3 — No Departments Exist
Input Tables:
Departments
id name
Students
id name department_id
1 Alice 1
2 Bob 2
Output:
id name
1 Alice
2 Bob
💡 Note:

Since no departments exist in the Departments table, all students are enrolled in invalid departments and should be returned in the result.

Constraints

  • 1 ≤ Departments.id ≤ 1000
  • 1 ≤ Students.id ≤ 1000
  • 1 ≤ Students.department_id ≤ 1000
  • Departments.name and Students.name consist of English letters and spaces

Visualization

Tap to expand
Students With Invalid Departments OverviewStudentsidnamedept1Alice12Bob5Departmentsidname1CSLEFT JOINInvalid Studentsidname2BobBob's department (id=5) doesn't exist in Departments table
Understanding the Visualization
1
Input Tables
Students and Departments tables
2
LEFT JOIN
Join on department_id = id
3
Filter NULL
Find missing department matches
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to reveal missing relationships between tables

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen