How the join engine matches rows in SQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of INNER JOIN with multiple matching rows

Consider two tables, Employees and Departments:

Employees:
id | name | dept_id
1 | Alice | 10
2 | Bob | 20
3 | Carol | 10

Departments:
dept_id | dept_name
10 | Sales
20 | Marketing

What is the output of this query?

SELECT Employees.name, Departments.dept_name
FROM Employees
INNER JOIN Departments ON Employees.dept_id = Departments.dept_id;

SQL

SELECT Employees.name, Departments.dept_name
FROM Employees
INNER JOIN Departments ON Employees.dept_id = Departments.dept_id;

A [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Marketing"}]

B [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Marketing"}, {"name": "Carol", "dept_name": "Sales"}]

C [{"name": "Alice", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "Sales"}]

D [{"name": "Alice", "dept_name": "Sales"}]

💡 Hint

INNER JOIN returns rows where the join condition matches in both tables.

Each employee with a matching department id is joined with that department. Alice and Carol both have dept_id 10 (Sales), Bob has dept_id 20 (Marketing). So all three employees appear with their departments.

🧠 Conceptual

intermediate

How does a LEFT JOIN match rows?

Which statement best describes how a LEFT JOIN matches rows between two tables?

A It returns all rows from the left table and matching rows from the right table; unmatched right rows are filled with NULLs.

B It returns only rows where both tables have matching values in the join condition.

C It returns all rows from the right table and matching rows from the left table; unmatched left rows are excluded.

D It returns all rows from the left table and matching rows from the right table; unmatched right rows are excluded.

💡 Hint

Think about which table's rows always appear in the result.

LEFT JOIN returns all rows from the left table. If there is no matching row in the right table, the right table columns are filled with NULL.

📝 Syntax

advanced

Identify the syntax error in JOIN condition

Which option contains a syntax error in the JOIN condition?

SELECT * FROM A JOIN B ON A.id = B.id;

A SELECT * FROM A JOIN B ON A.id = B.id;

B SELECT * FROM A JOIN B ON A.id = B.id

C SELECT * FROM A JOIN B ON A.id == B.id;

D SELECT * FROM A JOIN B ON A.id = B.id WHERE;

💡 Hint

Check the operator used in the ON clause.

SQL uses a single equals sign (=) for comparison, not double equals (==). Option C uses '==' which is invalid syntax.

🔧 Debug

advanced

Why does this JOIN return fewer rows than expected?

Given tables Orders and Customers, this query returns fewer rows than expected:

SELECT Orders.id, Customers.name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = Customers.id
WHERE Customers.status = 'active';

What is the most likely reason?

A The WHERE clause filters out orders with inactive customers after the join, reducing rows.

B Some Orders have customer_id values that do not match any Customers.id, so those orders are excluded.

C The INNER JOIN includes all orders regardless of customer status, so the WHERE clause has no effect.

D The query syntax is invalid and causes an error.

💡 Hint

Consider how WHERE filters rows after the join.

The INNER JOIN matches orders with customers, but the WHERE clause filters only customers with status 'active'. Orders linked to inactive customers are removed after the join, reducing rows.

❓ optimization

expert

Optimizing JOIN performance with indexes

You have two large tables, Products and Sales, joined on Products.product_id = Sales.product_id. Which indexing strategy will most improve the join performance?

A No indexes are needed; the database automatically optimizes joins.

B Create an index on Sales.product_id only.

C Create an index on Products.product_id only.

D Create indexes on both Products.product_id and Sales.product_id.

💡 Hint

Think about how indexes help the database find matching rows quickly.

Indexes on both join columns allow the database to quickly locate matching rows in both tables, greatly improving join performance.

Practice

1. What does the SQL JOIN engine use to match rows from two tables?

easy

A. The ON condition specifying matching columns

B. The order of rows in each table

C. The number of columns in each table

D. The table names only

Solution

1. Step 1: Understand the role of the ON condition

   The ON condition defines which columns from each table must have matching values for rows to join.

2. Step 2: Recognize what the join engine uses

   The join engine uses this condition to pair rows correctly, ignoring row order or table names alone.

3. Final Answer:

   The ON condition specifying matching columns -> Option A

4. Quick Check:

   Join engine matches rows using ON condition [OK]

Hint: Remember: JOIN matches rows using ON condition columns [OK]

Common Mistakes:

• Thinking row order affects join matching
• Assuming table names determine matches
• Confusing number of columns with matching criteria

2. Which of the following is the correct syntax to join two tables Employees and Departments on the column DeptID?

easy

A. SELECT * FROM Employees JOIN Departments WHERE Employees.DeptID = Departments.DeptID;

B. SELECT * FROM Employees JOIN Departments USING Employees.DeptID;

C. SELECT * FROM Employees, Departments ON Employees.DeptID = Departments.DeptID;

D. SELECT * FROM Employees JOIN Departments ON Employees.DeptID = Departments.DeptID;

Solution

1. Step 1: Identify correct JOIN syntax

   The correct JOIN syntax uses JOIN ... ON ... to specify the matching condition.

2. Step 2: Check each option

   SELECT * FROM Employees JOIN Departments ON Employees.DeptID = Departments.DeptID; uses JOIN ... ON correctly. SELECT * FROM Employees JOIN Departments WHERE Employees.DeptID = Departments.DeptID; wrongly uses WHERE instead of ON. SELECT * FROM Employees, Departments ON Employees.DeptID = Departments.DeptID; misuses ON with comma join. SELECT * FROM Employees JOIN Departments USING Employees.DeptID; misuses USING syntax with table prefix.

3. Final Answer:

   SELECT * FROM Employees JOIN Departments ON Employees.DeptID = Departments.DeptID; -> Option D

4. Quick Check:

   JOIN syntax requires ON condition [OK]

Hint: JOIN needs ON, not WHERE or comma with ON [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Mixing comma joins with ON clause
• Incorrect USING syntax with table prefixes

3. Given tables Orders and Customers with columns CustomerID, what will be the result of this query?

SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Assuming Orders has 3 rows with CustomerIDs 1, 2, 4 and Customers has 2 rows with CustomerIDs 1, 2.

medium

A. 3 rows with OrderIDs 1, 2, 4 and matching customer names for IDs 1 and 2 only

B. 2 rows with OrderIDs 1 and 2 only, matching customer names

C. All 3 rows with customer names, including NULL for CustomerID 4

D. No rows because CustomerID 4 does not exist in Customers

Solution

1. Step 1: Understand INNER JOIN behavior

   INNER JOIN returns only rows where the join condition matches in both tables.

2. Step 2: Apply to given data

   Orders have CustomerIDs 1, 2, 4; Customers have 1, 2. Only CustomerIDs 1 and 2 match, so only those rows appear.

3. Final Answer:

   2 rows with OrderIDs 1 and 2 only, matching customer names -> Option B

4. Quick Check:

   INNER JOIN returns only matching rows [OK]

Hint: INNER JOIN shows only matching rows from both tables [OK]

Common Mistakes:

• Expecting unmatched rows to appear with NULLs
• Confusing INNER JOIN with LEFT JOIN
• Assuming all rows from first table appear

4. You wrote this query but it returns fewer rows than expected:

SELECT * FROM Products JOIN Categories ON Products.CategoryID = Categories.ID;

What is the most likely cause?

medium

A. There are Products with CategoryID values not present in Categories

B. The query needs a WHERE clause to filter rows

C. The JOIN keyword is missing

D. The join condition column names are swapped

Solution

1. Step 1: Analyze INNER JOIN behavior

   INNER JOIN returns only rows where the join condition matches in both tables.

2. Step 2: Consider missing matches

   If some Products have CategoryID values not in Categories, those Products are excluded, reducing rows.

3. Final Answer:

   There are Products with CategoryID values not present in Categories -> Option A

4. Quick Check:

   Missing matches cause fewer rows in INNER JOIN [OK]

Hint: INNER JOIN excludes rows without matching keys [OK]

Common Mistakes:

• Thinking swapped column names cause fewer rows
• Assuming JOIN keyword missing causes fewer rows
• Believing WHERE clause is needed to fix join

5. You want to list all employees and their department names, but some employees have no department assigned. Which join type should you use to ensure all employees appear, even if their department is missing?

hard

A. RIGHT JOIN

B. INNER JOIN

C. LEFT JOIN

D. FULL JOIN

Solution

1. Step 1: Understand join types and their row inclusion

   INNER JOIN includes only matching rows; LEFT JOIN includes all rows from the left table and matches from right; RIGHT JOIN is opposite; FULL JOIN includes all rows from both.

2. Step 2: Apply to employees and departments

   To list all employees even if no department exists, use LEFT JOIN from Employees (left) to Departments (right).

3. Final Answer:

   LEFT JOIN -> Option C

4. Quick Check:

   LEFT JOIN keeps all left table rows [OK]

Hint: Use LEFT JOIN to keep all left table rows [OK]

Common Mistakes:

• Using INNER JOIN and missing employees without departments
• Confusing RIGHT JOIN with LEFT JOIN
• Assuming FULL JOIN is needed when LEFT JOIN suffices