Inner join in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Inner Join Result Rows Count

Given two tables Employees and Departments, where Employees has columns EmpID, Name, DeptID and Departments has columns DeptID, DeptName, what is the number of rows returned by the following inner join query?

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

A The total number of departments regardless of employees

B The total number of employees regardless of department

C The number of employees who have a matching department in Departments

D The sum of employees and departments counts

💡 Hint

Think about what inner join does: it keeps only rows where the join condition matches in both tables.

An inner join returns only rows where the join condition is true. Here, it returns employees who have a matching department in Departments table.

🧠 Conceptual

intermediate

Understanding Inner Join Behavior

Which statement best describes what an inner join does between two tables?

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

B Returns all rows from the first table and matching rows from the second table, filling with NULLs if no match

C Returns all rows from both tables, combining them regardless of matches

D Returns all rows from the second table and matching rows from the first table

💡 Hint

Remember inner join keeps only matching rows from both tables.

Inner join returns only rows where the join condition matches in both tables, excluding rows without matches.

📝 Syntax

advanced

Identify the Correct Inner Join Syntax

Which of the following SQL queries correctly performs an inner join between tables Orders and Customers on the column CustomerID?

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

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

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

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

💡 Hint

Check the correct syntax for ON clause and join condition operator.

Option A uses correct INNER JOIN syntax with ON and single equals sign for comparison. Option A uses WHERE instead of ON, which is invalid here. Option A uses USING without parentheses which is invalid syntax. Option A uses double equals which is not valid SQL syntax.

❓ optimization

advanced

Optimizing Inner Join Performance

You have two large tables, Sales and Products, joined on ProductID. Which of the following will most improve the performance of the inner join query?

A Use a LEFT JOIN instead of INNER JOIN

B Create an index on Sales.ProductID and Products.ProductID columns

C Add a WHERE clause filtering on a non-indexed column after the join

D Use SELECT * to retrieve all columns from both tables

💡 Hint

Indexes help speed up join operations on large tables.

Creating indexes on the join columns allows the database to quickly find matching rows, improving join performance. Selecting all columns or filtering on non-indexed columns does not help join speed. LEFT JOIN returns more rows and is not an optimization here.

🔧 Debug

expert

Debugging Unexpected Inner Join Result

You run this query:

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

But some employees you expect to see are missing. What is the most likely reason?

A The query should use LEFT JOIN instead of INNER JOIN

B There is a syntax error in the query causing rows to be skipped

C The Departments table is empty

D Some employees have DeptID values that do not exist in Departments table

💡 Hint

Inner join excludes rows without matching keys in both tables.

If employees have DeptID values not present in Departments, inner join excludes them. LEFT JOIN would include them with NULLs. The query syntax is correct. If Departments were empty, no rows would return, but the question implies some rows return.

Practice

1. What does an inner join do when combining two tables?

easy

A. Returns only rows with matching keys in both tables

B. Returns all rows from the first table, matching or not

C. Returns all rows from the second table, matching or not

D. Returns all rows from both tables, matching or not

Solution

1. Step 1: Understand join types

   Inner join returns rows where keys match in both tables only.

2. Step 2: Compare options with definition

   The option 'Returns only rows with matching keys in both tables' describes this behavior correctly.

3. Final Answer:

   Returns only rows with matching keys in both tables -> Option A

4. Quick Check:

   Inner join = matching rows only [OK]

Hint: Inner join keeps only matching rows from both tables [OK]

Common Mistakes:

• Confusing inner join with left or right join
• Thinking inner join returns unmatched rows
• Mixing up join types and their results

2. Which of the following is the correct syntax to perform an inner join of two pandas DataFrames df1 and df2 on column 'id'?

easy

A. df1.join(df2, on='id', how='outer')

B. pd.concat([df1, df2], axis=1, join='inner')

C. pd.merge(df1, df2, on='id', how='inner')

D. df1.merge(df2, on='id', how='left')

Solution

1. Step 1: Identify pandas inner join syntax

   The correct function is pd.merge() with how='inner' and on='id'.

2. Step 2: Check each option

   pd.merge(df1, df2, on='id', how='inner') matches this exactly; others use wrong methods or join types.

3. Final Answer:

   pd.merge(df1, df2, on='id', how='inner') -> Option C

4. Quick Check:

   pd.merge + how='inner' = inner join [OK]

Hint: Use pd.merge with how='inner' for inner joins [OK]

Common Mistakes:

• Using join() instead of merge() for inner join
• Using wrong join type like 'outer' or 'left'
• Using concat which does not perform join

3. Given two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'val1': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'val2': ['X', 'Y', 'Z']})
result = pd.merge(df1, df2, on='id', how='inner')

What is the content of result?

medium

A. {'id': [2, 3], 'val1': ['B', 'C'], 'val2': ['X', 'Y']}

B. {'id': [1, 4], 'val1': ['A', None], 'val2': [None, 'Z']}

C. {'id': [2, 3, 4], 'val1': ['B', 'C', None], 'val2': ['X', 'Y', 'Z']}

D. {'id': [1, 2, 3], 'val1': ['A', 'B', 'C'], 'val2': ['X', 'Y', 'Z']}

Solution

1. Step 1: Identify matching keys for inner join

   Keys in both df1 and df2 are 2 and 3 only.

2. Step 2: Merge rows with matching keys

   Rows with id 2 and 3 combine val1 and val2 columns.

3. Final Answer:

   {'id': [2, 3], 'val1': ['B', 'C'], 'val2': ['X', 'Y']} -> Option A

4. Quick Check:

   Inner join keeps ids 2 and 3 only [OK]

Hint: Inner join keeps only common keys in both DataFrames [OK]

Common Mistakes:

• Including keys not present in both tables
• Confusing inner join with outer join
• Assuming all rows are kept

4. You wrote this code to inner join two DataFrames:

result = pd.merge(df1, df2, how='inner')

But it raises an error. What is the likely cause?

medium

A. Using 'how' parameter incorrectly

B. Missing the 'on' parameter to specify join key

C. DataFrames must be converted to lists first

D. Inner join is not supported by pandas

Solution

1. Step 1: Check required parameters for pd.merge

   pd.merge requires 'on' or 'left_on'/'right_on' to specify join keys.

2. Step 2: Identify missing 'on' parameter

   Code misses 'on' parameter, causing error.

3. Final Answer:

   Missing the 'on' parameter to specify join key -> Option B

4. Quick Check:

   pd.merge needs 'on' key [OK]

Hint: Always specify 'on' column for merge keys [OK]

Common Mistakes:

• Forgetting to specify join keys
• Assuming default join key exists
• Trying to join incompatible data types

5. You have two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 2, 3], 'val1': ['A', 'B', 'C', 'D']})
df2 = pd.DataFrame({'id': [2, 2, 3], 'val2': ['X', 'Y', 'Z']})
result = pd.merge(df1, df2, on='id', how='inner')

How many rows will result contain after the inner join?

hard

A. 6

B. 4

C. 3

D. 5

Solution

1. Step 1: Identify matching keys and duplicates

   Keys 2 and 3 appear multiple times in both DataFrames.

2. Step 2: Calculate Cartesian product for duplicates

   For id=2: df1 has 2 rows, df2 has 2 rows -> 2*2=4 rows.
   For id=3: df1 has 1 row, df2 has 1 row -> 1*1=1 row.
   Total rows = 4 + 1 = 5 rows.

3. Step 3: Recount carefully

   Recount confirms 5 rows total.

4. Final Answer:

   5 -> Option D

5. Quick Check:

   Duplicates multiply rows in inner join [OK]

Hint: Duplicates multiply rows in inner join result [OK]

Common Mistakes:

• Ignoring duplicate keys causing more rows
• Counting unique keys only
• Assuming one-to-one join always