Skip to main content

Edit this page

SELECT

Query data from one or more tables.

Syntax​

Parameters​

ParameterDescription
WITHDefine common table expressions (CTEs) for use in the query
DISTINCTRemove duplicate rows from the result
DISTINCT ON (expr, ...)Keep only the first row for each distinct set of expressions
ALLKeep all rows including duplicates (default)
expressionColumn name, literal, function call, arithmetic, or subquery
aliasAlternative name for a column or table
table_refTable name, subquery, or table function, optionally with JOINs
WHERE expressionFilter rows before aggregation
GROUP BYGroup rows by one or more expressions
HAVING expressionFilter groups after aggregation
WINDOWDefine named window specifications for use in window functions
ORDER BYSort results. ASC (default) or DESC. NULLS FIRST or NULLS LAST
LIMIT expressionMaximum number of rows to return
FETCH FIRST expression ROWS ONLYSQL-standard alternative to LIMIT
OFFSET expressionNumber of rows to skip
UNIONCombine results from two queries, removing duplicates
INTERSECTReturn rows common to both queries
EXCEPTReturn rows in the first query but not the second
FILTER (WHERE ...)Filter rows fed to an aggregate function

Aliases​

Use AS (or omit it) to name columns and tables:

SELECT a.title AS article_title, COUNT(*) count
FROM articles a
WHERE a.views > 100;

Table references​

The FROM clause accepts:

  • Table name — FROM articles or FROM articles a
  • Subquery — FROM (SELECT ...) sub
  • Table function — FROM generate_series(1, 10) or user-defined table functions
  • JOINs — combine any of the above

Joins​

-- INNER JOIN (default)
SELECT a.title, c.body
FROM articles a
JOIN comments c ON a.id = c.article_id;

-- LEFT JOIN
SELECT a.title, c.body
FROM articles a
LEFT JOIN comments c ON a.id = c.article_id;

-- CROSS JOIN
SELECT a.title, t.tag
FROM articles a
CROSS JOIN tags t;

-- USING shorthand
SELECT a.title, c.body
FROM articles a
JOIN comments c USING (article_id);

WHERE​

Filter rows using comparison operators, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL:

SELECT * FROM articles WHERE category = 'engineering' AND views > 1000;

ORDER BY​

SELECT title, views FROM articles ORDER BY views DESC NULLS LAST;

LIMIT / OFFSET​

SELECT title FROM articles ORDER BY views DESC LIMIT 10 OFFSET 20;

-- SQL-standard alternative
SELECT title FROM articles ORDER BY views DESC FETCH FIRST 10 ROWS ONLY;

DISTINCT ON​

Return only the first row for each distinct value of the given expressions:

SELECT DISTINCT ON (category) category, title, views
FROM articles
ORDER BY category, views DESC;

WITH​

Define common table expressions (CTEs):

WITH top_articles AS (
    SELECT title, views, category
    FROM articles
    WHERE views > 5000
)
SELECT category, COUNT(*) AS count
FROM top_articles
GROUP BY category;
note

Recursive CTEs (WITH RECURSIVE) are not yet supported.

Window​

Define named windows for reuse across multiple window functions:

SELECT title, views,
    RANK() OVER w AS rank,
    SUM(views) OVER w AS running_total
FROM articles
WINDOW w AS (ORDER BY views DESC);

Inline window specifications also work:

SELECT title, category, views,
    RANK() OVER (PARTITION BY category ORDER BY views DESC) AS rank
FROM articles;

FILTER​

Restrict which rows are fed to an aggregate function:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE views > 1000) AS popular
FROM articles;

Expressions​

Expressions can be column references, literals, operators, function calls, CASE, CAST, or subqueries:

SELECT
    title,
    CASE WHEN views > 1000 THEN 'popular' ELSE 'niche' END AS status,
    CAST(views AS DOUBLE PRECISION) / total_views AS share
FROM articles;

Examples​

Aggregation​

SELECT category, COUNT(*) AS count, AVG(views) AS avg_views
FROM articles
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_views DESC;

Subquery in FROM​

SELECT avg_views, category
FROM (
    SELECT category, AVG(views) AS avg_views
    FROM articles
    GROUP BY category
) sub
WHERE avg_views > 1000;

Set operations​

SELECT title FROM articles_2024
UNION ALL
SELECT title FROM articles_2025;

See also​