INNER JOIN with ON condition in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

INNER JOIN with simple ON condition

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

SQL

SELECT Employees.Name, Departments.DepartmentName FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;

A [{"Name": "Alice", "DepartmentName": "HR"}, {"Name": "Bob", "DepartmentName": "Finance"}]

B []

C [{"Name": "Alice"}, {"Name": "Bob"}]

D [{"Name": "Alice", "DepartmentName": "HR"}, {"Name": "Bob", "DepartmentName": "IT"}]

💡 Hint

Think about matching DepartmentID in Employees with ID in Departments.

The INNER JOIN returns rows where Employees.DepartmentID matches Departments.ID. Alice is in HR and Bob is in IT, so only those pairs appear.

📝 Syntax

intermediate

Identify the syntax error in INNER JOIN

Which option contains a syntax error in the INNER JOIN statement?

SQL

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

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

B SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID WHERE Orders.Amount > 100;

C SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID;

D SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID ORDER BY Customers.Name;

💡 Hint

Check for missing keywords or clauses in the JOIN syntax.

Option A is missing the ON keyword, which is required to specify the join condition.

❓ optimization

advanced

Optimizing INNER JOIN with multiple conditions

Which query is the most efficient way to join Sales and Products tables on both ProductID and Region?

A SELECT * FROM Sales INNER JOIN Products ON Sales.ProductID = Products.ID WHERE Sales.Region = Products.Region;

B SELECT * FROM Sales INNER JOIN Products ON Sales.ProductID = Products.ID AND Sales.Region = Products.Region;

C SELECT * FROM Sales, Products WHERE Sales.ProductID = Products.ID AND Sales.Region = Products.Region;

D SELECT * FROM Sales INNER JOIN Products USING (ProductID, Region);

💡 Hint

Consider how join conditions affect query execution and filtering.

Option B uses INNER JOIN with both conditions in the ON clause, which is efficient and clear. Option B applies one condition in WHERE, which can cause less efficient filtering. Option B uses implicit join syntax which is less readable and can be less optimized. Option B is invalid because USING requires columns with the same name in both tables, and Region may not be named identically.

🧠 Conceptual

advanced

Understanding INNER JOIN behavior with NULL values

What happens when you INNER JOIN two tables on a column that contains NULL values in one table?

A Rows with NULL in the join column are excluded from the result.

B Rows with NULL in the join column are included with NULLs in the joined columns.

C The query returns an error due to NULL values in join columns.

D Rows with NULL in the join column are matched with all rows in the other table.

💡 Hint

Think about how NULL compares in SQL join conditions.

INNER JOIN matches rows where join columns are equal. NULL is not equal to anything, so rows with NULL in join columns do not match and are excluded.

🔧 Debug

expert

Debugging unexpected INNER JOIN results

You run this query but get fewer rows than expected: SELECT Orders.OrderID, Customers.Name FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID WHERE Customers.Country = 'USA'; What is the most likely reason?

A INNER JOIN does not work with WHERE clauses.

B The WHERE clause filters out all rows because no Customers are from USA.

C Some Orders have CustomerID values that do not exist in Customers table.

D The ON condition is incorrect; it should use Orders.ID = Customers.CustomerID.

💡 Hint

Consider how INNER JOIN and WHERE filtering interact.

If some Orders have CustomerID values not present in Customers, those Orders are excluded by INNER JOIN, reducing rows. The WHERE clause filters after the join, but the main cause is missing matching CustomerID.