Lessons › SQL › Deep Dive: Advanced Joins & Subqueries

SQL lesson

Self join basics

A self join treats a single table as if it were two separate tables, letting you compare rows within that table against each other.

Example

Comparing Products with a Self Join

SELECT p1.product_id AS product_a,
       p2.product_id AS product_b,
       p1.brand,
       p1.price AS price_a,
       p2.price AS price_b
FROM products p1
JOIN products p2
  ON p1.brand = p2.brand
WHERE p1.product_id < p2.product_id

Result:

Step by step

1. AS gives a column a friendlier name in the result.

   SELECT p1.product_id AS product_a, p2.product_id AS product_b, p1.brand, p1.price AS price_a, p2.price AS price_b

2. FROM tells SQL which table to pull the data from.

   SELECT p1.product_id AS product_a, p2.product_id AS product_b, p1.brand, p1.price AS price_a, p2.price AS price_b
   FROM products p1

3. JOIN combines rows from two tables that share a key.

   SELECT p1.product_id AS product_a, p2.product_id AS product_b, p1.brand, p1.price AS price_a, p2.price AS price_b
   FROM products p1
   JOIN products p2 ON p1.brand = p2.brand

Practice

Try each one in your head first, then open the answer.

SELECT * FROM orders t1 INNER JOIN orders t2 ON t1.order_id = t2.order_id

SELECT * FROM orders t1 INNER JOIN orders t2 ON t1.order_id = t2.order_id

SELECT * FROM orders t1 INNER JOIN orders t2 ON t1.order_id = t2.order_id

SELECT * FROM products t1 INNER JOIN products t2 ON t1.product_id = t2.product_id

SELECT * FROM products t1 INNER JOIN products t2 ON t1.product_id = t2.product_id