1350. Students With Invalid Departments
Problem Statement
Find the id and name of all students who are enrolled in a department that does not exist in the Departments table. Return the result table in any order.
Table Schema
Table: Departments
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key.
Table: Students
+------------------+---------+
| id | int |
| name | varchar |
| department_id | int |
+------------------+---------+
id is the primary key.
Examples
Example 1
Input Table:
Departments table:
+----+--------------------------+
| id | name |
+----+--------------------------+
| 1 | Electrical Engineering |
| 7 | Computer Engineering |
+----+--------------------------+
Students table:
+----+----------+---------------+
| id | name | department_id|
+----+----------+---------------+
| 1 | Kelly | 1 |
| 2 | Jonathan | 101 |
+----+----------+---------------+
Expected Output:
+----+----------+
| id | name |
+----+----------+
| 2 | Jonathan |
+----+----------+
SQL Solution
SELECT s.id, s.name
FROM Students s
WHERE s.department_id NOT IN (SELECT id FROM Departments);
Problem Info
DifficultyEASY
Topics
subquerynot-in