INNER JOIN with multiple conditions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

INNER JOIN with two conditions

Given two tables Employees and Departments, what is the result of this query?

SQL

SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID AND e.Location = d.Location;

A [{"EmployeeID": 2, "Name": "Bob", "DepartmentName": "Sales"}, {"EmployeeID": 3, "Name": "Charlie", "DepartmentName": "HR"}]

B [{"EmployeeID": 1, "Name": "Alice", "DepartmentName": "Sales"}, {"EmployeeID": 2, "Name": "Bob", "DepartmentName": "Sales"}]

C [{"EmployeeID": 1, "Name": "Alice", "DepartmentName": "Sales"}, {"EmployeeID": 3, "Name": "Charlie", "DepartmentName": "HR"}]

D []

💡 Hint

Remember that both conditions in the ON clause must be true for a row to join.

The INNER JOIN matches rows where DepartmentID and Location are the same in both tables. Only Alice and Charlie meet both conditions.

📝 Syntax

intermediate

Identify the syntax error in INNER JOIN with multiple conditions

Which option contains a syntax error in the INNER JOIN with multiple conditions?

SQL

SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID AND e.Location = d.Location;

A SELECT * FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID AND e.Location = d.Location;

B SELECT * FROM Employees e INNER JOIN Departments d ON (e.DepartmentID = d.DepartmentID AND e.Location = d.Location);

C ;noitacoL.d = noitacoL.e DNA DItnemtrapeD.d = DItnemtrapeD.e NO d stnemtrapeD NIOJ RENNI e seeyolpmE MORF * TCELES

D SELECT * FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID, e.Location = d.Location;

💡 Hint

Check the use of AND and commas in the ON clause.

Option D uses a comma instead of AND in the ON clause, which is invalid syntax for JOIN conditions.

❓ optimization

advanced

Optimizing INNER JOIN with multiple conditions

Which option is the most efficient way to write an INNER JOIN with multiple conditions for large tables?

A SELECT * FROM Employees e, Departments d WHERE e.DepartmentID = d.DepartmentID AND e.Location = d.Location;

B SELECT * FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID AND e.Location = d.Location;

C SELECT * FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID OR e.Location = d.Location;

D SELECT * FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID AND e.Location = d.Location WHERE d.DepartmentID IS NOT NULL;

💡 Hint

INNER JOIN with ON clause is generally more efficient than WHERE with comma joins.

Option B uses INNER JOIN with multiple conditions in ON clause, which is optimized by SQL engines. Option B uses implicit join syntax which is less efficient. Option B uses OR which is less selective. Option B uses LEFT JOIN with filter which is less efficient.

🔧 Debug

advanced

Why does this INNER JOIN return no rows?

Given these tables, why does this query return no rows? SELECT e.EmployeeID, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID AND e.Location = d.Location;

A Because no rows have matching DepartmentID and Location in both tables.

B Because the Employees table is empty.

C Because the ON clause uses AND instead of OR.

D Because INNER JOIN requires a WHERE clause to filter rows.

💡 Hint

Check if the join conditions match any rows in both tables.

INNER JOIN returns rows only when all conditions in ON clause are true. If no rows match both DepartmentID and Location, result is empty.

🧠 Conceptual

expert

Understanding INNER JOIN with multiple conditions logic

Which statement best describes how INNER JOIN with multiple conditions works?

A It returns rows where all conditions in the ON clause are true for the joined tables.

B It returns rows where at least one condition in the ON clause is true.

C It returns all rows from the left table and matching rows from the right table.

D It returns all rows from both tables regardless of conditions.

💡 Hint

Think about how AND works in the ON clause.

INNER JOIN with multiple conditions uses AND logic, so only rows matching all conditions are returned.

Practice

1. What does an INNER JOIN with multiple conditions do in SQL?

easy

A. It returns rows where all join conditions are true.

B. It returns rows where any one of the join conditions is true.

C. It returns all rows from both tables regardless of conditions.

D. It returns rows only from the first table.

Solution

1. Step 1: Understand INNER JOIN basics

   INNER JOIN returns rows that have matching values in both tables based on join conditions.

2. Step 2: Analyze multiple conditions with AND

   When multiple conditions are combined with AND, all must be true for a row to be included.

3. Final Answer:

   It returns rows where all join conditions are true. -> Option A

4. Quick Check:

   INNER JOIN + AND = all conditions true [OK]

Hint: All join conditions must be true with AND in INNER JOIN [OK]

Common Mistakes:

• Thinking any one condition is enough
• Confusing INNER JOIN with OUTER JOIN
• Ignoring the AND operator effect

2. Which of the following is the correct syntax for an INNER JOIN with two conditions on tables Orders and Customers?

easy

A. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status;

B. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID OR Orders.Status = Customers.Status;

C. SELECT * FROM Orders INNER JOIN Customers WHERE Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status;

D. SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.ID, Orders.Status = Customers.Status;

Solution

1. Step 1: Identify correct JOIN syntax

   INNER JOIN requires ON keyword followed by conditions combined with AND for multiple matches.

2. Step 2: Check each option's syntax

   SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status; correctly uses ON with two conditions joined by AND. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID OR Orders.Status = Customers.Status; uses OR which is incorrect for multiple conditions requiring all true. SELECT * FROM Orders INNER JOIN Customers WHERE Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status; wrongly uses WHERE instead of ON. SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.ID, Orders.Status = Customers.Status; has invalid syntax with comma.

3. Final Answer:

   SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status; -> Option A

4. Quick Check:

   INNER JOIN + ON + AND = correct syntax [OK]

Hint: Use ON with AND for multiple join conditions [OK]

Common Mistakes:

• Using OR instead of AND in ON clause
• Replacing ON with WHERE for join conditions
• Separating conditions with commas

3. Given tables Employees and Departments with columns Employees.DeptID, Departments.ID, and Departments.Location, what will this query return?

SELECT Employees.Name, Departments.Name FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.ID AND Departments.Location = 'NY';

medium

A. Syntax error due to multiple conditions in JOIN.

B. All employees with their department names regardless of location.

C. Departments located in NY with all employees listed multiple times.

D. Employees working only in departments located in NY with their department names.

Solution

1. Step 1: Understand the JOIN conditions

   The query joins Employees and Departments where DeptID matches ID and the department location is 'NY'.

2. Step 2: Analyze the result rows

   Only employees whose department is in NY will appear with their department names. Others are excluded.

3. Final Answer:

   Employees working only in departments located in NY with their department names. -> Option D

4. Quick Check:

   INNER JOIN + multiple conditions filters rows precisely [OK]

Hint: Multiple conditions filter rows strictly in INNER JOIN [OK]

Common Mistakes:

• Assuming all employees appear regardless of location
• Thinking multiple conditions cause syntax error
• Confusing join condition with WHERE clause

4. Identify the error in this SQL query:

SELECT * FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.ID, Products.Category = Suppliers.Category;

medium

A. Using ON instead of WHERE for join conditions.

B. Missing WHERE clause for the second condition.

C. Using comma instead of AND to combine join conditions.

D. INNER JOIN cannot have multiple conditions.

Solution

1. Step 1: Review JOIN condition syntax

   Multiple join conditions must be combined with AND inside the ON clause.

2. Step 2: Identify the error in the query

   The query incorrectly uses a comma to separate conditions, which is invalid syntax.

3. Final Answer:

   Using comma instead of AND to combine join conditions. -> Option C

4. Quick Check:

   Multiple conditions joined by AND, not comma [OK]

Hint: Use AND, not comma, to join multiple ON conditions [OK]

Common Mistakes:

• Separating conditions with commas
• Confusing WHERE and ON clauses
• Believing INNER JOIN allows only one condition

5. You have two tables: Orders(OrderID, CustomerID, Status) and Customers(CustomerID, Country, Status). You want to find orders where the customer is from 'USA' and both order and customer have the same status. Which query correctly uses INNER JOIN with multiple conditions?

hard

A. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.Country = 'USA' AND Orders.Status = Customers.Status;

B. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Customers.Country = 'USA' AND Orders.Status = Customers.Status;

C. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID OR Customers.Country = 'USA' AND Orders.Status = Customers.Status;

D. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID, Customers.Country = 'USA', Orders.Status = Customers.Status;

Solution

1. Step 1: Understand the filtering requirements

   We need to join on CustomerID and filter where customer is from USA and order status matches customer status.

2. Step 2: Check correct use of multiple conditions in INNER JOIN

   SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Customers.Country = 'USA' AND Orders.Status = Customers.Status; correctly uses AND to combine all conditions inside ON clause. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID OR Customers.Country = 'USA' AND Orders.Status = Customers.Status; wrongly uses OR which changes logic. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.Country = 'USA' AND Orders.Status = Customers.Status; moves conditions to WHERE, which is valid but not the question's focus on multiple ON conditions. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID, Customers.Country = 'USA', Orders.Status = Customers.Status; uses commas incorrectly.

3. Final Answer:

   SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Customers.Country = 'USA' AND Orders.Status = Customers.Status; -> Option B

4. Quick Check:

   Multiple AND conditions inside ON for precise join [OK]

Hint: Combine all join filters with AND inside ON clause [OK]

Common Mistakes:

• Using OR instead of AND in join conditions
• Placing join filters in WHERE instead of ON
• Separating conditions with commas