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 INPUT Departments Table id name 1 CS 2 Math Students Table id name dept_id 1 Alice 1 2 Bob 3 3 Carol 2 4 Dan 5 Valid dept Invalid dept ALGORITHM STEPS 1 LEFT JOIN Students LEFT JOIN Departments ON dept_id = Departments.id 2 Match Check Valid depts get matched rows Invalid depts get NULL 3 Filter NULLs WHERE Departments.id IS NULL Keeps only orphan students 4 Select Columns Return student id and name SELECT s.id, s.name FROM Students s LEFT JOIN Departments d ON s.dept_id = d.id WHERE d.id IS NULL FINAL RESULT Invalid Department Students Bob (id: 2) dept_id: 3 (not exists) Dan (id: 4) dept_id: 5 (not exists) Output Table id name 2 Bob 4 Dan 2 Invalid Found Key Insight: LEFT JOIN keeps all students even if no matching department exists. When a student's department_id has no match, the Departments columns become NULL. Filtering with WHERE d.id IS NULL identifies exactly those students enrolled in non-existent departments. Alternative: NOT IN or NOT EXISTS subquery. TutorialsPoint - Students With Invalid Departments | Optimal Solution (LEFT JOIN with NULL check)

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