Multi-Table Joins

Tutorial 4 of 5

This tutorial builds a small e-commerce schema and walks through every join type mskql supports: INNER, LEFT, RIGHT, FULL, CROSS, and NATURAL. You will also use correlated subqueries, EXISTS, and IN with subqueries.

1. Schema and seed data

CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    city  TEXT
);

CREATE TABLE products (
    id     SERIAL PRIMARY KEY,
    name   TEXT NOT NULL,
    price  INT NOT NULL
);

CREATE TABLE orders (
    id           SERIAL PRIMARY KEY,
    customer_id  INT REFERENCES customers(id),
    ordered_at   DATE NOT NULL
);

CREATE TABLE order_items (
    id         SERIAL PRIMARY KEY,
    order_id   INT NOT NULL REFERENCES orders(id),
    product_id INT NOT NULL REFERENCES products(id),
    qty        INT NOT NULL DEFAULT 1
);

INSERT INTO customers (name, city) VALUES
    ('Alice',  'Portland'),
    ('Bob',    'Seattle'),
    ('Carol',  'Portland'),
    ('Dave',   NULL),
    ('Eve',    'Denver');

INSERT INTO products (name, price) VALUES
    ('Widget',   25),
    ('Gadget',   50),
    ('Sprocket', 15),
    ('Gizmo',    75);

INSERT INTO orders (customer_id, ordered_at) VALUES
    (1, '2025-01-05'),
    (1, '2025-01-18'),
    (2, '2025-01-10'),
    (3, '2025-01-12');

INSERT INTO order_items (order_id, product_id, qty) VALUES
    (1, 1, 3),
    (1, 2, 1),
    (2, 3, 5),
    (3, 1, 2),
    (3, 4, 1),
    (4, 2, 2);

2. INNER JOIN — order details

The classic join: only rows that match on both sides.

SELECT c.name AS customer,
       o.ordered_at,
       p.name AS product,
       oi.qty,
       p.price * oi.qty AS total
FROM customers c
JOIN orders o
  ON c.id = o.customer_id
JOIN order_items oi
  ON o.id = oi.order_id
JOIN products p
  ON oi.product_id = p.id
ORDER BY o.ordered_at, p.name;

3. LEFT JOIN — customers who never ordered

A LEFT JOIN keeps all customers, even those with no orders. The unmatched rows get NULLs on the right side.

SELECT c.name,
       c.city,
       COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
GROUP BY c.name, c.city
ORDER BY orders, c.name;

4. FULL OUTER JOIN

Show all customers and all products, even if there is no link between them. This uses a FULL JOIN through the order tables.

SELECT
  COALESCE(c.name, '(none)')
    AS customer,
  COALESCE(p.name, '(none)')
    AS product
FROM customers c
FULL JOIN orders o
  ON c.id = o.customer_id
FULL JOIN order_items oi
  ON o.id = oi.order_id
FULL JOIN products p
  ON oi.product_id = p.id
WHERE c.name IS NULL
   OR p.name IS NULL
ORDER BY c.name, p.name;

Dave and Eve have no orders, so they appear with no product. Every product was ordered at least once, so no product appears without a customer.

5. CROSS JOIN — all combinations

A Cartesian product of two small tables. Useful for generating test data or price matrices.

SELECT c.name AS customer,
       p.name AS product
FROM customers c
CROSS JOIN products p
WHERE c.city = 'Portland'
  AND p.price <= 25
ORDER BY c.name, p.name;

6. EXISTS — customers with high-value orders

Use a correlated subquery with EXISTS to find customers who have at least one order item totalling more than $60.

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items oi
    ON o.id = oi.order_id
  JOIN products p
    ON oi.product_id = p.id
  WHERE o.customer_id = c.id
    AND p.price * oi.qty > 60
)
ORDER BY c.name;

7. IN with a subquery

Find products that have never been ordered.

SELECT p.name, p.price
FROM products p
WHERE p.id NOT IN (
  SELECT oi.product_id
  FROM order_items oi
);

All four products appear in order_items, so the result is empty. This is a useful pattern for finding orphaned or unused records.

8. Correlated subquery in SELECT

Compute each customer’s total spend using a scalar subquery in the SELECT list.

SELECT c.name,
  (SELECT SUM(p.price * oi.qty)
   FROM orders o
   JOIN order_items oi
     ON o.id = oi.order_id
   JOIN products p
     ON oi.product_id = p.id
   WHERE o.customer_id = c.id
  ) AS total_spend
FROM customers c
ORDER BY total_spend DESC;

What you learned

• INNER JOIN for matching rows across multiple tables
• LEFT JOIN to preserve all rows from the left table
• FULL OUTER JOIN to keep unmatched rows from both sides
• CROSS JOIN for Cartesian products
• EXISTS with correlated subqueries for existence checks
• NOT IN (SELECT ...) for anti-joins
• Scalar subqueries in the SELECT list for per-row aggregation