Find names that are in `students` but NOT in `teachers`.
Problem Statement
<p>Find names that are in <code>students</code> but NOT in <code>teachers</code>.</p>
Examples
Input: students table: +---------+ | name | +---------+ | Alice | | Bob | | Charlie | +---------+ teachers table: +------+ | name | +------+ | Bob | | Dana | +------+
Output: +---------+ | name | +---------+ | Alice | | Charlie | +---------+
Explanation: The query retrieves the requested records satisfying all problem requirements.
Complexity
Time Complexity: -
Space Complexity: -
Hints
š” Hint 1:
Carefully review the required fields in the table schema.
š” Hint 2:
Construct a clean SELECT statement specifying the requested columns or wildcard *.
š” Hint 3:
Structure: SELECT <columns> FROM <table_name>;
Editorial & Approach
Problem Overview & Intuition
To solve "EXCEPT", we query the relational database engine using declarative SQL. The goal is to find names that are in `students` but not in `teachers`. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.
Step-by-Step Approach
- Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
- Construct Filtering & Logic: Apply row projections to isolate the requested data.
- Format & Order: Ensure columns match the expected project schema in order.
Optimal Implementation (SQL)
SELECT name FROM students EXCEPT SELECT name FROM teachers;Complexity Analysis
Time Complexity
O(N)
Space Complexity
O(1)
Key Considerations & Edge Cases
- Empty Tables: The query executes safely returning zero rows without syntax error.
- NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
- Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.