-- Created on savesnippets.com · https://savesnippets.com/nwfGOZ5f4X2NYo
-- Each order with its user's email
SELECT o.id          AS order_id,
       o.amount,
       o.created_at,
       u.email
FROM   orders o
INNER  JOIN users u ON u.id = o.user_id
WHERE  o.amount > 100
ORDER  BY o.created_at DESC;

-- Multi-table join — orders + users + products
SELECT o.id          AS order_id,
       u.email,
       p.name        AS product,
       oi.qty,
       oi.unit_price
FROM   orders o
JOIN   users u           ON u.id = o.user_id
JOIN   order_items oi    ON oi.order_id = o.id
JOIN   products p        ON p.id = oi.product_id
WHERE  o.created_at >= NOW() - INTERVAL '7 days';

-- USING() — shorthand when both columns have the same name
SELECT u.name, o.id
FROM   users u
JOIN   orders o USING (user_id);