Self joins and Multi-table queries

You know RIGHT, FULL OUTER, and CROSS joins. Now meet the self-join, where a table is joined to itself, and see how to chain multiple JOINs to build complete queries across the entire schema.

A self-join joins a table to itself. This is great for comparing rows within the same table, such as finding sellers in the same city.

self_join.sql
sql
-- Self-join concept: compare rows within the same table
-- Find sellers who are in the same city
SELECT
  s1.seller_id AS seller_1,
  s2.seller_id AS seller_2,
  s1.seller_city
FROM sellers s1
JOIN sellers s2
  ON s1.seller_city = s2.seller_city
  AND s1.seller_id < s2.seller_id  -- avoid duplicates and self-pairs
LIMIT 20;

A self-join joins a table to itself using different aliases. The trick is using < (not !=) to avoid duplicate pairs.

Matching exercise: Match the join type

Loading practice…

multi_table_join.sql
sql
-- 4-table join: the full order picture
SELECT
  o.order_id,
  c.customer_city,
  c.customer_state,
  p.product_category_name,
  oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY oi.price DESC
LIMIT 10;

Chain multiple JOINs to connect any tables in the schema. Just follow the foreign key relationships.

Quiz: Quiz

Loading practice…

Code playground: Revenue by customer state

Loading practice…