Explorer
SQL

Find names that appear in both the `students` and `teachers` tables.

Problem Statement

<p>Find names that appear in both the <code>students</code> and <code>teachers</code> tables.</p>

Examples

Input: students table: +---------+ | name | +---------+ | Alice | | Bob | | Charlie | +---------+ teachers table: +---------+ | name | +---------+ | Bob | | Charlie | | Dana | +---------+

Output: +---------+ | name | +---------+ | Bob | | Charlie | +---------+

Explanation: Only records matching the specified criteria are returned in the final result.

Complexity

Time Complexity: -

Space Complexity: -

Hints

šŸ’” Hint 1: Identify the filtering conditions needed to isolate the target rows from the table. šŸ’” Hint 2: Use appropriate operators: = for exact matches, LIKE for wildcard patterns, IN for sets, or BETWEEN for numeric/date ranges. šŸ’” Hint 3: Combine filters with AND/OR if multiple conditions exist: SELECT <cols> FROM <table> WHERE <condition>;

Editorial & Approach

Problem Overview & Intuition

To solve "INTERSECT", we query the relational database engine using declarative SQL. The goal is to find names that appear in both the `students` and `teachers` tables. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

  1. Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
  2. Construct Filtering & Logic: Apply row projections to isolate the requested data.
  3. Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT name FROM students INTERSECT 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.

INTERSECT

Medium

Find names that appear in both the students and teachers tables.

Example Scenarios
1Example 1
Input:
students table
name
Alice
Bob
Charlie
teachers table
name
Bob
Charlie
Dana
Output:
name
Bob
Charlie
Explanation:

Only records matching the specified criteria are returned in the final result.

SQL Editor
Loading Editor...
Query Results

Run a query to see results here.