Why outer joins are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Outer Joins

Why do we use outer joins in SQL instead of just inner joins?

A To delete rows from one table based on another table

B To speed up query execution by skipping unmatched rows

C To combine only rows that have matching values in both tables

D To include all rows from one table even if there is no matching row in the other table

💡 Hint

Think about what happens when some data in one table has no match in the other.

Outer joins allow us to keep all rows from one table and fill in NULLs for missing matches in the other table. Inner joins only keep rows with matches in both tables.

❓ query_result

intermediate

Result of LEFT OUTER JOIN

Given these tables:

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

Departments:
id | dept_name
1 | Sales
3 | HR

What is the result of this query?

SELECT Employees.name, Departments.dept_name
FROM Employees LEFT OUTER JOIN Departments ON Employees.id = Departments.id;

SQL

SELECT Employees.name, Departments.dept_name
FROM Employees LEFT OUTER JOIN Departments ON Employees.id = Departments.id;

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

B [('Alice', 'Sales'), ('Carol', 'HR')]

C [('Alice', NULL), ('Bob', NULL), ('Carol', NULL)]

D [('Bob', NULL)]

💡 Hint

LEFT OUTER JOIN keeps all rows from the left table.

The LEFT OUTER JOIN returns all employees. For Bob, there is no matching department, so dept_name is NULL.

📝 Syntax

advanced

Correct Syntax for FULL OUTER JOIN

Which of the following SQL queries correctly uses FULL OUTER JOIN to combine two tables 'A' and 'B' on column 'id'?

A SELECT * FROM A JOIN FULL OUTER B ON A.id = B.id;

B SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

C SELECT * FROM A OUTER FULL JOIN B ON A.id = B.id;

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

💡 Hint

FULL OUTER JOIN syntax requires the keywords in a specific order.

The correct syntax is 'FULL OUTER JOIN' with ON clause specifying the join condition. Other options have wrong keyword order or missing ON.

❓ optimization

advanced

Optimizing Queries with Outer Joins

You have a large table 'Orders' and a smaller table 'Customers'. You want to list all customers and their orders, including customers with no orders. Which join and indexing strategy is best for performance?

A Use LEFT OUTER JOIN from Customers to Orders, and index Orders.customer_id

B Use INNER JOIN from Orders to Customers, and index Customers.id

C Use RIGHT OUTER JOIN from Orders to Customers, and no indexes

D Use CROSS JOIN and filter with WHERE clause

💡 Hint

Think about which table should be on the left and which column to index for fast lookups.

LEFT OUTER JOIN from Customers to Orders ensures all customers appear. Indexing Orders.customer_id speeds up matching orders lookup.

🔧 Debug

expert

Why does this OUTER JOIN query return fewer rows than expected?

Given tables:
Products(id, name)
Sales(product_id, quantity)

Query:
SELECT Products.name, Sales.quantity
FROM Products LEFT OUTER JOIN Sales ON Products.id = Sales.product_id
WHERE Sales.quantity > 10;

Why might this query return fewer rows than the total number of products?

A Because Products.id and Sales.product_id have different data types causing join failure

B Because LEFT OUTER JOIN only returns rows with matching Sales records

C Because the WHERE clause filters out rows where Sales.quantity is NULL, removing unmatched products

D Because the query syntax is invalid and causes an error

💡 Hint

Consider how WHERE conditions affect rows with NULLs from outer joins.

The WHERE clause filters after the join, so rows with no matching Sales (NULL quantity) are excluded. To keep all products, the condition should be in the JOIN or use IS NULL checks.