Skip to main content

​
Single Table with Alias

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

​
Multiple Tables (Implicit Cross Join)

Comma-separated tables produce a cartesian product. Use a WHERE predicate to filter to matching rows: 
SELECT o.id, c.name
FROM myschema.orders AS o, myschema.customers AS c
WHERE o.customer_id = c.id

​
Derived Table (Subquery in FROM)

A subquery in the FROM clause must have an alias: 
SELECT sub.status, COUNT(*) AS cnt
FROM (
  SELECT status FROM myschema.orders WHERE total > 100
) AS sub
GROUP BY sub.status

​
CTE References

Tables defined in a WITH clause can be referenced in FROM like any other table: 
WITH big_orders AS (
  SELECT * FROM myschema.orders WHERE total > 500
)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id
See Subqueries and CTEs for full CTE syntax.

​
VALUES Constructor

Use VALUES to produce an inline table: 
SELECT v.status_code, v.label
FROM (VALUES ('pending', 'Awaiting'), ('shipped', 'On the way')) AS v(status_code, label)

​
INNER JOIN

Join on an equality or arbitrary condition: 
-- 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)

​
Outer Joins

-- 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

​
Join Chains (3+ Tables)

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

​
Non-Equi Join Predicates

Join conditions are not limited to equality: 
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

​
Join with Derived Table

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

​
Not Supported

  • CROSS JOIN keyword syntax (use comma syntax instead)
  • NATURAL JOIN
  • LATERAL table references