SQL Joins

Why joins exist

Relational databases deliberately spread information across several tables so that each fact is stored only once. Customer details live in one table, and the orders those customers place live in another. This keeps the data tidy and avoids repeating the same customer name on every order row. A join is the mechanism that stitches these separated tables back together at query time, matching rows from one table to rows in another through a shared value such as a customer id.

Throughout this topic we use two small tables. The Customers table describes each customer, and the Orders table records purchases. Every order stores a CustomerID that points back to the customer who placed it. That shared column is what makes the join possible.

Notice two mismatches on purpose. Customer 3 and 4 have no orders, and order 104 references CustomerID 5, which does not exist in Customers. Different join types treat these unmatched rows differently, which is exactly what the rest of this topic explores.

The main types of join

• INNER JOIN: keeps only rows that have a match in both tables.
• LEFT JOIN: keeps every row from the left table, adding matching right-table data where it exists.
• RIGHT JOIN: keeps every row from the right table, adding matching left-table data where it exists.
• FULL JOIN: keeps every row from both tables, matching them where possible.
• SELF JOIN: joins a table to itself to compare rows within the same table.

SELECT Orders.OrderID, Customers.CustomerName, Orders.Amount
FROM Orders
INNER JOIN Customers
  ON Orders.CustomerID = Customers.CustomerID;