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.

Practice

1. What does an INNER JOIN do in MySQL?

easy

A. Combines rows from two tables where matching values exist in both tables.

B. Returns all rows from the first table regardless of matches.

C. Returns all rows from the second table regardless of matches.

D. Combines rows even if there is no matching value in either table.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows only when there is a match in both tables based on the join condition.

2. Step 2: Compare options with definition

   Combines rows from two tables where matching values exist in both tables. correctly describes this behavior. Options A, C, and D describe other join types or incorrect behavior.

3. Final Answer:

   Combines rows from two tables where matching values exist in both tables. -> Option A

4. Quick Check:

   INNER JOIN = matching rows only [OK]

Hint: INNER JOIN returns only rows with matching keys in both tables [OK]

Common Mistakes:

• Confusing INNER JOIN with LEFT or RIGHT JOIN
• Thinking INNER JOIN returns unmatched rows
• Assuming INNER JOIN returns all rows from one table

2. Which of the following is the correct syntax for an INNER JOIN in MySQL?

easy

A. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id;

C. SELECT * FROM table1 INNER JOIN table2 USING table1.id;

D. SELECT * FROM table1 INNER JOIN table2 ON table1.id == table2.id;

Solution

1. Step 1: Review INNER JOIN syntax

   The correct syntax uses INNER JOIN with ON and a single equals sign (=) for comparison.

2. Step 2: Analyze each option

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; uses correct syntax. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON, which is incorrect for JOIN conditions. SELECT * FROM table1 INNER JOIN table2 USING table1.id; uses USING with table prefix which is invalid; USING requires unqualified column names. SELECT * FROM table1 INNER JOIN table2 ON table1.id == table2.id; uses double equals (==) which is invalid in SQL.

3. Final Answer:

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   INNER JOIN syntax uses ON with = [OK]

Hint: Use ON with single = for INNER JOIN conditions [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals (==) instead of single equals (=)
• Confusing USING syntax with ON syntax

3. Given two tables:
Employees(id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT Employees.name, Departments.dept_name FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.dept_id;

medium

A. All departments with employees, even if no employees exist.

B. All employees, even if they have no department.

C. An error because dept_id is ambiguous.

D. All employees with their department names, only if department exists.

Solution

1. Step 1: Understand INNER JOIN effect on these tables

   The INNER JOIN returns rows where Employees.dept_id matches Departments.dept_id, so only employees with existing departments appear.

2. Step 2: Analyze query output

   The query selects employee names and their department names only for matching dept_id pairs. Employees without a department are excluded.

3. Final Answer:

   All employees with their department names, only if department exists. -> Option D

4. Quick Check:

   INNER JOIN filters to matching dept_id rows [OK]

Hint: INNER JOIN shows only matching rows from both tables [OK]

Common Mistakes:

• Thinking all employees appear even without departments
• Confusing INNER JOIN with LEFT JOIN
• Assuming ambiguous column error without aliasing

4. Identify the error in this query:

SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.id;

medium

A. Missing WHERE clause for join condition.

B. Column d.id does not exist; should be d.dept_id.

C. Alias 'e' is not allowed in INNER JOIN.

D. Using INNER JOIN without SELECT * causes error.

Solution

1. Step 1: Check join condition columns

   The join condition uses d.id, but Departments table has dept_id column, not id.

2. Step 2: Identify correct column name

   It should be d.dept_id to match Employees.dept_id. Other options are incorrect because aliasing is allowed, WHERE is not needed for JOIN, and SELECT * is not mandatory.

3. Final Answer:

   Column d.id does not exist; should be d.dept_id. -> Option B

4. Quick Check:

   Join columns must exist and match [OK]

Hint: Check column names in JOIN condition carefully [OK]

Common Mistakes:

• Using wrong column names in ON clause
• Thinking WHERE is required for JOIN
• Believing aliases are not allowed

5. You have two tables:
Orders(order_id, customer_id, amount)
Customers(customer_id, name)
You want to find customers who placed orders over 100 dollars. Which query correctly uses INNER JOIN to get customer names and order amounts over 100?

hard

A. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount < 100;

B. SELECT Customers.name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100;

C. SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100;

D. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount >= 100;

Solution

1. Step 1: Understand the requirement

   We want customers with orders over 100 dollars, so filter Orders.amount > 100.

2. Step 2: Check INNER JOIN usage and filter

   SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; correctly joins Customers and Orders on customer_id and filters orders with amount > 100. SELECT Customers.name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; uses LEFT JOIN instead of INNER JOIN. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount >= 100; uses >= 100 which includes 100, not strictly over 100. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount < 100; filters for amount < 100, opposite of requirement.

3. Final Answer:

   SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; -> Option C

4. Quick Check:

   INNER JOIN + filter amount > 100 = SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; [OK]

Hint: Use INNER JOIN with WHERE to filter matching rows [OK]

Common Mistakes:

• Using LEFT JOIN instead of INNER JOIN
• Using wrong comparison operator in WHERE
• Filtering on wrong table or column