INNER JOIN syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INNER JOIN query?

Consider two tables:

Employees:
id | name
1 | Alice
2 | Bob
3 | Carol

Departments:
emp_id | department
1 | Sales
3 | HR
4 | IT

What rows will this query return?

SELECT Employees.name, Departments.department
FROM Employees
INNER JOIN Departments ON Employees.id = Departments.emp_id;

SQL

SELECT Employees.name, Departments.department
FROM Employees
INNER JOIN Departments ON Employees.id = Departments.emp_id;

A [{"name": "Alice", "department": "Sales"}, {"name": "Bob", "department": "HR"}]

B [{"name": "Bob", "department": "Sales"}, {"name": "Carol", "department": "HR"}]

C [{"name": "Alice", "department": "Sales"}, {"name": "Carol", "department": "HR"}]

D [{"name": "Alice", "department": "Sales"}, {"name": "Carol", "department": "HR"}, {"name": "Bob", "department": "IT"}]

💡 Hint

INNER JOIN returns only rows with matching keys in both tables.

The INNER JOIN matches Employees.id with Departments.emp_id. Only Alice (id=1) and Carol (id=3) have matching department entries. Bob (id=2) has no department, and emp_id=4 in Departments has no matching employee.

🧠 Conceptual

intermediate

Which statement about INNER JOIN is true?

Choose the correct statement about INNER JOIN in SQL.

A INNER JOIN returns only rows where there is a match in both joined tables.

B INNER JOIN returns all rows from the left table, even if there is no match in the right table.

C INNER JOIN returns all rows from the right table, even if there is no match in the left table.

D INNER JOIN returns all rows from both tables, matching or not.

💡 Hint

Think about what 'inner' means in INNER JOIN.

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

📝 Syntax

advanced

Which query has correct INNER JOIN syntax?

Identify the query with valid INNER JOIN syntax.

A SELECT * FROM A JOIN B WHERE A.id = B.id;

B SELECT * FROM A INNER JOIN B ON A.id = B.id;

C SELECT * FROM A INNER JOIN B USING (id);

D SELECT * FROM A INNER JOIN B ON A.id == B.id;

💡 Hint

Check the JOIN clause and ON condition syntax carefully.

Option B uses correct INNER JOIN syntax with ON and single equals sign. Option B misses ON keyword. Option B uses USING which is valid in some SQL dialects but not standard SQL. Option B 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 a foreign key. Which method improves INNER JOIN query speed?

A Use CROSS JOIN instead of INNER JOIN for faster results.

B Use SELECT * to retrieve all columns to avoid specifying columns.

C Remove WHERE clause filters to reduce query complexity.

D Add indexes on the columns used in the JOIN condition.

💡 Hint

Think about how databases find matching rows quickly.

Indexes on join columns help the database quickly locate matching rows, improving performance. SELECT * can slow queries by returning unnecessary data. Removing filters increases data processed. CROSS JOIN returns all combinations, which is slower.

🔧 Debug

expert

What error does this INNER JOIN query raise?

Given these tables:

Table A: id, value
Table B: id, description

Query:

SELECT A.id, B.description
FROM A INNER JOIN B ON A.id = B.idd;

What error will this query produce?

A Column 'B.idd' does not exist

B Ambiguous column name 'id'

C No error, returns empty result

D Syntax error near 'idd'

💡 Hint

Check the column names used in the ON clause carefully.

The column 'idd' does not exist in table B, so the database will raise an error about the missing column.

Practice

1. What does an INNER JOIN do in SQL?

easy

A. It returns all rows from the first table only.

B. It returns rows that have matching values in both tables.

C. It returns all rows from the second table only.

D. It returns all rows from both tables, matching or not.

Solution

1. Step 1: Understand the purpose of INNER JOIN

   INNER JOIN combines rows from two tables where the join condition matches in both tables.

2. Step 2: Compare options with INNER JOIN behavior

   Only the description "It returns rows that have matching values in both tables." correctly states that it returns rows with matching values in both tables.

3. Final Answer:

   It returns rows that have matching values in both tables. -> Option B

4. Quick Check:

   INNER JOIN = matching rows only [OK]

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

Common Mistakes:

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

2. Which of the following is the correct syntax for an INNER JOIN between tables Employees and Departments on the column DeptID using the ON clause?

easy

A. SELECT * FROM Employees INNER JOIN Departments ON Employees.DeptID == Departments.DeptID;

B. SELECT * FROM Employees JOIN Departments WHERE Employees.DeptID = Departments.DeptID;

C. SELECT * FROM Employees INNER JOIN Departments USING DeptID;

D. SELECT * FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;

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: Check each option

   SELECT * FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID; uses correct INNER JOIN syntax with ON and =. SELECT * FROM Employees JOIN Departments WHERE Employees.DeptID = Departments.DeptID; uses WHERE instead of ON. SELECT * FROM Employees INNER JOIN Departments USING DeptID; uses USING instead of ON. SELECT * FROM Employees INNER JOIN Departments ON Employees.DeptID == Departments.DeptID; uses double equals (==), which is not valid in SQL.

3. Final Answer:

   SELECT * FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID; -> Option D

4. Quick Check:

   INNER JOIN syntax = ON with single = [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 (=)
• Using USING instead of ON

3. Given the tables:
Employees(id, name, dept_id)
Departments(dept_id, dept_name)
What is the result of this query?

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

Assuming:
Employees: (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Carol', 30)
Departments: (10, 'HR'), (20, 'Sales')

medium

A. [('Alice', 'HR'), ('Bob', 'Sales')]

B. [('Alice', 'HR'), ('Bob', 'Sales'), ('Carol', '30')]

C. [('Alice', 'HR'), ('Bob', 'Sales'), ('Carol', NULL)]

D. [('Alice', 'HR'), ('Bob', 'Sales'), ('Carol', 'Finance')]

Solution

1. Step 1: Identify matching rows by dept_id

   Employees with dept_id 10 and 20 match Departments with same dept_id. Carol's dept_id 30 has no match.

2. Step 2: Understand INNER JOIN output

   INNER JOIN returns only rows with matching dept_id in both tables, so Carol is excluded.

3. Final Answer:

   [('Alice', 'HR'), ('Bob', 'Sales')] -> Option A

4. Quick Check:

   INNER JOIN excludes unmatched rows [OK]

Hint: INNER JOIN excludes rows without matching keys [OK]

Common Mistakes:

• Including unmatched rows in result
• Assuming NULL values appear for unmatched rows
• Confusing INNER JOIN with LEFT JOIN behavior

4. Identify the error in this SQL query:

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

medium

A. The join condition uses '==' instead of '='.

B. Using alias names for tables is not allowed.

C. Missing WHERE clause for filtering.

D. INNER JOIN requires USING instead of ON.

Solution

1. Step 1: Check join condition syntax

   SQL uses a single equals sign (=) for comparison, not double equals (==).

2. Step 2: Verify other parts of the query

   Aliases e and d are valid. WHERE clause is optional. INNER JOIN can use ON.

3. Final Answer:

   The join condition uses '==' instead of '='. -> Option A

4. Quick Check:

   Use = for join conditions, not == [OK]

Hint: Use single = in ON clause, not double == [OK]

Common Mistakes:

• Using == instead of = in join condition
• Thinking aliases are disallowed
• Confusing ON with WHERE clause necessity

5. You have two tables:
Orders(order_id, customer_id, amount)
Customers(customer_id, customer_name)
You want to find all customers who have placed orders and the total amount they spent.
Which query correctly uses INNER JOIN and aggregation to get this result?

hard

A. SELECT c.customer_name, SUM(o.amount) AS total_spent FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name;

B. SELECT c.customer_name, o.amount FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id;

C. SELECT c.customer_name, SUM(o.amount) AS total_spent FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name;

D. SELECT customer_name, amount FROM Customers INNER JOIN Orders ON customer_id = customer_id;

Solution

1. Step 1: Understand the requirement

   We want customers who placed orders and the total amount spent, so INNER JOIN with aggregation is needed.

2. Step 2: Analyze each option

   SELECT c.customer_name, SUM(o.amount) AS total_spent FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name; correctly uses INNER JOIN on customer_id and sums amounts grouped by customer_name. SELECT c.customer_name, SUM(o.amount) AS total_spent FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name; uses LEFT JOIN, which includes customers without orders. SELECT c.customer_name, o.amount FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id; does not aggregate amounts. SELECT customer_name, amount FROM Customers INNER JOIN Orders ON customer_id = customer_id; has ambiguous join condition and no aggregation.

3. Final Answer:

   SELECT c.customer_name, SUM(o.amount) AS total_spent FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name; -> Option C

4. Quick Check:

   INNER JOIN + GROUP BY + SUM = total spent per customer [OK]

Hint: Use INNER JOIN with GROUP BY and SUM for totals [OK]

Common Mistakes:

• Using LEFT JOIN instead of INNER JOIN when only matching rows needed
• Forgetting GROUP BY with aggregation
• Incorrect join condition syntax