Why advanced joins matter in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output of LEFT JOIN with NULL values

Consider two tables: Employees and Departments. Employees may or may not belong to a department.

What is the output of this query?

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.id
ORDER BY e.name;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(20), department_id INT);
CREATE TABLE Departments (id INT, department_name VARCHAR(20));
INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', NULL), (3, 'Charlie', 20);
INSERT INTO Departments VALUES (10, 'HR'), (20, 'IT');

A [('Alice', 'HR'), ('Bob', 'HR'), ('Charlie', 'IT')]

B [('Alice', 'HR'), ('Bob', NULL), ('Charlie', 'IT')]

C [('Alice', 'HR'), ('Charlie', 'IT')]

D [('Bob', NULL)]

💡 Hint

LEFT JOIN keeps all rows from the left table even if there is no matching row in the right table.

The LEFT JOIN returns all employees. For Bob, who has no department_id, the department_name is NULL.

🧠 Conceptual

intermediate

Why use FULL OUTER JOIN?

Which situation best explains why a FULL OUTER JOIN is useful?

A To get all records from the left table and matching from the right only.

B To get only matching records from both tables.

C To get all records from both tables, including unmatched rows from either side.

D To get all records from the right table and matching from the left only.

💡 Hint

Think about when you want to see everything from both tables, even if no match exists.

FULL OUTER JOIN returns all rows from both tables, filling NULLs where no match exists.

📝 Syntax

advanced

Identify the syntax error in this JOIN query

Which option contains a syntax error in the JOIN clause?

SELECT a.id, b.value
FROM TableA a
JOIN TableB b ON a.id = b.a_id;

A SELECT a.id, b.value FROM TableA a JOIN TableB b WHERE a.id = b.a_id;

B SELECT a.id, b.value FROM TableA a INNER JOIN TableB b ON a.id = b.a_id;

C SELECT a.id, b.value FROM TableA a JOIN TableB b ON a.id = b.a_id;

D SELECT a.id, b.value FROM TableA a LEFT JOIN TableB b ON a.id = b.a_id;

💡 Hint

Check where the join condition is placed.

JOIN requires the ON clause for the join condition, not WHERE.

❓ optimization

advanced

Optimizing JOINs for large tables

You have two large tables and want to join them efficiently. Which approach is best?

A Use INNER JOIN with indexed join columns and filter early in WHERE clause.

B Use CROSS JOIN and filter results with WHERE clause after join.

C Use FULL OUTER JOIN without indexes to get all data.

D Use LEFT JOIN without any filters or indexes.

💡 Hint

Indexes help speed up joins by quickly matching rows.

INNER JOIN with indexes and early filtering reduces data processed and speeds query.

🔧 Debug

expert

Why does this JOIN query return duplicate rows?

Given these tables:

Orders(order_id, customer_id)
Customers(customer_id, name)

Query:

SELECT o.order_id, c.name
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id;

Why might this query return duplicate rows for the same order?

A Because the query is missing a GROUP BY clause.

B Because Orders table has duplicate order_id values causing multiple matches.

C Because JOIN syntax is incorrect and duplicates rows by default.

D Because Customers table has duplicate customer_id values causing multiple matches.

💡 Hint

Check if the join keys are unique in both tables.

If Customers has duplicate customer_id, each order matches multiple customers, causing duplicates.