Why LEFT JOIN execution behavior in SQL? - Purpose & Use Cases

The Big Idea

What if you could instantly see all your friends and their gifts without missing a single one?

The Scenario

Imagine you have two lists: one with all your friends and another with the gifts they gave you. You want to see every friend and the gift they gave, if any. Doing this by hand means checking each friend one by one and matching gifts, which is slow and confusing.

The Problem

Manually matching these lists is error-prone and takes a lot of time, especially if the lists are long. You might miss friends who didn't give gifts or accidentally skip some gifts. It's hard to keep track and easy to make mistakes.

The Solution

The LEFT JOIN in SQL automatically pairs each friend with their gift if it exists, and still shows friends without gifts clearly. It saves time, avoids errors, and gives a complete picture in one simple step.

Before vs After

✗ Before

for friend in friends:
    gift = find_gift_for(friend)
    print(friend, gift if gift else 'No gift')

✓ After

SELECT friends.name, gifts.item
FROM friends
LEFT JOIN gifts ON friends.id = gifts.friend_id;

What It Enables

LEFT JOIN lets you combine related data while keeping all main records visible, even when matches are missing.

Real Life Example

In a store, you want to list all customers and any orders they placed. LEFT JOIN shows every customer, including those who haven't bought anything yet.

Key Takeaways

LEFT JOIN keeps all records from the first table, adding matching data from the second.

It helps find missing matches without losing main data.

It simplifies combining related information efficiently.

Practice

1. What does a LEFT JOIN do in SQL?

easy

A. Returns only rows that have matching values in both tables

B. Returns all rows from the left table and matching rows from the right table, NULL if no match

C. Returns all rows from the right table and matching rows from the left table

D. Returns rows only from the left table without any matching

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens when no match exists

   If no matching row exists in the right table, the result shows NULL for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, NULL if no match -> Option B

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

Hint: LEFT JOIN keeps all left rows, fills right with NULL if no match [OK]

Common Mistakes:

• Confusing LEFT JOIN with INNER JOIN
• Thinking LEFT JOIN returns only matching rows
• Assuming NULLs never appear in results

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

easy

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

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

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

D. SELECT * FROM table1 LEFT JOIN table2 USING id;

Solution

1. Step 1: Recall correct LEFT JOIN syntax

   The correct syntax uses LEFT JOIN followed by ON clause to specify join condition.

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; uses correct syntax: LEFT JOIN with ON condition. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has incorrect order. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON. SELECT * FROM table1 LEFT JOIN table2 USING id; uses USING without parentheses, which is invalid syntax.

3. Final Answer:

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

4. Quick Check:

   LEFT JOIN ... ON condition is standard syntax [OK]

Hint: Use LEFT JOIN ... ON condition for correct syntax [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Swapping JOIN and LEFT keywords
• Confusing USING with ON without proper column names

3. Given these tables:
Employees(id, name)
Departments(id, dept_name, manager_id)
What will this query return?

SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.id = d.manager_id;

medium

A. All employees with their department names if they are managers, NULL otherwise

B. Only employees who are managers with their department names

C. All departments with their managers' names

D. Only departments with managers matching employee ids

Solution

1. Step 1: Analyze LEFT JOIN condition

   The query LEFT JOINs Employees (left) with Departments (right) on employee id matching department manager_id.

2. Step 2: Understand output rows

   All employees appear. If an employee is a manager (id matches manager_id), department name shows; else department columns are NULL.

3. Final Answer:

   All employees with their department names if they are managers, NULL otherwise -> Option A

4. Quick Check:

   LEFT JOIN keeps all employees, adds department if manager [OK]

Hint: LEFT JOIN keeps all left rows, adds right data if matched [OK]

Common Mistakes:

• Thinking only managers appear in result
• Confusing which table is left or right
• Expecting departments without managers to appear

4. Identify the error in this SQL query:

SELECT a.id, b.value FROM A a LEFT JOIN B b ON a.id = b.id WHERE b.value > 10;

medium

A. The ON clause is missing a join condition

B. The SELECT clause must include all columns from both tables

C. LEFT JOIN should be INNER JOIN for this query

D. The WHERE clause filters out rows where b.value is NULL, negating LEFT JOIN effect

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   The WHERE clause filters rows after join. Filtering on b.value > 10 excludes rows where b.value is NULL.

2. Step 2: Effect on LEFT JOIN

   This filtering removes rows without matches in B, making LEFT JOIN behave like INNER JOIN.

3. Final Answer:

   The WHERE clause filters out rows where b.value is NULL, negating LEFT JOIN effect -> Option D

4. Quick Check:

   Filtering on right table in WHERE breaks LEFT JOIN [OK]

Hint: Use ON for right table filters, not WHERE, to keep LEFT JOIN effect [OK]

Common Mistakes:

• Filtering right table columns in WHERE after LEFT JOIN
• Confusing ON and WHERE clauses
• Assuming LEFT JOIN always keeps all left rows regardless of WHERE

5. You want to list all customers and their last order date if any. Which query correctly uses LEFT JOIN to achieve this?

Customers(id, name)
Orders(id, customer_id, order_date)

hard

A. SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

B. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders);

C. SELECT c.name, o.order_date FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id;

D. SELECT c.name, MAX(o.order_date) FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

Solution

1. Step 1: Understand requirement for all customers

   We want all customers listed, even those without orders, so LEFT JOIN is needed.

2. Step 2: Aggregate last order date per customer

   Using MAX(o.order_date) with GROUP BY c.name gives last order date or NULL if no orders.

3. Step 3: Check options

   SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; uses LEFT JOIN and GROUP BY correctly. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders); filters in WHERE, excluding customers without orders. Options A and C use INNER JOIN, excluding customers without orders.

4. Final Answer:

   SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; -> Option A

5. Quick Check:

   LEFT JOIN + GROUP BY + MAX gets last order date including customers without orders [OK]

Hint: Use LEFT JOIN with GROUP BY and MAX to include all left rows [OK]

Common Mistakes:

• Using INNER JOIN excludes customers without orders
• Filtering right table in WHERE removes unmatched rows
• Not grouping when using aggregate functions