10. Joins · Data Engineering

Joins

Joins combine rows from related tables. A normalized database stores facts in separate tables, and joins bring those facts together for analysis.

Sample tables

sql

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10, 2),
  status VARCHAR(20)
);

INNER JOIN

INNER JOIN returns only rows that match in both tables.

sql

SELECT
  o.order_id,
  c.first_name,
  c.last_name,
  o.total_amount
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id;

Use it when you only want orders that have a matching customer.

LEFT JOIN

LEFT JOIN keeps every row from the left table, even when there is no match on the right.

sql

SELECT
  c.customer_id,
  c.first_name,
  o.order_id,
  o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id;

This is useful for finding customers who have not ordered yet.

sql

SELECT
  c.customer_id,
  c.first_name,
  c.last_name
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

RIGHT JOIN

RIGHT JOIN keeps every row from the right table. It is less common because most queries can be rewritten as a LEFT JOIN.

sql

SELECT
  c.customer_id,
  c.first_name,
  o.order_id
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.customer_id = o.customer_id;

Joining more than two tables

sql

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id INT,
  product_name VARCHAR(100),
  quantity INT,
  unit_price DECIMAL(10, 2)
);

SELECT
  c.first_name,
  o.order_id,
  oi.product_name,
  oi.quantity,
  oi.unit_price
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
  ON o.order_id = oi.order_id;

Self join

A self join joins a table to itself. For example, employees can reference their manager in the same table.

sql

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  full_name VARCHAR(100),
  manager_id INT
);

SELECT
  e.full_name AS employee,
  m.full_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.employee_id;

Table aliases are essential in self joins because the same table appears twice.