FROM and JOINs

SELECT o.id, o.total FROM myschema.orders AS o

SELECT "o"."order-id", "o"."Safety.Warning"
FROM east."prod-orders" AS "o"

SELECT o.id, c.name
FROM myschema.orders AS o, myschema.customers AS c
WHERE o.customer_id = c.id

SELECT sub.status, COUNT(*) AS cnt
FROM (
  SELECT status FROM myschema.orders WHERE total > 100
) AS sub
GROUP BY sub.status

WITH big_orders AS (
  SELECT * FROM myschema.orders WHERE total > 500
)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id

SELECT v.status_code, v.label
FROM (VALUES ('pending', 'Awaiting'), ('shipped', 'On the way')) AS v(status_code, label)

-- ON syntax
SELECT o.id, c.name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id

-- USING syntax (columns must share the same name)
SELECT o.id, c.name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c USING (customer_id)

-- USING also accepts quoted identifiers
SELECT l.value, r.value
FROM left_data AS l
INNER JOIN right_data AS r USING ("order-id")

-- LEFT OUTER JOIN: all rows from the left table; NULL for unmatched right columns
SELECT c.name, o.id
FROM myschema.customers AS c
LEFT OUTER JOIN myschema.orders AS o ON c.id = o.customer_id

-- RIGHT OUTER JOIN
SELECT c.name, o.id
FROM myschema.orders AS o
RIGHT OUTER JOIN myschema.customers AS c ON o.customer_id = c.id

-- FULL OUTER JOIN
SELECT c.name, o.id
FROM myschema.customers AS c
FULL OUTER JOIN myschema.orders AS o ON c.id = o.customer_id

SELECT o.id, c.name, p.title
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id
INNER JOIN myschema.products AS p ON o.product_id = p.id

SELECT e.name, s.tier
FROM myschema.employees AS e
INNER JOIN myschema.salary_bands AS s ON e.salary >= s.min_salary AND e.salary < s.max_salary

SELECT c.name, recent.total
FROM myschema.customers AS c
INNER JOIN (
  SELECT customer_id, MAX(total) AS total
  FROM myschema.orders
  GROUP BY customer_id
) AS recent ON c.id = recent.customer_id