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.