Why joins are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why do we use JOINs in SQL?

Imagine you have two tables: Customers and Orders. Each order belongs to a customer. Why do we need to use a JOIN to get a list of customers with their orders?

A Because JOINs delete duplicate rows from tables to keep data clean.

B Because JOINs create new tables permanently to store combined data.

C Because JOINs sort the data alphabetically in the result set.

D Because JOINs combine rows from two or more tables based on a related column, allowing us to see connected data in one result.

💡 Hint

Think about how you can see information from both tables together in one list.

JOINs let us combine rows from different tables using a common column, like customer ID, so we can see related information together.

❓ query_result

intermediate

Output of INNER JOIN between Customers and Orders

Given these tables:

Customers:
1, Alice
2, Bob

Orders:
101, 1
102, 2
103, 1

What is the result of this query?

SELECT Customers.Name, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.ID = Orders.CustomerID;

A Alice, 101<br>Alice, 103<br>Bob, 102

B Alice, 101<br>Bob, 102<br>NULL, 103

C Alice, 101<br>Bob, 102

D Alice, 101<br>Alice, 103

💡 Hint

INNER JOIN returns rows where the join condition matches in both tables.

The INNER JOIN matches customers with their orders by customer ID, so Alice has orders 101 and 103, Bob has order 102.

📝 Syntax

advanced

Identify the syntax error in this JOIN query

Which option contains a syntax error in the JOIN clause?

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

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

B SELECT * FROM Customers JOIN Orders ON Customers.ID = Orders.CustomerID;

C SELECT * FROM Customers JOIN Orders Customers.ID = Orders.CustomerID;

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

💡 Hint

Look for missing keywords or misplaced clauses in the JOIN syntax.

Option C is missing the ON keyword before the join condition, causing a syntax error.

❓ optimization

advanced

Optimizing JOINs for performance

You have two large tables: Employees and Departments. You want to list employees with their department names. Which option is best to improve query speed?

A Use CROSS JOIN to combine all rows and then filter with WHERE.

B Use INNER JOIN on indexed columns and select only needed columns.

C Use LEFT JOIN without any indexes and select all columns.

D Use subqueries instead of JOINs to get department names.

💡 Hint

Think about how indexes and selecting fewer columns affect speed.

Using INNER JOIN on indexed columns and selecting only necessary columns reduces data processed and speeds up the query.

🔧 Debug

expert

Why does this JOIN query return fewer rows than expected?

Tables:

Students: 1, John
2, Mary
3, Alex

Enrollments: 1, Math
2, Science

Query:

SELECT Students.Name, Enrollments.Course FROM Students INNER JOIN Enrollments ON Students.ID = Enrollments.StudentID;

Why does Alex not appear in the result?

A Because INNER JOIN only returns rows with matching keys in both tables, and Alex has no enrollment record.

B Because Alex's name is misspelled in the Students table.

C Because the query syntax is incorrect and skips some rows.

D Because the Enrollments table has duplicate courses causing conflicts.

💡 Hint

Think about how INNER JOIN works when one table has missing matching rows.

INNER JOIN returns only rows where the join condition matches in both tables. Alex has no matching enrollment, so is excluded.