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.