Day 5: Connecting Tables with JOINS

Join Type	What it Returns	Common Use Case
INNER JOIN	ONLY rows with a match in BOTH tables.	"Show me customers who have actually placed an order."
LEFT JOIN	ALL rows from the Left table, and matching rows from the Right.	"Show me ALL customers, and their orders if they have any."
RIGHT JOIN	ALL rows from the Right table, and matching rows from the Left.	"Show me ALL orders, and link the customer data to them."
FULL OUTER JOIN	ALL rows from BOTH tables, regardless of matches.	"Show me everything from both tables, matched up where possible."
CROSS JOIN	Every row from Left combined with Every row from Right.	"Generate all possible combinations of Size and Color."

1. What is a JOIN?

Because relational databases eliminate redundancy by splitting data into multiple tables (e.g., storing Customers in one table and Orders in another), we need a way to bring them back together. JOIN clauses allow us to link tables horizontally using their related columns (Primary Keys and Foreign Keys).

Syntax Tip: We often use Table Aliases (like `C` for Customers and `O` for Orders) to save typing and make queries cleaner!

2. INNER JOIN

The INNER JOIN is the default and most common join. It acts like an intersection—it only returns records where the link exists in both tables.

-- Find details of orders and the customers who placed them
SELECT C.FirstName, C.LastName, O.OrderDate, O.TotalAmount
FROM Customers C
INNER JOIN Orders O 
    ON C.CustomerID = O.CustomerID;

3. OUTER JOINS (Left, Right, Full)

Outer joins are used when you want to keep records even if there is no match in the other table. The missing data will simply be filled in with NULL values.

LEFT OUTER JOIN (or LEFT JOIN)

Keeps everything from the "Left" table (the one mentioned first/top), regardless of whether it found a match in the second table.

-- Shows ALL customers. If they haven't ordered, OrderDate will be NULL.
SELECT C.FirstName, O.OrderDate
FROM Customers C
LEFT JOIN Orders O 
    ON C.CustomerID = O.CustomerID;

RIGHT OUTER JOIN (or RIGHT JOIN)

The exact opposite of a Left Join. It keeps everything from the "Right" table (the one mentioned second/bottom).

SELECT C.FirstName, O.OrderDate
FROM Customers C
RIGHT JOIN Orders O 
    ON C.CustomerID = O.CustomerID;

FULL OUTER JOIN

Combines Left and Right joins. It keeps everything from both tables. If a match isn't found, NULLs are placed on the side that is missing data.

SELECT C.FirstName, O.OrderDate
FROM Customers C
FULL OUTER JOIN Orders O 
    ON C.CustomerID = O.CustomerID;

4. CROSS JOIN

A CROSS JOIN produces a "Cartesian Product." It takes row 1 from Table A and pairs it with every row in Table B. Then it takes row 2 from Table A and does the same. Notice it does NOT use an ON clause.

-- If you have 3 Shirt Sizes and 4 Colors, this generates 12 rows of combinations.
SELECT S.SizeName, C.ColorName
FROM Sizes S
CROSS JOIN Colors C;

5. Advanced Join: The SELF JOIN

A Self Join is a regular join, but the table is joined to itself. This is used when a table contains hierarchical data. For example, an Employee table where managers are also employees.

-- Match an employee to their manager (who is in the same table!)
SELECT 
    Emp.FirstName AS EmployeeName, 
    Mgr.FirstName AS ManagerName
FROM Employees Emp
LEFT JOIN Employees Mgr 
    ON Emp.ManagerID = Mgr.EmployeeID;