Working with Subqueries

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country='US');

SELECT s.customer_id, s.total
  FROM (SELECT customer_id, sum(total_cents) AS total
          FROM orders GROUP BY customer_id) s
 WHERE s.total > 100000;

SELECT id, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
  FROM customers c;

SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items);

SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL);

SELECT * FROM orders WHERE total_cents > ANY (SELECT min_high FROM tiers);
-- '= ANY (...)' is equivalent to IN

SELECT * FROM products WHERE price >= ALL (SELECT price FROM products);

SELECT c.id, recent.*
  FROM customers c
  LEFT JOIN LATERAL (
      SELECT id, placed_at FROM orders
       WHERE customer_id = c.id ORDER BY placed_at DESC LIMIT 3
  ) recent ON true;