The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Select columnsSELECT id, name FROM customers;View examples
Rename an output columnSELECT total AS order_total FROM orders;View examples
Remove duplicate rowsSELECT DISTINCT country FROM customers;View examples
Filter by comparisonSELECT * FROM orders WHERE total >= 100;View examples
Require every conditionSELECT * FROM orders WHERE status = 'paid' AND total >= 100;View examples
Accept either conditionSELECT * FROM orders WHERE status = 'new' OR status = 'processing';View examples
Match a value listSELECT * FROM orders WHERE status IN ('new', 'processing');View examples
Match an inclusive rangeSELECT * FROM orders WHERE total BETWEEN 50 AND 100;View examples
Find missing valuesSELECT * FROM customers WHERE phone IS NULL;View examples
Match a text patternSELECT * FROM customers WHERE name LIKE 'A%';View examples
Sort the resultSELECT * FROM orders ORDER BY created_at DESC, id ASC;View examples
Limit returned rowsSELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;View examples
Skip result rowsSELECT * FROM orders ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;View examples
Count rowsSELECT COUNT(*) AS order_count FROM orders;View examples
Sum a columnSELECT SUM(total) AS revenue FROM orders WHERE status = 'paid';View examples
Aggregate by groupSELECT status, COUNT(*) FROM orders GROUP BY status;View examples
Filter grouped resultsSELECT customer_id, SUM(total) FROM orders GROUP BY customer_id HAVING SUM(total) >= 500;View examples
Join matching rowsSELECT o.id, c.name FROM orders AS o INNER JOIN customers AS c ON c.id = o.customer_id;View examples
Keep every left rowSELECT c.name, o.id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id;View examples
Test for related rowsSELECT * FROM customers AS c WHERE EXISTS ( SELECT 1 FROM orders AS o WHERE o.customer_id = c.id );View examples
Name an intermediate queryWITH paid_orders AS ( SELECT * FROM orders WHERE status = 'paid' ) SELECT * FROM paid_orders;View examples

Build SELECT queries in stages: choose source tables, filter rows, group when needed, calculate the output columns, and finally sort or limit the result. Explicit columns and deterministic ordering make queries easier to understand and safer to maintain.

Step by step

Detailed examples

01

Choose and name result columns

List the columns the caller actually needs instead of relying on SELECT *. Aliases make calculated fields easier to consume, while DISTINCT removes duplicate combinations only after the selected expressions have been calculated.

Customer locations with readable headings
SELECT DISTINCT
  country AS customer_country,
  city AS customer_city
FROM customers
ORDER BY customer_country, customer_city;

Note: DISTINCT applies to the complete selected row, so two cities with the same name in different countries remain separate combinations.

Calculate an order total
SELECT
  id,
  subtotal,
  tax,
  subtotal + tax AS order_total
FROM orders;
Back to quick reference ↑
02

Filter rows with explicit conditions

WHERE removes rows before grouping and output calculation. Combine conditions with parentheses when AND and OR appear together, use IS NULL instead of equality for missing values, and remember that BETWEEN includes both boundaries.

Find active orders in a total range
SELECT id, customer_id, status, total
FROM orders
WHERE status IN ('new', 'processing')
  AND total BETWEEN 50 AND 100
ORDER BY id;
Find customers who need contact cleanup
SELECT id, name, email, phone
FROM customers
WHERE (name LIKE 'A%' OR name LIKE 'B%')
  AND (email IS NULL OR phone IS NULL);

Note: LIKE case sensitivity varies by database and collation. PostgreSQL also provides ILIKE for case-insensitive matching, but ILIKE is not portable SQL.

Filter by a direct comparison
SELECT id, total
FROM orders
WHERE total >= 100;
Back to quick reference ↑
03

Sort before limiting or paging

A database does not guarantee row order without ORDER BY. Add a unique tie-breaker for stable results, then apply FETCH and OFFSET. Large offsets can become slow or unstable while data changes, so keyset pagination is often better for deep application pages.

Return the newest ten orders
SELECT id, customer_id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
FETCH FIRST 10 ROWS ONLY;
Return the third ten-row page
SELECT id, customer_id, created_at, total
FROM orders
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Note: FETCH is standard SQL and supported by PostgreSQL. Some databases use LIMIT, TOP, or different pagination syntax.

Back to quick reference ↑
04

Summarize rows with aggregate functions

Aggregate functions calculate one value from many rows. GROUP BY produces one result row per key combination, WHERE filters source rows before aggregation, and HAVING filters the completed groups afterward.

Count orders and total paid revenue
SELECT
  COUNT(*) AS order_count,
  SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid';

Note: COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL.

Find customers with substantial paid totals
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) >= 500
ORDER BY paid_total DESC;
Count orders by status
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY status;
Back to quick reference ↑
05

Join related tables with explicit keys

INNER JOIN returns only matching pairs. LEFT JOIN preserves every row from its left input and uses NULL for missing right-side values. Keep relationship predicates in ON so the intended join behavior remains visible.

List orders with their customers
SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.total
FROM orders AS o
INNER JOIN customers AS c
  ON c.id = o.customer_id
ORDER BY o.id;
Include customers without orders
SELECT
  c.id AS customer_id,
  c.name,
  COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Note: COUNT(o.id) returns zero for customers without orders, whereas COUNT(*) would count the preserved customer row.

Back to quick reference ↑
06

Compose queries with EXISTS and CTEs

EXISTS expresses a Boolean relationship test and can stop after finding a qualifying row. A common table expression introduced by WITH gives an intermediate result a name, which can make multi-stage queries easier to review.

Find customers with paid orders
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id
    AND o.status = 'paid'
)
ORDER BY c.name;
Reuse a filtered order set
WITH paid_orders AS (
  SELECT customer_id, total
  FROM orders
  WHERE status = 'paid'
)
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total) AS paid_total
FROM paid_orders
GROUP BY customer_id
ORDER BY paid_total DESC;

Note: A CTE improves structure but is not automatically faster than an equivalent subquery. Inspect the database execution plan for performance decisions.

Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupSELECT — Retrieve Rows from a Table or Viewpostgresql.org
  2. PostgreSQL Global Development GroupJoins Between Tablespostgresql.org
  3. PostgreSQL Global Development GroupAggregate Functionspostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback