Joins Deep Dive Problems

Problem 1: Employees and Their Departments

Problem Statement

A company stores information about its employees and departments. Some departments may exist even if no employees are currently assigned to them. The goal is to produce a complete view of every department, showing employee details where they exist, and NULL where they don't.

Business context New departments are sometimes created before anyone is hired into them, or a department could temporarily have zero staff due to reorganization. A complete company structure report needs to reflect this reality, not silently hide empty departments.

Goal Return all departments, including those without any employees, with employee columns shown as NULL for departments with no assigned staff.

Input Two tables: Employee (with a dept_id foreign key) and Department.

Output employee_id, name, and dept_name for every department, employee fields NULL where a department has no employees.

Table Structure

Table: Employee

Table: Department

Column Meaning

• employee_id — unique identifier for each employee, primary key of the Employee table
• name — the employee's name
• dept_id (Employee table) — which department the employee belongs to
• dept_id (Department table) — unique identifier for each department, primary key of the Department table
• dept_name — the department's name

Important Notes

• dept_id is the primary key of Department, and a foreign-key-style reference from Employee.
• The requirement explicitly guarantees "departments may exist even if no employees are currently assigned," this is the direct signal that Department needs to be the table whose rows are all preserved, regardless of matches.
• Multiple employees can share the same dept_id (many employees to one department), but this specific example only shows one employee per department, keep in mind a department with several employees would produce one output row per employee.

Example

Input: Employee

Input: Department

Expected Output

CREATE TABLE Employee (employee_id INTEGER, name TEXT, dept_id INTEGER);
INSERT INTO Employee VALUES
  (1, 'Alice', 10),
  (2, 'Bob', 20);
CREATE TABLE Department (dept_id INTEGER, dept_name TEXT);
INSERT INTO Department VALUES
  (10, 'HR'),
  (20, 'IT'),
  (30, 'Finance');

Explanation: Finance (dept_id 30) exists as a department, but has no employees assigned to it, so its row still appears, with employee_id and name shown as NULL.

Intuition

Think of a company directory where department signage is put up on office doors before anyone actually moves in. Even an empty office still has its department nameplate on the door, HR, IT, Finance, all clearly labeled, regardless of whether anyone currently sits inside. This report needs to walk past every single door in the building, department by department, and note down who (if anyone) currently works there.

Mental Model

Picture the Department table as the master list of "rooms that officially exist" in a building. For each room, you check whether anyone's name tag is posted inside (matching via dept_id). If a room has a name tag, you write down that person's details next to the room name. If the room is empty, you still list the room, just with blank spaces where a name would normally go.

Thought Process

1. Which table needs every single row preserved? Department, since the requirement explicitly says departments without employees must still appear.
2. Which join type keeps every row of a specific table, filling the other side with NULL when unmatched? A LEFT JOIN, with Department as the left table, or equivalently, a RIGHT JOIN with Department as the right table (both achieve the same result, just written with the tables in a different order).
3. What should the matching condition be? Employee.dept_id = Department.dept_id.

Approach

Step 1: Start from the Department table This ensures every department row is guaranteed to appear in the final result, regardless of whether any employees match it.

Step 2: Join Employee using dept_id Connect the two tables on the shared dept_id column.

Step 3: Use RIGHT JOIN (Employee RIGHT JOIN Department), or equivalently LEFT JOIN with Department first This preserves every department row, filling employee columns with NULL where no employee is assigned.

SQL Query

SELECT
    e.employee_id,
    e.name,
    d.dept_name
FROM Employee e
-- RIGHT JOIN ensures every Department row survives, even with no matching employee
RIGHT JOIN Department d
ON e.dept_id = d.dept_id;

Query Explanation

• FROM Employee e — the left table in this specific join direction.
• RIGHT JOIN Department d — since Department is the right table here, every single row from Department is guaranteed to appear in the output, matched or not.
• ON e.dept_id = d.dept_id — the matching condition connecting an employee to their department.
• Where no employee matches a given department, e.employee_id and e.name become NULL automatically, exactly as an outer join is designed to behave.

Dry Run

Employee

Department

↓

Match each department against Employee

↓

Final output

SQL Execution Flow

FROM Employee e
   ↓
RIGHT JOIN Department d ON e.dept_id = d.dept_id
   ↓
For every Department row, find matching Employee row(s), or NULL if none
   ↓
SELECT employee_id, name, dept_name
   ↓
Return final output (every department preserved)

Time Complexity

Time Complexity: O(n + m), or faster with an index on dept_id Each Department row needs to be checked against the Employee table for a match; with an index on Employee.dept_id, this lookup is fast per department, making the overall cost close to linear in the combined row counts.

Space Complexity: O(n + m) The combined result set needs to be held long enough to be returned, roughly proportional to the number of departments plus the number of employees.

Why: This is a straightforward outer join with no aggregation or sorting layered on top, so its cost is dominated by the join matching itself, not any secondary processing.

Common Mistakes to Avoid

Mistake 1: Using INNER JOIN instead of an outer join

Wrong:

SELECT e.employee_id, e.name, d.dept_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;

This would silently drop Finance entirely, since INNER JOIN only keeps matched rows.

Correct: Use RIGHT JOIN (with Department as the right table) or LEFT JOIN (with Department as the left table), to guarantee every department survives.

Mistake 2: Starting FROM Department but still using an INNER-style plain JOIN

Wrong:

SELECT e.employee_id, e.name, d.dept_name
FROM Department d
JOIN Employee e ON e.dept_id = d.dept_id;

A plain JOIN defaults to INNER JOIN, so simply reordering the FROM clause without also specifying LEFT still drops unmatched departments.

Correct:

SELECT e.employee_id, e.name, d.dept_name
FROM Department d
LEFT JOIN Employee e ON e.dept_id = d.dept_id;

Mistake 3: Filtering with a WHERE clause that accidentally removes NULL rows

Wrong:

SELECT e.employee_id, e.name, d.dept_name
FROM Employee e
RIGHT JOIN Department d ON e.dept_id = d.dept_id
WHERE e.employee_id IS NOT NULL;

This silently converts the query back into INNER JOIN-like behavior, removing Finance again, since it explicitly filters out rows where the employee side is NULL.

Correct: Don't add any WHERE filter on the employee columns if the goal is to preserve every department, exactly as the base query does.

Interview Follow-up Questions

• "How would you count how many employees are in each department, including departments with zero employees?"
• "How would you rewrite this using LEFT JOIN instead of RIGHT JOIN?"
• "What would happen if a department_id appeared in Employee but not in Department at all?"
• "How would you find only the departments that have zero employees?"

Similar Problems

• Problem 2 (Customer Orders Overview), which uses the same "preserve all rows from one side" pattern with LEFT JOIN instead
• Any "complete listing with optional related data" problem, like listing all products even those never ordered, or all categories even those with no items

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement says "including those without any X" or "even if no Y exists" → use an outer join (LEFT or RIGHT), not INNER JOIN, with the "must always appear" table on the preserved side
• Tempted to add a WHERE filter after an outer join → double-check it doesn't accidentally filter out the very NULL rows the outer join was meant to preserve

Problem 2: Customer Orders Overview

Problem Statement

An e-commerce platform stores customers and their purchase history separately. Some customers may have registered but never placed an order. The goal is to list every customer, along with their order details where they exist.

Business context For reporting and retention analysis, a business needs to see registered-but-inactive customers just as clearly as active ones, hiding them would understate the true customer base.

Goal Return all customers, along with order details, showing NULL for order columns where a customer has no orders.

Input Customers and Orders tables, connected by customer_id.

Output customer_id, name, order_id, amount, for every customer (with repeated customer rows if they have multiple orders).

Table Structure

Table: Customers

Table: Orders

Column Meaning

• customer_id — unique identifier for the customer, primary key of Customers
• name — customer's name
• order_id — unique identifier for the order, primary key of Orders
• customer_id (Orders table) — references which customer placed this order
• amount — the order's monetary value

Important Notes

• A customer can have zero, one, or multiple orders, this is a one-to-many relationship from Customers to Orders.
• The requirement explicitly calls out that customers with zero orders must still show up, with NULL order fields, this is the clearest possible signal for LEFT JOIN with Customers as the left (preserved) table.

Example

Input: Customers

Input: Orders

Expected Output

CREATE TABLE Customers (customer_id INTEGER, name TEXT);
INSERT INTO Customers VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');
CREATE TABLE Orders (order_id INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO Orders VALUES
  (101, 1, 500),
  (102, 1, 300),
  (103, 2, 200);

Explanation: Charlie has not placed any orders, so his order-related fields appear as NULL, while Alice appears twice, once per order she placed.

Intuition

Think of a customer loyalty report where every signed-up member gets listed, regardless of whether they've ever actually bought anything. Someone who signed up but never purchased is still a real customer worth tracking, for win-back campaigns or engagement analysis, they shouldn't vanish from the report just because the Orders table has nothing about them.

Mental Model

Picture the Customers table as a guest list for an ongoing event, everyone on the list gets checked in, whether or not they've picked up any merchandise from the merchandise table (Orders) yet. If a guest picked up multiple items, they show up multiple times in the merchandise log, once per item. If they picked up nothing, their merchandise columns simply stay blank.

Thought Process

1. Which table must be fully preserved? Customers, since every registered customer needs to appear regardless of order history.
2. Which join type keeps every row of the left table, NULLing the right side when unmatched? LEFT JOIN.
3. What's the matching condition? Customers.customer_id = Orders.customer_id.

Approach

Step 1: Start from the Customers table Ensures every customer is included in the base of the query.

Step 2: Join Orders using customer_id

Step 3: Use LEFT JOIN So customers without any orders are not filtered out, their order-related columns simply become NULL.

SQL Query

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM Customers c
-- LEFT JOIN preserves every customer, even those without a single matching order
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;

Query Explanation

• FROM Customers c — the left table, guaranteed to be fully preserved.
• LEFT JOIN Orders o ON c.customer_id = o.customer_id — attaches matching order rows where they exist; customers with multiple orders produce multiple output rows (one per order), and customers with none produce a single row with NULL order fields.

Dry Run

Customers

Orders

↓

Match each customer against Orders

↓

Final output

SQL Execution Flow

FROM Customers c
   ↓
LEFT JOIN Orders o ON c.customer_id = o.customer_id
   ↓
For every customer, find all matching orders, or NULL if none exist
   ↓
SELECT customer_id, name, order_id, amount
   ↓
Return final output (every customer preserved, possibly with repeated rows)

Time Complexity

Time Complexity: O(n + m), faster with an index on Orders.customer_id Each customer needs to be matched against potentially multiple order rows; with a proper index, this lookup is efficient.

Space Complexity: O(n + m) The result set size depends on the total number of customer-order pairings, at minimum one row per customer, more if customers have multiple orders.

Why: This is a one-to-many LEFT JOIN, the row count of the output can exceed the row count of either input table individually, since customers with multiple orders contribute multiple rows.

Common Mistakes to Avoid

Mistake 1: Using INNER JOIN, silently dropping customers with no orders

Wrong:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

This drops Charlie entirely, violating the requirement to show all customers.

Correct: Use LEFT JOIN, exactly as shown in the main solution.

Mistake 2: Adding a WHERE clause that filters out the NULL order rows

Wrong:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NOT NULL;

This converts the query back into INNER JOIN-like behavior, removing Charlie again.

Correct: Leave off any WHERE filter on the order columns, unless the goal is specifically to find customers without orders (in which case, flip the condition to IS NULL, as shown in the concept notes' LEFT JOIN Scenario 2).

Mistake 3: Expecting one row per customer regardless of their order count

Why it happens: It's easy to assume a join always produces exactly one row per row in the left table.

Why it matters: A customer with multiple orders (like Alice, with 2 orders) naturally produces multiple output rows, once per matched order, this is standard one-to-many join behavior, not a bug.

Interview Follow-up Questions

• "How would you calculate the total amount spent per customer, including customers with $0 in orders?"
• "How would you find only the customers who have never placed an order?"
• "What would this query look like if a customer could have orders from multiple different Orders-like tables (like Problem 2 in the previous UNION chapter)?"

Similar Problems

• Problem 1 (Employees and Their Departments), the same LEFT JOIN "preserve all rows" pattern, just applied the opposite direction
• Any customer relationship management (CRM) report requiring full customer visibility regardless of activity

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement says "all X, along with optional Y" or explicitly calls out records that may not have related data → LEFT JOIN, with the "must always appear" table first
• Some records are expected to have multiple matches on the other side → expect repeated rows in the output, this is normal one-to-many join behavior, not an error

Problem 3: Employees With Confirmed Salary Records

Problem Statement

An HR system stores employee information and salary details separately. Only employees who have both personal details and a salary record are considered valid for payroll processing.

Business context Payroll can only run correctly for employees whose salary has actually been recorded, an employee missing a salary entry (perhaps due to onboarding lag) shouldn't be included in a payroll run yet.

Goal Return only employees who have a matching salary record.

Input Employees and Salaries tables, connected by employee_id.

Output employee_id, name, salary, for every employee that has a salary record.

Table Structure

Table: Employees

Table: Salaries

Column Meaning

• employee_id — primary key in both tables individually; links a personal record to a salary record
• name — the employee's name
• salary — the recorded salary amount

Important Notes

• This is explicitly framed as needing both records present, described directly as finding "the intersection of the two tables," which is the clearest possible signal for INNER JOIN.
• Employees with no salary record, and any salary record with no matching employee, should both be automatically excluded.

Example

Input: Employees

Input: Salaries

Expected Output

CREATE TABLE Employees (employee_id INTEGER, name TEXT);
INSERT INTO Employees VALUES
  (1, 'Aarav'),
  (2, 'Neha'),
  (3, 'Rohan');
CREATE TABLE Salaries (employee_id INTEGER, salary INTEGER);
INSERT INTO Salaries VALUES
  (1, 50000),
  (3, 60000);

Explanation: Neha has no salary record, so she is excluded entirely, only employees present in both tables appear in the output.

Intuition

Think of a payroll clerk who needs both a completed HR file and a signed salary agreement before they can cut a paycheck. If either document is missing, that person simply doesn't get processed this cycle, no partial or placeholder payment, they're just skipped until both pieces of information are on file together.

Mental Model

Picture two overlapping circles in a classic Venn diagram, one circle is "has an employee record," the other is "has a salary record." INNER JOIN specifically returns only the overlapping middle section, people who exist in both circles at once. Anyone sitting only in one circle or the other is left out entirely.

Thought Process

1. Does this need every row from one side preserved, or only matches? Only matches, since an employee without a salary shouldn't be "valid for payroll" per the problem statement.
2. Which join type keeps only rows matching in both tables? INNER JOIN.
3. What's the matching condition? Employees.employee_id = Salaries.employee_id.

Approach

Step 1: Start from the Employees table

Step 2: Join Salaries using employee_id

Step 3: Use INNER JOIN So only employees with a confirmed matching salary record survive into the final result.

SQL Query

SELECT
    e.employee_id,
    e.name,
    s.salary
FROM Employees e
-- INNER JOIN keeps only employees who have a matching salary record
INNER JOIN Salaries s
ON e.employee_id = s.employee_id;

Query Explanation

• INNER JOIN Salaries s ON e.employee_id = s.employee_id — this is the entire logic of the query, only rows where an employee_id exists in both Employees and Salaries make it into the output, everything else (unmatched employees or unmatched salary records) is automatically dropped.

Dry Run

Employees

Salaries

↓

Check each employee against Salaries

↓

Final output (only matches survive)

SQL Execution Flow

FROM Employees e
   ↓
INNER JOIN Salaries s ON e.employee_id = s.employee_id
   ↓
Keep only rows where employee_id exists in BOTH tables
   ↓
SELECT employee_id, name, salary
   ↓
Return final output (unmatched rows from either side dropped entirely)

Time Complexity

Time Complexity: O(n + m), faster with an index on employee_id in both tables Matching can be done efficiently with proper indexing on the join column in both tables.

Space Complexity: O(k), where k is the number of matched rows (k ≤ min(n, m)) Only the matched result set needs to be held, which is at most as large as the smaller of the two tables.

Why: INNER JOIN's output can never be larger than what both tables' matches allow, this is generally the "cheapest" join type in terms of output size, since unmatched rows from either side never make it into the result at all.

Common Mistakes to Avoid

Mistake 1: Using LEFT JOIN and forgetting to filter, letting unmatched employees slip through

Wrong:

SELECT e.employee_id, e.name, s.salary
FROM Employees e
LEFT JOIN Salaries s ON e.employee_id = s.employee_id;

This would include Neha with salary = NULL, directly violating the requirement to only show employees "valid for payroll."

Correct: Use INNER JOIN directly, as shown in the main solution, or if starting from LEFT JOIN for some reason, add WHERE s.salary IS NOT NULL to filter unmatched rows out (though INNER JOIN is simpler and more direct here).

Mistake 2: Assuming this requires some special "intersection" SQL command

Why it happens: The problem's intuition explicitly uses the word "intersection," which might make someone search for a dedicated INTERSECT keyword.

Why it matters: While MySQL lacks direct INTERSECT support (as covered in the UNION chapter), when working with exactly two tables and wanting a simple row-level intersection based on a shared key, INNER JOIN already achieves this directly, no manual UNION ALL + GROUP BY + HAVING trick is needed here, that technique is specifically for combining and comparing values from a single combined column, not matching full rows across two structurally different tables.

Interview Follow-up Questions

• "How would you find employees who do NOT have a salary record?"
• "How would you also include salary records that don't correspond to any actual employee?"
• "What's the difference between this INNER JOIN approach and building an intersection manually with UNION ALL + GROUP BY + HAVING?"

Similar Problems

• Problem 6 (Match Employees With Their Salaries), which solves this exact same problem using implicit join syntax instead
• The Intersection technique from the UNION chapter, a useful contrast for understanding when a manual technique is needed versus when INNER JOIN already solves the problem directly

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement says "only if both records exist," "valid for," or explicitly mentions an intersection between two tables sharing a key → INNER JOIN
• Don't reach for the manual UNION ALL + GROUP BY + HAVING intersection technique when a direct INNER JOIN on a shared key already solves the problem more simply

Problem 4: Generate All Possible User-Category Pairs

Problem Statement

A recommendation system wants to precompute every possible combination of users and content categories, so personalized suggestions can later be applied on top of this complete grid.

Business context Recommendation engines often start by generating a complete "all possibilities" grid, then later filter or rank it down based on actual user behavior or preferences. Generating the full grid upfront makes downstream scoring logic simpler.

Goal Generate every possible combination of users and categories, with no matching condition involved at all.

Input Users and Categories tables, with no shared relationship between them.

Output user_id, name, category_id, category, for every possible user-category pairing.

Table Structure

Table: Users

Table: Categories

Column Meaning

• user_id — unique identifier for a registered user
• name — the user's name
• category_id — unique identifier for a content category
• category — the category's name

Important Notes

• There's no actual relationship between Users and Categories here, no shared key, no foreign key, this is explicitly about generating every possible combination, not matching related data.
• The total output row count will always be (number of users) × (number of categories).

Example

Input: Users

Input: Categories

Expected Output

CREATE TABLE Users (user_id INTEGER, name TEXT);
INSERT INTO Users VALUES
  (1, 'Aarav'),
  (2, 'Neha');
CREATE TABLE Categories (category_id INTEGER, category TEXT);
INSERT INTO Categories VALUES
  (10, 'Sports'),
  (20, 'Music');

Explanation: 2 users × 2 categories = 4 total combinations, every user is paired with every category, exactly once each.

Intuition

Think of preparing a seating chart for a trivia event where every contestant must eventually be quizzed on every possible topic. Before the event even starts, you'd want a master grid listing every contestant next to every topic, contestant 1 with topic A, contestant 1 with topic B, contestant 2 with topic A, and so on, so you have a complete starting point to fill in scores later.

Mental Model

Picture a multiplication table, rows are users, columns are categories, and every single cell in that grid represents one user-category pairing that needs to exist in the output. There's no "matching rule" here at all, every row pairs with every column, full stop.

Thought Process

1. Is there a shared key between Users and Categories? No, there's no natural relationship to match on.
2. What kind of join produces every possible combination, with no matching condition? CROSS JOIN.
3. What will the row count be? Exactly (number of users) × (number of categories), which serves as a good sanity check after writing the query.

Approach

Step 1: Start from the Users table

Step 2: Combine with the Categories table using CROSS JOIN No ON condition is needed or used.

Step 3: Verify the row count Total rows should equal users × categories.

SQL Query

SELECT
    u.user_id,
    u.name,
    c.category_id,
    c.category
FROM Users u
-- CROSS JOIN produces every possible user-category combination, no ON needed
CROSS JOIN Categories c;

Query Explanation

• CROSS JOIN Categories c — no matching condition is used at all, every row in Users is combined with every row in Categories, producing the full Cartesian product of both tables.

Dry Run

Users

Categories

↓

Pair every user with every category

↓

Final output (2 × 2 = 4 rows)

SQL Execution Flow

FROM Users u
   ↓
CROSS JOIN Categories c (no condition evaluated at all)
   ↓
Every row in Users paired with every row in Categories
   ↓
SELECT user_id, name, category_id, category
   ↓
Return final output (users × categories total rows)

Time Complexity

Time Complexity: O(n × m) Every row in Users is paired with every row in Categories, so the output size (and the work to generate it) grows as the product of both table sizes, not their sum.

Space Complexity: O(n × m) The full combined result set needs to be produced and held, which can become very large very quickly as either table grows.

Why: Unlike every other join type covered so far, CROSS JOIN has no filtering mechanism at all, its entire purpose is to produce the complete product of both tables, which is inherently more expensive than a matched join as table sizes increase.

Common Mistakes to Avoid

Mistake 1: Adding an ON condition to a CROSS JOIN unnecessarily

Why it happens: After learning several join types that all require ON, it can feel wrong to write a join without one.

Unnecessary (and invalid in standard CROSS JOIN syntax, which takes no ON clause):

SELECT u.user_id, c.category_id
FROM Users u
CROSS JOIN Categories c
ON u.user_id = c.category_id;

Correct: CROSS JOIN intentionally takes no ON condition:

SELECT u.user_id, c.category_id
FROM Users u
CROSS JOIN Categories c;

Mistake 2: Using CROSS JOIN by accident when a regular JOIN was intended

Why it happens: Forgetting an ON clause on a regular JOIN, or forgetting a WHERE clause on an implicit join, both silently degrade into the same Cartesian product behavior as CROSS JOIN.

Why it matters: If two tables have an actual relationship, but the matching condition is accidentally left out, the result balloons to an enormous, meaningless combination of every row with every other row, rather than the intended, much smaller matched result.

Correct: Reserve CROSS JOIN specifically for situations (like this one) where generating every possible combination is genuinely the goal, and always double-check that a regular join's ON clause (or implicit join's WHERE clause) hasn't been accidentally omitted elsewhere.

Mistake 3: Not anticipating how large the output could get on real tables

Why it happens: The example here only has 2 users and 2 categories, making 4 rows feel trivial.

Why it matters: On a real system with, say, 50,000 users and 200 categories, this same CROSS JOIN would produce 10,000,000 rows, which could seriously strain memory, storage, or downstream processing if not anticipated and planned for.

Interview Follow-up Questions

• "How would you modify this to exclude combinations where a user has already interacted with that category?"
• "How would you estimate the output row count before running a CROSS JOIN on large tables?"
• "What's the difference between a CROSS JOIN and an implicit join that's missing its WHERE clause?"

Similar Problems

• Generating all date × product combinations for a sales forecasting grid
• Generating all customer × coupon combinations before applying eligibility filters
• Any "precompute the full combination space, then filter or rank later" system design pattern

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement explicitly asks for "all possible combinations," "every pairing," or a full grid/matrix between two independent sets → CROSS JOIN, with no ON condition
• No natural shared key exists between the two tables being combined → this is a strong signal that a matching join doesn't apply here at all

Problem 5: Employees With or Without Salary Records

Problem Statement

An HR system has synchronization issues: some employees are missing salary records, and some salary records don't have a matching employee. The goal is to return every employee_id from either table, preserving whichever data is actually available.

Business context Data sync issues between two systems (an HR system and a payroll system, say) are extremely common in real companies. Being able to see the full picture, every employee, every salary record, and specifically where they don't line up, is essential for catching and fixing these sync problems.

Goal Return all employee_ids from both tables: employees with a salary, employees without a salary, and salary records with no matching employee.

Input Employees and Salaries tables, both potentially containing unmatched rows relative to the other.

Output employee_id, name, salary, for every row from either table, sorted by employee_id ascending.

Table Structure

Table: Employees

Table: Salaries

Column Meaning

• employee_id — primary key independently in both tables; used to connect them
• name — employee's name (only present in Employees)
• salary — salary amount (only present in Salaries)

Important Notes

• This is explicitly a FULL OUTER JOIN requirement: every row from both tables must appear, matched or not.
• Since MySQL doesn't support FULL OUTER JOIN directly (as covered in the concept notes), this needs to be built manually using two LEFT JOINs (one from each table's perspective) combined with UNION.

Example

Input: Employees

Input: Salaries

Expected Output

CREATE TABLE Employees (employee_id INTEGER, name TEXT);
INSERT INTO Employees VALUES
  (1, 'Aarav'),
  (2, 'Neha'),
  (3, 'Rohan');
CREATE TABLE Salaries (employee_id INTEGER, salary INTEGER);
INSERT INTO Salaries VALUES
  (1, 50000),
  (4, 65000);

Explanation: Employee 1 has both records (full data). Employees 2 and 3 have no salary (salary is NULL). Employee 4's salary record has no matching employee (name is NULL).

Intuition

Think of reconciling two separate spreadsheets after a system migration, one team's spreadsheet lists employees, another team's lists salary payments. To find every discrepancy (missing salaries, or orphaned salary payments with no matching employee), you need to lay both spreadsheets side by side and specifically flag anything that doesn't perfectly align, from either direction.

Mental Model

Picture two flashlights shining from opposite ends of a dark room, one from the "Employees" side, one from the "Salaries" side. The first flashlight (LEFT JOIN from Employees) illuminates every employee, and shows a salary wherever one exists. The second flashlight (LEFT JOIN from Salaries) illuminates every salary record, and shows an employee name wherever one exists. By combining what both flashlights reveal (and being careful not to double-count anything both flashlights already showed), you end up seeing the entire room, including the corners only one flashlight could reach.

Thought Process

1. Does either table alone cover every needed row? No, a plain LEFT JOIN from Employees would miss orphaned salary records (like employee_id 4), and a plain LEFT JOIN from Salaries would miss employees without any salary at all.
2. How do I capture both "every employee" and "every orphaned salary" without double-counting matched rows? Run a LEFT JOIN from Employees to Salaries (captures every employee, salary NULL if missing), and separately run a LEFT JOIN from Salaries to Employees (captures every salary record, name NULL if missing), then combine both with UNION, which naturally removes the exact duplicate rows that both queries would produce for already-matched employees (like employee 1).
3. What's the final sort requirement? Ascending by employee_id.

Approach

Step 1: Retrieve all employees, with or without a salary LEFT JOIN from Employees to Salaries.

Step 2: Retrieve all salary records, with or without a matching employee LEFT JOIN from Salaries to Employees.

Step 3: Combine both results using UNION This merges both pieces and removes duplicate rows, matched employees would otherwise appear identically in both halves.

Step 4: Sort by employee_id ascending

SQL Query

SELECT
    e.employee_id AS employee_id,
    e.name,
    s.salary
FROM Employees e
LEFT JOIN Salaries s
ON e.employee_id = s.employee_id

UNION

SELECT
    s.employee_id,
    e.name,
    s.salary
FROM Salaries s
LEFT JOIN Employees e
ON s.employee_id = e.employee_id

ORDER BY employee_id;

Query Explanation

• First SELECT (Employees LEFT JOIN Salaries) — captures every employee, whether or not they have a salary; employees without one show salary = NULL.
• Second SELECT (Salaries LEFT JOIN Employees) — captures every salary record, whether or not it has a matching employee; salary records without one show name = NULL. Note it selects s.employee_id (not e.employee_id) as the first column, since for an orphaned salary record, e.employee_id would itself be NULL, we specifically want the actual employee_id from the Salaries side to appear in the output.
• UNION — merges both halves together, and removes exact duplicate rows. A fully matched employee (like employee 1) would produce an identical row from both halves (1, Aarav, 50000), UNION collapses this down to a single occurrence.
• ORDER BY employee_id — sorts the final, merged, deduplicated result.

Dry Run

Employees

Salaries

↓

First SELECT: Employees LEFT JOIN Salaries

↓

Second SELECT: Salaries LEFT JOIN Employees

↓

Combine with UNION (removes the duplicate employee 1 row, which appears identically in both halves)

↓

Apply ORDER BY employee_id (already in order here)

SQL Execution Flow

Employees LEFT JOIN Salaries (every employee, salary NULL if missing)
   ↓
UNION
   ↓
Salaries LEFT JOIN Employees (every salary record, name NULL if missing)
   ↓
Combine both, remove exact duplicate rows
   ↓
ORDER BY employee_id
   ↓
Return final combined output

Time Complexity

Time Complexity: O((n + m) log(n + m)) Both LEFT JOINs run in roughly O(n + m) with proper indexing, and the UNION requires a deduplication step (typically a sort or hash), which dominates the overall cost.

Space Complexity: O(n + m) The combined result from both halves needs to be held to perform deduplication and sorting.

Why: This query effectively does the work of two separate joins plus a UNION's deduplication and a final sort, making it noticeably more expensive than a single plain join, this is the real cost of working around MySQL's lack of a native FULL OUTER JOIN.

Common Mistakes to Avoid

Mistake 1: Using UNION ALL instead of UNION

Wrong:

SELECT e.employee_id, e.name, s.salary FROM Employees e LEFT JOIN Salaries s ON e.employee_id = s.employee_id
UNION ALL
SELECT s.employee_id, e.name, s.salary FROM Salaries s LEFT JOIN Employees e ON s.employee_id = e.employee_id
ORDER BY employee_id;

This would show employee 1 (Aarav, 50000) twice in the output, since UNION ALL doesn't remove the duplicate row both halves legitimately produce for any fully matched employee.

Correct: Use plain UNION here specifically because deduplication is needed, unlike Problem 3 from the UNION chapter (which deliberately needed UNION ALL for a completely different reason).

Mistake 2: Selecting e.employee_id in the second SELECT instead of s.employee_id

Wrong:

SELECT
    e.employee_id, -- WRONG: this will be NULL for orphaned salary records
    e.name,
    s.salary
FROM Salaries s
LEFT JOIN Employees e
ON s.employee_id = e.employee_id;

For employee_id 4 (an orphaned salary record with no matching employee), e.employee_id would itself be NULL, since there's no matching Employees row at all, silently losing the employee_id value that Salaries actually has.

Correct: Select s.employee_id in this second half, since Salaries is the side guaranteed to have that value:

SELECT s.employee_id, e.name, s.salary
FROM Salaries s
LEFT JOIN Employees e ON s.employee_id = e.employee_id;

Mistake 3: Forgetting that column names in a UNION come from the first SELECT

Why it happens: It's easy to assume each SELECT's own column aliases matter independently.

Why it matters: Even though the second SELECT lists columns in the order s.employee_id, e.name, s.salary, the final combined result's column header still comes from the first SELECT (e.employee_id, e.name, s.salary), this doesn't change the data, but it's worth understanding for clarity when reading or debugging a UNION query.

Interview Follow-up Questions

• "How would you write this same result if your database supported FULL OUTER JOIN directly?"
• "How would you specifically isolate only the mismatched rows (not the fully matched ones)?"
• "Why does selecting s.employee_id versus e.employee_id matter in the second half of this query?"
• "How would this query's cost compare to a native FULL OUTER JOIN, if your database supported one?"

Similar Problems

• The FULL OUTER JOIN section in the concept notes (students/payments example), which uses this exact same LEFT JOIN + LEFT JOIN + UNION pattern
• Any data reconciliation problem between two systems that may have diverged

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement explicitly mentions records existing "in either table," or specifically calls out both "missing from A" and "missing from B" cases → manual FULL OUTER JOIN via two LEFT JOINs + UNION, in MySQL
• Building this pattern → always select the join key from whichever side of each LEFT JOIN is guaranteed not to be NULL

Problem 6: Match Employees With Their Salaries

Problem Statement

A company needs the same result as Problem 3 (employees who have a salary record), but this time, written using only implicit join syntax.

Business context Understanding implicit joins is important both for reading older, legacy codebases, and for solidifying the underlying logic of how a JOIN actually works beneath the more modern, explicit syntax.

Goal Return employees who have salary records, using comma-separated FROM and a WHERE-based matching condition.

Input Employees and Salaries tables.

Output employee_id, name, salary, for every employee with a matching salary record.

Table Structure

Same as Problem 3: Employees (employee_id, name) and Salaries (employee_id, salary).

Column Meaning

Same as Problem 3.

Important Notes

• The requirement explicitly restricts the solution to implicit join syntax only, no explicit JOIN ... ON keywords are allowed here.
• Functionally, this needs to behave identically to Problem 3's INNER JOIN, only unmatched rows are excluded on both sides.

Example

Input: Employees

Input: Salaries

Expected Output

CREATE TABLE Employees (employee_id INTEGER, name TEXT);
INSERT INTO Employees VALUES
  (1, 'Aarav'),
  (2, 'Neha'),
  (3, 'Rohan');
CREATE TABLE Salaries (employee_id INTEGER, salary INTEGER);
INSERT INTO Salaries VALUES
  (1, 50000),
  (3, 60000);

Explanation: Neha has no salary record, so she's excluded, only rows where employee_id matches in both tables are returned, exactly the same outcome as Problem 3, just written differently.

Intuition

Before modern JOIN syntax existed, SQL used this comma-and-WHERE style exclusively. Conceptually, listing two tables with a comma first creates every possible pairing between them (just like a CROSS JOIN), and the WHERE clause is what narrows that massive combined set down to only the pairings that actually make sense, in this case, only pairings where the employee_id genuinely matches.

Mental Model

Picture dumping every single employee card and every single salary card onto one giant table, every possible pairing of one employee card with one salary card, all mixed together. Then, you walk through this entire messy pile and specifically pull out only the pairs where the employee_id written on both cards actually matches. Everything else gets tossed aside.

Thought Process

1. How do I list two tables without using JOIN? A comma-separated list in FROM: FROM Employees e, Salaries s.
2. Where does the matching condition go, since there's no ON clause available? Inside WHERE: WHERE e.employee_id = s.employee_id.
3. Does this behave like INNER JOIN or an outer join? Like INNER JOIN, since only rows satisfying the WHERE condition survive, unmatched rows from either side are dropped.

Approach

Step 1: List both tables using commas in FROM

Step 2: Write the join condition in WHERE

Step 3: Recognize this behaves exactly like an INNER JOIN Rows without a matching employee_id on both sides are automatically excluded.

SQL Query

SELECT
    e.employee_id,
    e.name,
    s.salary
FROM Employees e, Salaries s
-- The matching condition lives in WHERE, since implicit join syntax has no ON clause
WHERE e.employee_id = s.employee_id;

Query Explanation

• FROM Employees e, Salaries s — conceptually forms a full Cartesian product of every Employees row paired with every Salaries row, exactly like a CROSS JOIN would.
• WHERE e.employee_id = s.employee_id — filters that massive intermediate set down to only pairs where the employee_id genuinely matches on both sides, this single WHERE clause is doing all the real "joining" logic here.

Dry Run

Employees

Salaries

↓

Step 1: Form the full Cartesian product (conceptually, before filtering) — 3 employees × 2 salaries = 6 pairs

↓

Step 2: Apply WHERE e.employee_id = s.employee_id

↓

Final output

SQL Execution Flow

FROM Employees e, Salaries s
   ↓
Form the conceptual Cartesian product of every row pairing
   ↓
WHERE e.employee_id = s.employee_id
   ↓
Keep only pairs where the employee_id genuinely matches
   ↓
SELECT employee_id, name, salary
   ↓
Return final output

Time Complexity

Time Complexity: O(n × m) in the worst case (if the database literally materializes the full Cartesian product before filtering), though modern query optimizers typically rewrite this internally into an efficient join plan, closer to O(n + m) with proper indexing, rather than truly generating and then filtering every possible pairing.

Space Complexity: O(k), where k is the number of matched rows in the final result Though depending on the optimizer, intermediate space usage could theoretically be much larger before filtering, in practice, modern databases are smart enough to avoid this.

Why: Conceptually, implicit join syntax is "generate everything, then filter," which sounds expensive, but real-world query optimizers almost always recognize the WHERE equality condition and execute it efficiently, similar to how an explicit INNER JOIN would, this conceptual cost is worth understanding, but isn't necessarily the literal execution plan used in practice.

Common Mistakes to Avoid

Mistake 1: Forgetting the WHERE clause entirely

Wrong:

SELECT e.employee_id, e.name, s.salary
FROM Employees e, Salaries s;

This silently returns the full Cartesian product (3 × 2 = 6 rows here), pairing every employee with every salary record, regardless of whether employee_id actually matches.

Correct: Always include the WHERE condition when using implicit join syntax, this is the single most important habit for this syntax style.

Mistake 2: Mixing implicit and explicit join syntax in the same query without realizing it

Why it happens: It's easy to accidentally combine both styles when editing a query quickly, since both are technically valid SQL.

Why it matters: While technically legal in most databases, mixing FROM a, b JOIN c ON ... styles in one query can become confusing to read and maintain, and increases the chance of forgetting a needed WHERE condition for the comma-joined tables specifically.

Correct: When asked to use implicit join syntax, stick to it consistently throughout the query, using commas and WHERE conditions exclusively.

Mistake 3: Assuming implicit joins can express LEFT/RIGHT/FULL outer join behavior

Why it happens: Implicit joins can replicate INNER JOIN behavior cleanly, which might lead to assuming they can replicate every join type.

Why it matters: Implicit join syntax has no clean, portable way to express outer join behavior (preserving unmatched rows), some databases offer non-standard extensions for this, but it's not reliable or widely supported, this is one of the reasons modern explicit JOIN syntax (with LEFT/RIGHT/FULL keywords) is strongly preferred for anything beyond a simple INNER JOIN.

Interview Follow-up Questions

• "Why is implicit join syntax generally discouraged in modern SQL codebases?"
• "How would you express a LEFT JOIN's behavior using implicit join syntax, if at all?"
• "What happens internally if you use implicit join syntax across three or more tables, and forget one of the WHERE conditions?"

Similar Problems

• Problem 3 (Employees With Confirmed Salary Records), the exact same logical problem, solved with explicit INNER JOIN syntax instead
• Any legacy SQL codebase review task requiring you to read and modernize old comma-join queries into explicit JOIN syntax

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement explicitly restricts you to implicit join syntax, or you're reading/maintaining a legacy codebase using this style → comma-separated FROM, matching condition in WHERE
• Implicit join syntax is being used → always double, then triple check the WHERE clause is present and correct, since there's no visual "ON" keyword to remind you

Problem 7: Employees Earning More Than Their Manager

Problem Statement

A company wants to identify employees whose salary is higher than their direct manager's salary, to review the compensation structure for inconsistencies.

Business context In a healthy compensation structure, managers are usually expected to earn more than their direct reports. When a report earns more than their manager, that's often worth a compensation review or at least a closer look.

Goal Return the names of employees whose salary is strictly greater than their direct manager's salary.

Input A single Employees table containing both employee and manager information (via a self-referencing manager_id column).

Output name, for every employee earning more than their manager.

Table Structure

Table: Employees

Column Meaning

• employee_id — primary key, unique identifier for each employee
• name — employee's name
• salary — employee's salary
• manager_id — refers to another employee's employee_id (their direct manager); the top-level manager has manager_id = NULL

Important Notes

• This table stores both employees and managers in the exact same structure, since a manager is themselves just another employee row, this is precisely what makes a self join necessary.
• manager_id = NULL (the top of the hierarchy) will never match anything in a self join, and correctly so, since the top manager has no manager to compare their salary against.
• The comparison is strict (>), an employee earning exactly the same as their manager doesn't qualify.

Example

Input: Employees

Expected Output

CREATE TABLE Employees (employee_id INTEGER, name TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO Employees VALUES
  (1, 'Ritesh', 90000, NULL),
  (2, 'Ananya', 95000, 1),
  (3, 'Mohit', 85000, 1),
  (4, 'Kunal', 97000, 2);

Explanation: Ananya (95000) > Ritesh (90000), her manager, so she's included. Mohit (85000) < Ritesh (90000), so he's excluded. Kunal (97000) > Ananya (95000), his manager, so he's included.

Intuition

Think of this exactly like the earlier employee-manager self join from the concept notes, except now, instead of just listing "employee next to manager," you're specifically comparing their two salary values and keeping only the rows where the report out-earns their boss.

Mental Model

Picture laying two copies of the exact same employee roster side by side, one copy labeled "the report," one copy labeled "the manager." You draw a line connecting each report to their manager (matching manager_id to employee_id), and then, for every connected pair, you compare the two salary numbers written on each copy. Only the pairs where the report's number is bigger get circled and kept.

Thought Process

1. Do I need two "copies" of the same table? Yes, one representing the employee, one representing the manager, exactly the self-join pattern from the concept notes.
2. What's the matching condition? employee.manager_id = manager.employee_id.
3. Where does the salary comparison go, ON or WHERE? Since this problem has no outer-join requirement (unmatched rows, like the top manager with no manager, should simply be excluded, not preserved with NULLs), both placing the salary comparison in ON or in WHERE would produce the same final result here, since this is effectively an INNER JOIN with two conditions. The source solution places it in ON, which is a perfectly valid and common style for combining multiple join conditions together.

Approach

Step 1: Self join Employees to itself Using two aliases, one for the employee (e), one for the manager (m).

Step 2: Match manager_id to employee_id e.manager_id = m.employee_id.

Step 3: Add the salary comparison as an additional join condition e.salary > m.salary, combined with AND inside the same ON clause.

Step 4: Select just the employee's name

SQL Query

SELECT
    e.name
FROM Employees e
JOIN Employees m
-- Match each employee to their direct manager
ON e.manager_id = m.employee_id
-- Additionally, only keep pairs where the employee out-earns their manager
AND e.salary > m.salary;

Query Explanation

• FROM Employees e — treats the table as "the employee" side.
• JOIN Employees m — treats the exact same table as "the manager" side, using a second alias.
• ON e.manager_id = m.employee_id — connects each employee row to their specific manager's row.
• AND e.salary > m.salary — an additional condition combined directly into the ON clause, only pairs satisfying both the manager match and the salary comparison survive into the result.
• Since this is a plain (INNER) JOIN, Ritesh (the top-level manager with manager_id = NULL) is automatically excluded, there's no manager row for NULL to match against.

Dry Run

Employees

↓

Match employees (e) to managers (m) via manager_id = employee_id

(Ritesh himself has manager_id = NULL, so he never appears as "e" in this matched list at all.)

↓

Apply the salary comparison: e.salary > m.salary

↓

Final output

SQL Execution Flow

FROM Employees e
   ↓
JOIN Employees m ON e.manager_id = m.employee_id AND e.salary > m.salary
   ↓
For each employee, find their manager row, keep only if salary comparison also passes
   ↓
SELECT e.name
   ↓
Return final output

Time Complexity

Time Complexity: O(n), faster with an index on employee_id (which is already the primary key here) Each employee needs a single lookup to find their manager (via manager_id matching employee_id, the primary key), making this efficient even on large employee tables.

Space Complexity: O(k), where k is the number of employees who out-earn their manager Only the final matched, filtered result needs to be held.

Why: Since manager_id references a primary key (employee_id), this lookup is essentially a fast, indexed match for every row, the additional salary comparison adds negligible extra cost once the manager row has already been found.

Common Mistakes to Avoid

Mistake 1: Reversing the join condition (matching employee_id to manager_id backwards)

Wrong:

SELECT e.name
FROM Employees e
JOIN Employees m
ON e.employee_id = m.manager_id
AND e.salary > m.salary;

This backwards condition would match each employee to their direct reports, not their manager, completely flipping the intended relationship and producing a meaningless comparison.

Correct: ON e.manager_id = m.employee_id, connecting the employee's manager_id to the manager's own employee_id.

Mistake 2: Using a plain JOIN when the top-level manager should be included with a NULL comparison

Why it happens: It might seem like every employee, including the top manager, should somehow appear in the output.

Why it matters: The problem specifically asks for employees who earn more than their manager, since Ritesh has no manager at all (manager_id = NULL), there's no salary to compare him against, so it's correct, not a bug, that he never appears in this self join's output at all.

Mistake 3: Using WHERE instead of combining the salary check into ON

This isn't strictly a mistake here, but it's worth understanding both are valid:

SELECT e.name
FROM Employees e
JOIN Employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Since this is an INNER JOIN (not an outer join), placing the salary condition in ON versus WHERE produces the same result here, this differs from the RIGHT JOIN examples in the concept notes, where ON vs. WHERE genuinely changed behavior specifically because those were outer joins. With a plain INNER JOIN, both placements are equivalent, though many style guides prefer keeping pure matching logic in ON and additional filtering in WHERE, purely for readability.

Interview Follow-up Questions

• "How would you find employees who earn the same as their manager, instead of strictly more?"
• "How would you find the single employee with the largest salary gap above their manager?"
• "How would you extend this to a multi-level hierarchy, like finding employees earning more than their manager's manager?"
• "Would placing the salary condition in WHERE instead of ON change this result? Why or why not?"

Similar Problems

• The SELF JOIN section in the concept notes (employee + manager name, and count of direct reports per manager), the same foundational self-join pattern, applied here with an added comparison
• Any hierarchical comparison problem, like finding a product cheaper than the product it replaced, or a student who scored higher than their assigned tutor

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• A table stores a self-referencing relationship (like manager_id pointing back to employee_id in the same table) → self join, using two aliases
• Need to compare two related rows' values against each other (not just check that they exist) → add the comparison directly into the ON clause (or WHERE, if using INNER JOIN specifically), combined with AND alongside the matching condition

Problem 8: Students Enrolled in Courses

Problem Statement

An online education platform wants to list students along with the courses they're enrolled in, using NATURAL JOIN specifically, since both tables share a common column name.

Business context This problem is specifically about recognizing when NATURAL JOIN's automatic matching behavior is appropriate, both tables here genuinely share exactly one same-named column (student_id), with no ambiguity.

Goal Return student name and course, using NATURAL JOIN only.

Input Students and Enrollments tables, both sharing a student_id column.

Output name, course, for every matching student-enrollment pair.

Table Structure

Table: Students

Table: Enrollments

Column Meaning

• student_id — shared column name between both tables, primary key in Students, a reference (not necessarily unique) in Enrollments
• name — student's name
• course — the course name a student is enrolled in

Important Notes

• Both tables share exactly one same-named column (student_id), this is exactly the scenario where NATURAL JOIN is safe and predictable, as opposed to the general risk highlighted in the concept notes.
• A student can be enrolled in multiple courses, producing multiple output rows for that student.
• A student with zero enrollments (like Neha in the example) is automatically excluded, since NATURAL JOIN behaves like an INNER JOIN by default, unmatched rows are dropped.

Example

Input: Students

Input: Enrollments

Expected Output

CREATE TABLE Students (student_id INTEGER, name TEXT);
INSERT INTO Students VALUES
  (1, 'Aarav'),
  (2, 'Neha'),
  (3, 'Rohan');
CREATE TABLE Enrollments (student_id INTEGER, course TEXT);
INSERT INTO Enrollments VALUES
  (1, 'Math'),
  (1, 'Physics'),
  (3, 'Chemistry');

Explanation: Neha has no enrollment record at all, so she's excluded, only matching student_id values appear in the output.

Intuition

Think of NATURAL JOIN here as SQL noticing that both tables have a column literally named student_id, and assuming (correctly, in this specific case) that this is exactly the field meant to connect them, no need to spell it out explicitly.

Mental Model

Picture handing two spreadsheets to an assistant and saying "just match these up using whatever column name they have in common," rather than pointing at the specific column yourself. As long as there's genuinely only one shared column name, and it really is the right one to match on, the assistant does exactly the right thing without needing further instructions.

Thought Process

1. Do both tables share exactly one same-named column? Yes, student_id, in both Students and Enrollments.
2. Is that shared column actually the correct one to match on? Yes, this is precisely the relationship the two tables are meant to represent.
3. Given the explicit requirement to use NATURAL JOIN, is there any ON clause needed? No, NATURAL JOIN handles the matching automatically.

Approach

Step 1: Use NATURAL JOIN between Students and Enrollments No ON clause is written or needed.

Step 2: Select name and course Matching the exact output columns requested.

SQL Query

SELECT
    name,
    course
FROM Students
-- NATURAL JOIN automatically matches on student_id, the one shared column name
NATURAL JOIN Enrollments;

Query Explanation

• NATURAL JOIN Enrollments — automatically detects that student_id is the only column name shared between Students and Enrollments, and uses it as the matching condition, with no ON clause required at all.
• Since NATURAL JOIN behaves like an INNER JOIN by default, students with zero matching enrollments (like Neha) are excluded entirely from the output.

Dry Run

Students

Enrollments

↓

Match automatically on the shared student_id column

(student_id 2, Neha, has no matching row in Enrollments, so she's excluded entirely.)

↓

Final output (only name and course selected)

SQL Execution Flow

FROM Students
   ↓
NATURAL JOIN Enrollments (automatically matches on shared column name: student_id)
   ↓
Keep only rows where student_id matches in both tables
   ↓
SELECT name, course
   ↓
Return final output

Time Complexity

Time Complexity: O(n + m), faster with an index on Enrollments.student_id Behaves identically to an INNER JOIN in terms of cost, since that's effectively what NATURAL JOIN executes as, once it has determined the shared column(s).

Space Complexity: O(k), where k is the number of matched enrollment rows Only the matched result needs to be held.

Why: NATURAL JOIN's only real difference from a regular JOIN is how the matching column is determined (automatically, by name, versus explicitly written in ON), the actual join execution cost is otherwise identical.

Common Mistakes to Avoid

Mistake 1: Assuming NATURAL JOIN is always safe just because it worked here

Why it happens: Seeing NATURAL JOIN work correctly in this specific, controlled example might create false confidence about using it elsewhere.

Why it matters: As covered in the concept notes, if either table later gains another same-named column (for instance, both tables independently adding a status column for unrelated purposes), NATURAL JOIN would silently start matching on both columns at once, without any warning, potentially breaking this exact query without a single line of it being changed directly.

Correct: Even when NATURAL JOIN happens to work correctly today, in most real, evolving codebases, prefer an explicit JOIN ... ON student_id instead, to guard against this exact risk going forward. Use NATURAL JOIN specifically when a requirement calls for it (like this problem does), or for quick, temporary, one-off queries.

Mistake 2: Selecting student_id explicitly with a table prefix

Wrong (can sometimes cause ambiguity errors, depending on how the query is structured, and defeats part of NATURAL JOIN's point):

SELECT s.name, e.course
FROM Students s
NATURAL JOIN Enrollments e;

While this specific version might work in some databases, mixing explicit table aliases with NATURAL JOIN's implicit matching can be confusing, and doesn't add clarity, since NATURAL JOIN's whole premise is not needing to specify the matching column.

Correct: Keep it simple and consistent with the NATURAL JOIN style, as shown in the main solution, selecting unambiguous, non-shared columns directly by name.

Mistake 3: Expecting every student to appear, since NATURAL JOIN "sounds" like it should be permissive

Why it happens: The word "NATURAL" doesn't inherently signal INNER JOIN behavior to someone new to the term.

Why it matters: NATURAL JOIN defaults to INNER JOIN behavior specifically, unmatched rows (like Neha) are dropped entirely, if every student needed to appear regardless of enrollment, a NATURAL LEFT JOIN would be needed instead (which some databases support), not the plain NATURAL JOIN used here.

Interview Follow-up Questions

• "What would happen to this exact query if both tables later added an unrelated column with the same name, like created_at?"
• "How would you rewrite this using an explicit JOIN, to make the matching column clear to future readers?"
• "Does NATURAL JOIN support LEFT or RIGHT outer join behavior? How would you use it if so?"
• "Why might a team's style guide explicitly forbid NATURAL JOIN in production code, despite it working correctly here?"

Similar Problems

• The NATURAL JOIN section in the concept notes (customers + customer_loyalty), the same automatic same-column matching pattern
• Any problem explicitly requiring a specific join syntax style (implicit, natural, or self join) as a constraint, rather than leaving the syntax choice open

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement explicitly asks for NATURAL JOIN specifically, and you've confirmed the two tables share exactly one correctly-matching column name → use NATURAL JOIN with no ON clause
• Considering NATURAL JOIN for new, evolving production code → prefer an explicit JOIN ... ON instead, reserving NATURAL JOIN for situations where it's specifically required or for quick, temporary exploration