PostgreSQL Joins

JOINs are how PostgreSQL connects data from different tables.
They’re essential when you need to combine related information stored across multiple tables.

---

🔌 What Are JOINs?

When data is stored in separate tables (like customers and orders), JOINs let you pull that data together into a single result.

Think of it like: Connecting puzzle pieces to see the full picture.

---

🚦 Basic JOIN Syntax

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

---

🧩 Types of JOINs

Each JOIN type gives you different results depending on what data you need.

---

1. INNER JOIN → Common Ground

Returns rows that have matching values in both tables.

Example: Find customers and their orders

SELECT customers.name, orders.order_date, orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

✅ Gets only customers who have placed orders
✅ Gets only orders that have assigned customers
✅ Excludes unmatched records from both sides

---

2. LEFT JOIN (or LEFT OUTER JOIN) → Complete Left Side

Returns all rows from the left table, plus matched data from the right.

Example: Show all customers, including those without orders

SELECT customers.name, orders.order_date, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

✅ Gets all customers (even without orders)
✅ Shows order info where available
✅ Shows NULL for customers without orders

---

3. RIGHT JOIN (or RIGHT OUTER JOIN) → Complete Right Side

Returns all rows from the right table, plus matched data from the left.

Example: Show all orders, even if customer info is missing

SELECT customers.name, orders.order_date, orders.total
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

✅ Gets all orders (even those without customer data)
✅ Shows customer info where available
✅ Shows NULL when customer record is missing

---

4. FULL OUTER JOIN → All Records

Returns all rows from both tables, whether they match or not.

SELECT customers.name, orders.order_date, orders.total
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

✅ Gets all customers and all orders
✅ Shows matches where they exist
✅ Shows NULL values where no match occurs

---

5. CROSS JOIN → All Combinations

Returns the Cartesian product of both tables—every row from the first table is paired with every row from the second.

Example: Pair every customer with every order

SELECT customers.name, orders.order_date, orders.total
FROM customers
CROSS JOIN orders;

✅ Useful for generating all possible combinations
✅ Can produce large result sets quickly
✅ No JOIN condition needed

---

🎯 JOIN Examples with Sample Data

Let’s say we have two tables:

customers table:

id	name	email
1	Alice	alice@email.com
2	Bob	bob@email.com
3	Charlie	charlie@email.com

orders table:

id	customer_id	total	order_date
101	1	50.00	2023-01-15
102	1	30.00	2023-02-10
103	2	75.00	2023-03-22

INNER JOIN Result:

name	total	order_date
Alice	50.00	2023-01-15
Alice	30.00	2023-02-10
Bob	75.00	2023-03-22

(No Charlie because no orders)

LEFT JOIN Result:

name	total	order_date
Alice	50.00	2023-01-15
Alice	30.00	2023-02-10
Bob	75.00	2023-03-22
Charlie	NULL	NULL

---

🔄 Multiple JOINs

You can connect more than two tables:

SELECT customers.name, orders.total, products.name
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN products ON orders.product_id = products.id;

---

⚠️ Common JOIN Mistakes to Avoid

1. Forgetting the JOIN condition

   -- ❌ This creates a Cartesian product (bad!)
   SELECT * FROM table1 JOIN table2;

2. Using wrong column names

   -- ❌ Wrong columns
   SELECT * FROM customers JOIN orders ON customers.id = orders.id;

3. Not considering NULL values

   -- ✅ Handle NULL properly
   SELECT customers.name, COALESCE(orders.total, 0)
   FROM customers
   LEFT JOIN orders ON customers.id = orders.customer_id;

---

🎯 When to Use Each JOIN Type

JOIN Type	Use When	Example
INNER JOIN	Need only matching records	Customers who placed orders
LEFT JOIN	Need all records from first table	All customers, with order data if available
RIGHT JOIN	Need all records from second table	All orders, with customer data if available
FULL OUTER JOIN	Need all records from both tables	Complete data audit

---

🛠️ Practical Use Cases

Case 1: Customer Order Analysis

SELECT
    customers.name,
    COUNT(orders.id) AS order_count,
    SUM(orders.total) AS total_spent
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name
ORDER BY total_spent DESC;

Case 2: Inventory Report

SELECT
    products.name,
    categories.category_name,
    suppliers.company_name
FROM products
JOIN categories ON products.category_id = categories.id
LEFT JOIN suppliers ON products.supplier_id = suppliers.id;

---

📌 Quick Summary

• JOINs connect tables using matching column values
• INNER JOIN → Only matching records from both tables
• LEFT JOIN → All records from left table, matched data from right
• RIGHT JOIN → All records from right table, matched data from left
• FULL OUTER JOIN → All records from both tables
• CROSS JOIN → All combinations of rows from both tables
• Always include proper JOIN conditions to avoid performance issues