LEFT JOIN and RIGHT JOIN in PostgreSQL - Deep Dive

Overview - LEFT JOIN and RIGHT JOIN

What is it?

LEFT JOIN and RIGHT JOIN are ways to combine rows from two tables based on a related column. LEFT JOIN returns all rows from the left table and matching rows from the right table, filling with NULLs if no match exists. RIGHT JOIN does the opposite: it returns all rows from the right table and matching rows from the left table, filling with NULLs if no match exists. These joins help you see related data even when some matches are missing.

Why it matters

Without LEFT and RIGHT JOINs, you would only see rows where both tables have matching data, missing important information from one side. These joins let you find unmatched records, like customers without orders or products without sales, which is crucial for complete analysis and decision-making.

Where it fits

Before learning LEFT and RIGHT JOINs, you should understand basic SELECT queries and INNER JOINs, which combine only matching rows. After mastering these joins, you can explore FULL OUTER JOINs, CROSS JOINs, and advanced filtering techniques to handle complex data relationships.

Mental Model

Core Idea

LEFT JOIN keeps all rows from the left table and adds matching rows from the right, while RIGHT JOIN keeps all rows from the right table and adds matching rows from the left, filling gaps with NULLs.

Think of it like...

Imagine two lists of friends: one from your school (left) and one from your sports club (right). A LEFT JOIN is like listing all your school friends and adding their sports club info if they have it. A RIGHT JOIN is like listing all sports club friends and adding their school info if available.

Left Table       Right Table
┌───────────┐    ┌───────────┐
│ A │ B    │    │ B │ C    │
├───────────┤    ├───────────┤
│ 1 │ x    │    │ x │ 100  │
│ 2 │ y    │    │ y │ 200  │
│ 3 │ z    │    │ z │ 300  │
└───────────┘    └───────────┘

LEFT JOIN Result:
┌────┬────┬─────┐
│ A  │ B  │ C   │
├────┼────┼─────┤
│ 1  │ x  │ 100 │
│ 2  │ y  │ 200 │
│ 3  │ z  │ 300 │
│ 4  │ w  │ NULL│
└────┴────┴─────┘

RIGHT JOIN Result:
┌────┬────┬─────┐
│ A  │ B  │ C   │
├────┼────┼─────┤
│ 1  │ x  │ 100 │
│ 2  │ y  │ 200 │
│ 3  │ z  │ 300 │
│NULL│ v  │ 400 │
└────┴────┴─────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic Table Joins

Concept: Introduce the idea of combining rows from two tables based on a shared column.

When you have two tables, you often want to see how their data relates. For example, a table of customers and a table of orders. A JOIN combines rows where a key matches, like customer ID. INNER JOIN shows only rows where both tables match.

Result

You get a new table with rows only where the key exists in both tables.

Understanding that JOINs combine related data is the foundation for all more complex joins.

2

Foundation

What is NULL in Joins?

Concept: Explain NULL as a placeholder for missing data in join results.

When a join tries to find matching rows but none exist, it fills the missing side with NULL. NULL means 'no data here'. This is important to know because it shows where matches are missing.

Result

Joined tables may have NULLs where no matching row exists.

Recognizing NULLs in join results helps you spot unmatched data and understand the completeness of your query.

3

Intermediate

LEFT JOIN: Keep All Left Rows

🤔Before reading on: do you think LEFT JOIN returns only matching rows or all rows from the left table? Commit to your answer.

Concept: LEFT JOIN returns all rows from the left table and matches from the right, filling NULLs when no match.

Imagine you want to list all customers and their orders if any. LEFT JOIN keeps every customer, even those without orders. The order columns will be NULL for customers with no orders.

Result

You get all left table rows, with right table data where available, NULL otherwise.

Knowing LEFT JOIN keeps all left rows helps you find missing matches on the right side.

4

Intermediate

RIGHT JOIN: Keep All Right Rows

🤔Before reading on: does RIGHT JOIN keep all rows from the right table or the left? Commit to your answer.

Concept: RIGHT JOIN returns all rows from the right table and matches from the left, filling NULLs when no match.

If you want to list all orders and their customers, even orders without customers, RIGHT JOIN keeps every order. Customer info will be NULL if missing.

Result

You get all right table rows, with left table data where available, NULL otherwise.

Understanding RIGHT JOIN is the mirror of LEFT JOIN, focusing on the right table's completeness.

5

Intermediate

Comparing LEFT JOIN and RIGHT JOIN

🤔Before reading on: do you think LEFT JOIN and RIGHT JOIN can produce the same results? Commit to your answer.

Concept: LEFT JOIN and RIGHT JOIN are symmetrical; switching table order switches the join type.

LEFT JOIN A to B is the same as RIGHT JOIN B to A. The difference is which table is considered 'left' or 'right'. This affects which rows are all kept.

Result

You see that changing table order changes which rows are preserved.

Knowing the symmetry helps you choose the join type based on which table you want to keep fully.

6

Advanced

Handling NULLs After Joins

🤔Before reading on: do you think NULLs in join results mean missing data or zero/empty values? Commit to your answer.

Concept: NULLs after joins mean no matching row, not zero or empty; this affects filtering and calculations.

When you see NULLs in joined columns, it means no match was found. If you filter with WHERE column = value, you might accidentally exclude these rows. Use WHERE column IS NULL or IS NOT NULL to handle them properly.

Result

You learn to write queries that correctly handle unmatched rows with NULLs.

Understanding NULL semantics prevents common bugs in filtering and aggregation after joins.

7

Expert

Performance and Execution of LEFT and RIGHT JOINs

🤔Before reading on: do you think LEFT JOIN and RIGHT JOIN have the same performance or does one tend to be faster? Commit to your answer.

Concept: LEFT and RIGHT JOINs are logically symmetrical but query planners may optimize differently based on table size and indexes.

PostgreSQL query planner decides join order and method. Sometimes rewriting RIGHT JOIN as LEFT JOIN by swapping tables helps optimization. Understanding execution plans helps write faster queries.

Result

You can optimize queries by choosing join direction and analyzing execution plans.

Knowing how the database executes joins helps you write efficient queries and avoid slowdowns.

Under the Hood

LEFT JOIN and RIGHT JOIN work by scanning the 'preserved' table (left for LEFT JOIN, right for RIGHT JOIN) and for each row, searching the other table for matching rows. If matches exist, rows are combined; if not, NULLs fill the missing side. Internally, the database uses indexes and join algorithms like nested loops, hash joins, or merge joins to perform this efficiently.

Why designed this way?

These joins were designed to let users see all data from one table while optionally including related data from another. This solves the problem of incomplete matches in relational data. LEFT and RIGHT JOINs provide flexibility depending on which table's completeness is more important. Alternatives like FULL OUTER JOIN exist but are more expensive and less commonly needed.

┌───────────────┐       ┌───────────────┐
│ Left Table    │       │ Right Table   │
│ (preserved)   │       │ (matched)     │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │ For each left row     │
       │ find matching right   │
       │ rows                  │
       │                       │
       ▼                       ▼
┌─────────────────────────────────────┐
│ Result Row: left columns + right columns or NULLs if no match │
└─────────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LEFT JOIN only return rows where both tables match? Commit yes or no.

Common Belief: LEFT JOIN returns only rows where both tables have matching keys.

Reality: LEFT JOIN returns all rows from the left table, even if there is no match in the right table, filling right columns with NULL.

Why it matters: Believing this causes missing important unmatched rows, leading to incomplete data analysis.

Quick: Is RIGHT JOIN just a rare, unnecessary join type? Commit yes or no.

Common Belief: RIGHT JOIN is rarely used and can be ignored in favor of LEFT JOIN.

Reality: RIGHT JOIN is just LEFT JOIN with table order swapped; it is useful when the right table's completeness matters or for readability.

Why it matters: Ignoring RIGHT JOIN limits query expressiveness and can lead to confusing rewrites.

Quick: Does NULL in join results mean zero or empty value? Commit yes or no.

Common Belief: NULL in join results means zero, empty string, or default value.

Reality: NULL means no matching row exists; it is different from zero or empty and must be handled explicitly.

Why it matters: Misinterpreting NULL leads to wrong calculations, filters, and data interpretation.

Quick: Can LEFT JOIN and RIGHT JOIN always be swapped without changing results? Commit yes or no.

Common Belief: LEFT JOIN and RIGHT JOIN are always interchangeable by swapping tables.

Reality: They are interchangeable only if you swap the table order and adjust the query accordingly; otherwise, results differ.

Why it matters: Assuming interchangeability without adjusting queries causes wrong data outputs.

Expert Zone

1

LEFT JOIN and RIGHT JOIN can produce different query plans depending on table size and indexes, affecting performance.

2

Using RIGHT JOIN is less common in practice; many experts prefer LEFT JOIN with swapped tables for clarity and consistency.

3

NULLs introduced by these joins can affect aggregate functions and filtering in subtle ways, requiring careful query design.

When NOT to use

Avoid LEFT or RIGHT JOIN when you need all rows from both tables regardless of matches; use FULL OUTER JOIN instead. For simple matching rows only, use INNER JOIN for better performance.

Production Patterns

In production, LEFT JOIN is often used to find missing related data, like customers without orders. RIGHT JOIN is used less but can simplify queries when the right table is the main focus. Query tuning often involves rewriting RIGHT JOIN as LEFT JOIN for planner optimization.

Connections

FULL OUTER JOIN

Builds-on LEFT and RIGHT JOIN concepts by combining both to keep all rows from both tables.

Understanding LEFT and RIGHT JOIN is essential to grasp FULL OUTER JOIN, which merges their behaviors to show all data.

Null Handling in Programming

Shares the concept of NULL as 'missing' or 'unknown' data that must be handled carefully.

Knowing how NULL works in joins helps understand null safety and error handling in programming languages.

Set Theory

LEFT and RIGHT JOINs relate to set operations where one set is fully included and the other partially matched.

Seeing joins as set operations clarifies why some rows appear with NULLs and how data overlaps.

Common Pitfalls

#1 Filtering on right table columns in WHERE clause after LEFT JOIN removes unmatched rows.

Wrong approach:

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.amount > 100;

Correct approach:

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.amount > 100 OR orders.amount IS NULL;

Root cause:

Filtering in WHERE after LEFT JOIN excludes rows with NULLs, losing unmatched left rows.

#2 Using RIGHT JOIN without understanding table order leads to confusing queries.

Wrong approach:

SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;

Correct approach:

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

Root cause:

RIGHT JOIN reverses table order; rewriting as LEFT JOIN improves readability and reduces errors.

#3 Assuming NULL means zero or empty causes wrong calculations.

Wrong approach:

SELECT SUM(orders.amount) FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.amount = 0;

Correct approach:

SELECT SUM(COALESCE(orders.amount, 0)) FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

Root cause:

NULL is not zero; COALESCE replaces NULL with zero for correct aggregation.

Key Takeaways

LEFT JOIN and RIGHT JOIN let you keep all rows from one table while adding matching rows from another, filling gaps with NULLs.

NULLs in join results mean no matching row, not zero or empty, and must be handled carefully in queries.

LEFT JOIN and RIGHT JOIN are symmetrical; choosing which to use depends on which table's rows you want to keep fully.

Filtering on joined columns requires care to avoid accidentally removing unmatched rows with NULLs.

Understanding how these joins work internally helps optimize queries and avoid common mistakes.

Practice

1. What does a LEFT JOIN do in PostgreSQL?

easy

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

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

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

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

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 with unmatched rows

   If there is no matching row in the right table, the result includes NULLs for right table columns.

3. Final Answer:

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

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 RIGHT JOIN
• Thinking LEFT JOIN returns only matched rows
• Assuming unmatched rows are dropped

2. Which of the following is the correct syntax for a RIGHT JOIN in PostgreSQL?

easy

A. SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

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

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

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

Solution

1. Step 1: Recall correct RIGHT JOIN syntax

   The correct syntax is: SELECT ... FROM left_table RIGHT JOIN right_table ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; matches the correct syntax exactly. Options B, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   RIGHT JOIN syntax = FROM left_table RIGHT JOIN right_table ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left_table RIGHT JOIN right_table ON condition [OK]

Common Mistakes:

• Placing JOIN keyword incorrectly
• Using WHERE instead of ON for join condition
• Misordering table names

3. Given these tables:

employees
id | name
1 | Alice
2 | Bob
3 | Carol

departments
id | dept_name
2 | Sales
3 | HR
4 | IT

What is the result of this query?

SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.id = departments.id ORDER BY employees.id;

medium

A. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

B. [{"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}, {"name": null, "dept_name": "IT"}]

C. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

D. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

Solution

1. Step 1: Understand LEFT JOIN with given tables

   LEFT JOIN keeps all employees. For each employee, it tries to find a department with matching id.

2. Step 2: Match rows and fill NULLs

   Alice (id=1) has no matching department, so dept_name is NULL. Bob (id=2) matches Sales. Carol (id=3) matches HR.

3. Final Answer:

   [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}] -> Option C

4. Quick Check:

   LEFT JOIN keeps all employees, unmatched dept_name is NULL [OK]

Hint: LEFT JOIN keeps all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are excluded
• Mixing up LEFT and RIGHT JOIN results
• Ignoring NULL values for unmatched rows

4. You wrote this query but it gives an error:

SELECT * FROM employees RIGHT JOIN departments employees.id = departments.id;

What is the error and how to fix it?

medium

A. RIGHT JOIN is not supported; use LEFT JOIN instead

B. Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id

C. Table names are reversed; switch employees and departments

D. Use WHERE instead of ON for join condition

Solution

1. Step 1: Identify syntax error in JOIN clause

   The query is missing the ON keyword before the join condition, which is required syntax.

2. Step 2: Correct the syntax

   Add ON before employees.id = departments.id to fix the error.

3. Final Answer:

   Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id -> Option B

4. Quick Check:

   JOIN condition must use ON keyword [OK]

Hint: JOIN conditions always need ON keyword before condition [OK]

Common Mistakes:

• Omitting ON keyword
• Using WHERE instead of ON for join condition
• Confusing table order in JOIN

5. You want to list all departments and their employees, including departments with no employees. Which query achieves this?

hard

A. SELECT departments.dept_name, employees.name FROM employees RIGHT JOIN departments WHERE employees.id = departments.id;

B. SELECT departments.dept_name, employees.name FROM employees LEFT JOIN departments ON employees.id = departments.id;

C. SELECT departments.dept_name, employees.name FROM departments RIGHT JOIN employees ON departments.id = employees.id;

D. SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id;

Solution

1. Step 1: Identify which table to keep all rows from

   We want all departments, even those without employees, so departments is the left table in LEFT JOIN.

2. Step 2: Use LEFT JOIN from departments to employees

   LEFT JOIN keeps all departments and adds matching employees or NULL if none.

3. Final Answer:

   SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id; -> Option D

4. Quick Check:

   LEFT JOIN from departments keeps all departments [OK]

Hint: LEFT JOIN from main table keeps all its rows, unmatched get NULLs [OK]

Common Mistakes:

• Using employees as left table instead of departments
• Using RIGHT JOIN incorrectly
• Confusing which table to keep all rows from