INNER JOIN in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INNER JOIN query?

Consider two tables: Employees and Departments.

Employees has columns: EmpID, Name, DeptID.

Departments has columns: DeptID, DeptName.

What rows will this query return?

MySQL

SELECT Employees.Name, Departments.DeptName FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

A [{"Name": "Alice", "DeptName": "HR"}, {"Name": "Bob", "DeptName": "IT"}]

B [{"Name": "Alice", "DeptName": "HR"}, {"Name": "Bob", "DeptName": "IT"}, {"Name": "Charlie", "DeptName": null}]

C [{"Name": "Alice", "DeptName": "HR"}]

D []

💡 Hint

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

Only employees with a matching DeptID in Departments appear. Charlie has no matching DeptID, so is excluded.

🧠 Conceptual

intermediate

What does INNER JOIN do in SQL?

Choose the best description of what an INNER JOIN does.

A Returns all rows from the left table, and matching rows from the right table or NULL if no match.

B Returns only rows where there is a match in both tables based on the join condition.

C Returns all rows from both tables, combining all possible pairs.

D Returns all rows from the right table, and matching rows from the left table or NULL if no match.

💡 Hint

Think about which rows appear when both tables have matching values.

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

📝 Syntax

advanced

Which query correctly uses INNER JOIN syntax?

Identify the query that will run without syntax errors and perform an INNER JOIN between Orders and Customers on CustomerID.

A SELECT * FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID;

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

C SELECT * FROM Orders INNER JOIN Customers USING (CustomerID);

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

💡 Hint

Check the correct JOIN syntax and operator for equality in SQL.

Option D uses correct INNER JOIN syntax with ON and single equals sign for comparison.

Option D is missing ON clause.

Option D uses USING without parentheses, which is valid in some SQL dialects but not standard MySQL.

Option D uses double equals which is invalid in SQL.

❓ optimization

advanced

How to optimize INNER JOIN performance on large tables?

You have two large tables joined by DeptID. Which option best improves INNER JOIN query speed?

A Add an index on the <code>DeptID</code> column in both tables.

B Use SELECT * to get all columns instead of specifying needed columns.

C Use a subquery instead of INNER JOIN.

D Remove the ON clause and join without conditions.

💡 Hint

Indexes help databases find matching rows faster.

Indexing the join columns speeds up matching rows in INNER JOIN.

Other options either slow down or produce incorrect results.

🔧 Debug

expert

Why does this INNER JOIN query return zero rows?

Given tables Students and Enrollments, this query returns no rows:

SELECT Students.Name, Enrollments.Course FROM Students INNER JOIN Enrollments ON Students.ID = Enrollments.StudentID;

What is the most likely reason?

A INNER JOIN always returns zero rows if tables have different column names.

B The query syntax is incorrect and causes an error.

C There are no matching values between <code>Students.ID</code> and <code>Enrollments.StudentID</code>.

D The SELECT clause is missing a GROUP BY statement.

💡 Hint

Think about what INNER JOIN requires to return rows.

INNER JOIN returns rows only when join columns match. If no matches exist, result is empty.