Joins in SQL in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding INNER JOIN behavior

Given two tables, Employees and Departments, what will be the result of an INNER JOIN on the department ID if some employees do not belong to any department?

DBMS Theory

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

A Only departments with employees will appear, excluding employees without departments.

B Only employees who have a matching department will appear in the result.

C All employees will appear, with NULL for department names if no match exists.

D All departments will appear, with NULL for employee names if no match exists.

💡 Hint

Think about what INNER JOIN does when there is no matching record in the other table.

INNER JOIN returns only rows where there is a match in both tables. Employees without a department will be excluded.

📋 Factual

intermediate

Identifying LEFT JOIN output

What does a LEFT JOIN return when joining Customers with Orders on customer ID, if some customers have no orders?

DBMS Theory

SELECT Customers.name, Orders.order_id FROM Customers LEFT JOIN Orders ON Customers.id = Orders.customer_id;

A All orders appear; orders without customers show NULL for customer name.

B Only customers with orders appear; others are excluded.

C All customers appear; customers without orders show NULL for order_id.

D Only customers without orders appear.

💡 Hint

LEFT JOIN keeps all rows from the left table regardless of matches.

LEFT JOIN returns all rows from the left table (Customers). If no matching order exists, order columns are NULL.

🔍 Analysis

advanced

Determining output of FULL OUTER JOIN

Consider two tables: Authors and Books. What will a FULL OUTER JOIN on author ID return if some authors have no books and some books have no authors?

DBMS Theory

SELECT Authors.name, Books.title FROM Authors FULL OUTER JOIN Books ON Authors.id = Books.author_id;

A All authors and all books appear; unmatched authors or books show NULL in the other column.

B Only authors with books appear; unmatched authors or books are excluded.

C Only books with authors appear; unmatched authors or books are excluded.

D Only authors without books appear.

💡 Hint

FULL OUTER JOIN combines LEFT and RIGHT JOIN results.

FULL OUTER JOIN returns all rows from both tables. Where no match exists, NULL fills the missing side.

❓ Comparison

advanced

Comparing CROSS JOIN and INNER JOIN

What is the main difference between a CROSS JOIN and an INNER JOIN when joining two tables without any ON condition?

DBMS Theory

SELECT * FROM TableA CROSS JOIN TableB;
SELECT * FROM TableA INNER JOIN TableB;

A CROSS JOIN returns the Cartesian product; INNER JOIN without ON is invalid syntax.

B Both return the Cartesian product of the two tables.

C INNER JOIN returns only matching rows; CROSS JOIN returns all rows from the first table.

D INNER JOIN returns all rows; CROSS JOIN returns only matching rows.

💡 Hint

Think about the requirement of ON clause in INNER JOIN.

INNER JOIN requires an ON condition to match rows. Without it, the query is invalid. CROSS JOIN returns all combinations.

❓ Reasoning

expert

Predicting number of rows after multiple joins

Given three tables: Students (10 rows), Enrollments (15 rows), and Courses (5 rows). Enrollments links Students and Courses by IDs. If you perform an INNER JOIN of Students to Enrollments, then INNER JOIN the result to Courses, how many rows will the final result have?

DBMS Theory

SELECT Students.name, Courses.title FROM Students INNER JOIN Enrollments ON Students.id = Enrollments.student_id INNER JOIN Courses ON Enrollments.course_id = Courses.id;

A 5 rows, equal to the number of courses.

B 10 rows, equal to the number of students.

C 30 rows, product of students and courses.

D 15 rows, equal to the number of enrollments.

💡 Hint

Enrollments represent each student-course pair; joins filter to matching pairs.

The join chains filter to enrollments linking students and courses. The final row count matches enrollments (15).

Practice

1. What does an INNER JOIN do in SQL?

easy

A. Returns all rows from the right table, and matched rows from the left

B. Returns all rows from the left table, and matched rows from the right

C. Returns only rows with matching values in both tables

D. Returns all rows from both tables, matching where possible

Solution

1. Step 1: Understand INNER JOIN behavior

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

2. Step 2: Compare with other join types

   LEFT JOIN returns all from left, RIGHT JOIN all from right, FULL JOIN all from both. INNER JOIN only matched rows.

3. Final Answer:

   Returns only rows with matching values in both tables -> Option C

4. Quick Check:

   INNER JOIN = matched rows only [OK]

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

Common Mistakes:

• Confusing INNER JOIN with LEFT JOIN
• Thinking INNER JOIN returns unmatched rows
• Mixing up LEFT and RIGHT JOIN definitions

2. Which of the following is the correct syntax for a LEFT JOIN in SQL?

easy

A. SELECT * FROM table1 LEFT JOIN table2 USING (id);

B. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id;

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

Solution

1. Step 1: Recall LEFT JOIN syntax

   The correct syntax is: SELECT columns FROM table1 LEFT JOIN table2 ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; matches correct syntax. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has wrong order. SELECT * FROM table1 LEFT OUTER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON. SELECT * FROM table1 LEFT JOIN table2 USING (id); uses correct syntax with parentheses.

3. Final Answer:

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; -> Option D

4. Quick Check:

   LEFT JOIN syntax = FROM table1 LEFT JOIN table2 ON condition [OK]

Hint: LEFT JOIN uses ON, not WHERE, for join condition [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Incorrect order of JOIN keywords
• Confusing USING with ON without matching columns

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

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

medium

A. All employees with their department names, only if dept_id matches

B. All employees, even if they have no department

C. All departments with employees, including departments with no employees

D. All employees and departments, matched or not

Solution

1. Step 1: Analyze INNER JOIN condition

   The INNER JOIN returns rows where Employees.dept_id matches Departments.dept_id.

2. Step 2: Understand result rows

   Only employees with a matching department will appear, showing employee name and department name.

3. Final Answer:

   All employees with their department names, only if dept_id matches -> Option A

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:

• Assuming employees without departments appear
• Confusing INNER JOIN with LEFT JOIN
• Thinking unmatched departments appear

4. Identify the error in this SQL join query:

SELECT * FROM Orders o LEFT JOIN Customers c ON o.customer_id = c.id WHERE c.country = 'USA';

medium

A. The WHERE clause filters out unmatched rows, defeating LEFT JOIN purpose

B. The ON clause should use = instead of LIKE

C. LEFT JOIN should be RIGHT JOIN here

D. Missing GROUP BY clause for aggregation

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   LEFT JOIN keeps all rows from Orders, matching Customers or NULL if no match.

2. Step 2: Effect of WHERE on joined table

   WHERE c.country = 'USA' removes rows where c is NULL (no match), so unmatched rows are excluded, negating LEFT JOIN effect.

3. Final Answer:

   The WHERE clause filters out unmatched rows, defeating LEFT JOIN purpose -> Option A

4. Quick Check:

   WHERE on joined table filters NULLs, breaks LEFT JOIN [OK]

Hint: Use ON for join filters, WHERE filters after join [OK]

Common Mistakes:

• Filtering joined table in WHERE instead of ON
• Confusing LEFT JOIN with RIGHT JOIN
• Adding unnecessary GROUP BY

5. You want to list all products and their suppliers, including products without suppliers and suppliers without products. Which join should you use?

hard

A. LEFT JOIN

B. FULL OUTER JOIN

C. RIGHT JOIN

D. INNER JOIN

Solution

1. Step 1: Understand join requirements

   We want all products and all suppliers, even if no matching row exists in the other table.

2. Step 2: Match join type to requirement

   INNER JOIN excludes unmatched rows. LEFT or RIGHT JOIN include unmatched rows from one side only. FULL OUTER JOIN includes unmatched rows from both tables.

3. Final Answer:

   FULL OUTER JOIN -> Option B

4. Quick Check:

   FULL OUTER JOIN returns all rows from both tables [OK]

Hint: Use FULL OUTER JOIN to include unmatched rows from both tables [OK]

Common Mistakes:

• Choosing INNER JOIN which excludes unmatched rows
• Using LEFT or RIGHT JOIN which include only one side unmatched
• Confusing FULL OUTER JOIN syntax