The most common use of LEFT JOIN: finding rows that have NO match.

---

RIGHT JOIN — Keep All Right Rows

RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table. It is used rarely in practice because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order.

Use LEFT JOIN consistently and avoid RIGHT JOIN — it makes queries easier to read.

---

FULL OUTER JOIN — Keep All Rows from Both Sides

FULL OUTER JOIN returns all rows from both tables. Where there is no match, the missing side has NULLs.

Used less frequently than INNER and LEFT joins, but useful for finding unmatched rows on either side.

---

CROSS JOIN — Every Combination

A CROSS JOIN produces the Cartesian product — every row in the left table combined with every row in the right table.

This is rarely intentional. An explicit JOIN with no ON/USING clause is actually a syntax error in PostgreSQL — the grammar requires one. The accidental version comes from the old comma-join syntax instead, which silently is a cross join with no condition required:

---

Self-Joins — A Table Joining Itself

Sometimes you need to join a table to itself. The classic example: an employee table where each employee has a manager_id that references another employee's id.

The key: you use aliases (e and m) to distinguish the two "instances" of the same table.

---

Joining Three or More Tables

Joins chain naturally — each new JOIN adds another table:

---

Common Join Mistakes

Mistake 1: Duplicate rows from one-to-many joins

If a customer has 5 orders, joining customers to orders gives 5 rows for that customer. This is correct and expected — but surprises beginners who then aggregate incorrectly.

Mistake 2: Ambiguous column names

When two joined tables have a column with the same name, you must qualify which table's column you want:

Mistake 3: Missing the join condition

---

Practical Exercise: Complete Store Queries

Using the store database with customers, products, and orders:

---

Join Type Summary

When to use which:

• Finding related data → INNER JOIN
• Keeping all records even without a match → LEFT JOIN
• Finding records with no match → LEFT JOIN ... WHERE right.id IS NULL
• Comparing two complete sets → FULL OUTER JOIN

One join-performance trap worth knowing now: every join in this module leans on foreign-key columns like customer_id/product_id, but PostgreSQL does not automatically index foreign-key columns — only the referenced side (the primary/unique key being pointed at) gets an index for free. Without an explicit index on the FK column itself, joins and cascading deletes on the child table can end up doing a sequential scan. Module F-6 (constraints) and the indexing module both come back to this.

---

Module F-6 covers constraints — the rules that make your database trustworthy: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

Next: F-6 — Constraints and Data Integrity →

---

Question 2: A data analyst reports that a dashboard showing 'Total Revenue by Customer' is displaying NULL or 0 for some customers, even though the customers table clearly lists them. The SQL query used for the dashboard is:

As a Senior Principal Software Engineer, what is the most likely reason for this discrepancy and the correct immediate fix to include all customers, showing 0 for those with no orders?

• A) A. The products table has missing price data, causing NULLs in the SUM. The fix is to add a WHERE p.price IS NOT NULL clause.
• B) B. The JOIN between customers and orders is an INNER JOIN, which excludes customers without matching orders. The fix is to change it to a LEFT JOIN and use COALESCE for SUM.
• C) C. The GROUP BY clause is incorrect and should include o.id to ensure all orders are counted. The fix is to add o.id to GROUP BY.
• D) D. The SUM function implicitly handles NULLs as 0, so the issue must be that quantity or price are 0. The fix is to check the orders and products data.

Reveal Answer

Correct Answer: B

The lesson explicitly states that INNER JOIN (which JOIN defaults to) 'returns rows that have a match in both tables' and demonstrates that customers without orders (like David) 'do not appear' in the result. The problem describes customers being entirely missing or showing NULL/0 revenue, which is characteristic of INNER JOIN excluding them. The correct fix is to use a LEFT JOIN from customers to orders to ensure all customers are included. For customers with no orders, the orders and products columns will be NULL, causing SUM(o.quantity * p.price) to evaluate to NULL. To display 0 instead of NULL for these customers, COALESCE(SUM(o.quantity * p.price), 0) should be used. Option A is incorrect because INNER JOIN would already filter out rows where price is NULL if it's part of the join or calculation, but the primary issue is missing customers. Option C is incorrect; GROUP BY c.name is appropriate for summing per customer. Option D is incorrect; while SUM ignores NULLs, the core problem is the exclusion of entire customer rows, not just NULL values within existing rows.

Question 3: During a peak traffic event, a critical reporting service experiences severe database load, leading to timeouts and degraded user experience. Upon investigation, a newly deployed SQL query for a 'product compatibility matrix' report is identified as the culprit. The query looks like this:

Assuming the products table has N rows, what is the primary performance issue this query introduces, and what is the most direct consequence in a production environment?

• A) A. It creates an N*N Cartesian product, leading to excessive memory usage and network transfer for the result set.
• B) B. It causes a deadlock because p1 and p2 are aliases of the same table, leading to contention.
• C) C. It results in an N row output, but the database optimizer struggles with the implicit join syntax, causing slow execution.
• D) D. It performs an INNER JOIN by default, but without an ON clause, it scans the entire table multiple times, leading to high I/O.

Reveal Answer

Correct Answer: A

The lesson explicitly warns that 'If you write a JOIN without an ON clause, you accidentally get a cross join' and highlights SELECT * FROM customers, orders; as an 'old-style implicit join syntax' that is an 'ACCIDENTAL cross join.' A CROSS JOIN produces a Cartesian product, meaning every row from the first table is combined with every row from the second table. If the products table has N rows, joining it to itself this way will generate N * N rows. For even moderately sized tables (e.g., N=10,000), this results in 100 million rows, which can quickly overwhelm database memory, CPU, and network bandwidth, leading to the observed timeouts and degraded performance (A). Option B is incorrect; self-joins with aliases are a standard and safe practice and do not inherently cause deadlocks. Option C is incorrect; the output is N*N rows, not N. Option D is incorrect; while it is an implicit CROSS JOIN, the primary issue is the massive result set size, not just multiple table scans, and it's not an INNER JOIN in the sense of matching rows.