PostgreSQL Joins

Types of Joins

SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id

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

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

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.id = table2.id

🔹 INNER JOIN

INNER JOIN returns only the rows where there is a match in both tables based on the join condition. This is the most common type of join and is used when you only want records that have corresponding entries in both tables. Rows without matches are excluded from the result set, making it perfect for finding related data.

-- Get customers and their orders
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

🔹 LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned for the right table columns. This is useful when you want to see all records from the primary table regardless of whether they have related records in the secondary table, such as all customers even those without orders.

-- Get all customers and their orders (including customers without orders)
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

🔹 RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN returns all rows from the right table and matching rows from the left table. If there's no match, NULL values are returned for the left table columns. This is the opposite of LEFT JOIN and is less commonly used, but useful when you want to ensure all records from the secondary table are included in your results regardless of matches.

-- Get all orders and their customers (including orders without customer info)
SELECT 
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

🔹 FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, with NULL values where there's no match. This combines the results of both LEFT and RIGHT joins, showing all records from both tables regardless of whether they have matching records. It's useful for finding all records and identifying which ones have or don't have relationships between tables.

-- Get all customers and orders, including unmatched records
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

🔹 CROSS JOIN

CROSS JOIN returns the Cartesian product of both tables, meaning every row from the first table is combined with every row from the second table. This creates a result set with rows equal to the product of the row counts of both tables. Use this carefully as it can create very large result sets, but it's useful for generating combinations or test data.

-- Create all possible combinations of products and colors
SELECT 
    p.product_name,
    c.color_name
FROM products p
CROSS JOIN colors c;

🔹 SELF JOIN

A SELF JOIN is when a table is joined with itself. This is useful for comparing rows within the same table or finding hierarchical relationships like employee-manager structures. You must use table aliases to distinguish between the two instances of the same table in the query, making it clear which role each instance plays in the join.

-- Find employees and their managers
SELECT 
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

🔹 Multiple Table Joins

You can join more than two tables in a single query by chaining multiple JOIN clauses. Each join connects two tables based on their relationship, allowing you to combine data from complex database schemas. This is essential for real-world applications where data is normalized across multiple related tables, enabling comprehensive data retrieval in one query.

-- Join customers, orders, and products
SELECT 
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;