SQL Joins

A join combines rows from two or more tables based on a related column between them - typically a foreign key linking to a primary key. Joins are essential because relational databases intentionally split data across multiple tables to avoid duplication.

Why Joins Are Needed

Recall from the Relational Model that a customers table and an orders table are linked through customer_id. To see a customer's name alongside their orders, you need to join the two tables together rather than duplicating customer details into every order row.

Basic Join Syntax

SELECT c.first_name, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Types of Joins

• Inner Join - only rows that match in both tables
• Left Join - all rows from the left table, matched rows from the right
• Right Join - all rows from the right table, matched rows from the left
• Full Outer Join - all rows from both tables, matched where possible
• Cross Join - every combination of rows from both tables
• Self Join - a table joined with itself