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.