Querying Data (SELECT)

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

This page covers the standard SQL SELECT statement: clause order and evaluation, filtering rows, grouping and aggregation, and combining rows from multiple tables with joins. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines SELECT.

The SELECT statement and clause order

A SELECT statement is written with clauses in a fixed syntactic order, but the database evaluates them in a different logical order: first FROM (and any joins), then WHERE, then GROUP BY, then HAVING, then the SELECT list itself, and finally ORDER BY. Understanding this evaluation order explains, for example, why a column alias defined in SELECT cannot generally be used in WHERE, but can be used in ORDER BY.

Clause Purpose

Clause	Purpose
`SELECT`	Chooses which columns (or expressions) to return, optionally renamed with `AS`.
`FROM`	Names the source table(s), optionally joined together (see Table joins).
`WHERE`	Filters individual rows before any grouping occurs (see WHERE predicates).
`GROUP BY`	Collapses the filtered rows into groups that share the same values for the listed expressions.
`HAVING`	Filters entire groups, typically using aggregate functions (see GROUP BY and HAVING).
`ORDER BY`	Sorts the final result set, ascending (`ASC`, the default) or descending (`DESC`).

SELECT

Chooses which columns (or expressions) to return, optionally renamed with AS.

FROM

Names the source table(s), optionally joined together (see Table joins).

WHERE

Filters individual rows before any grouping occurs (see WHERE predicates).

GROUP BY

Collapses the filtered rows into groups that share the same values for the listed expressions.

HAVING

Filters entire groups, typically using aggregate functions (see GROUP BY and HAVING).

ORDER BY

Sorts the final result set, ascending (ASC, the default) or descending (DESC).

The example below uses every clause in the standard order, listing, for each department with more than one employee, the average salary of employees hired since 2015:

SELECT department_id,
       COUNT(*)         AS employee_count,
       AVG(salary)      AS average_salary
FROM employees
WHERE hire_date >= DATE '2015-01-01'
GROUP BY department_id
HAVING COUNT(*) > 1
ORDER BY average_salary DESC;

To limit the number of rows returned, standard SQL (SQL:2008 and later) provides OFFSET and FETCH FIRST … ROWS ONLY, evaluated after ORDER BY:

SELECT department_id, average_salary
FROM department_salaries
ORDER BY average_salary DESC
OFFSET 10 ROWS
FETCH FIRST 5 ROWS ONLY;

OFFSET skips the given number of rows before starting to return results, and FETCH FIRST n ROWS ONLY (also written FETCH NEXT n ROWS ONLY) caps how many rows follow. Both are optional and may be used independently. Many DBMS products additionally support a non-standard, shorter LIMIT/OFFSET syntax; that syntax is a vendor extension and is intentionally not documented here.

Both forms still cost the engine a full scan-and-discard of every skipped row before the requested page can be returned — OFFSET/FETCH FIRST does not index "the Nth row," it counts rows off from the start of the ordered result set and throws away everything before the offset. On a deep page this dominates the query’s cost. The seek method (also called keyset pagination) avoids that by remembering the last-seen key from the previous page and filtering with WHERE key > :lastSeenKey instead of skipping rows — an index seek instead of a scan:

SELECT department_id, average_salary
FROM department_salaries
WHERE average_salary < :lastSeenAverage
ORDER BY average_salary DESC
FETCH FIRST 5 ROWS ONLY;

See Pagination: Offset vs. Keyset for why this matters and how the same pattern applies across MongoDB, Couchbase, Solr, Elasticsearch, GraphQL and Spring Data.

DISTINCT

DISTINCT removes duplicate rows from the result set, comparing all selected columns together. Its complement, ALL, keeps every row including duplicates and is the implicit default when neither keyword is written.

SELECT DISTINCT department_id, job_title
FROM employees;

DISTINCT may also qualify a single argument inside an aggregate function, so the aggregate only considers each distinct value once:

SELECT COUNT(DISTINCT department_id) AS department_count
FROM employees;

GROUP BY and HAVING

GROUP BY groups rows that share the same value(s) for one or more expressions, so that aggregate functions (COUNT, SUM, AVG, MIN, MAX, and others) can be computed per group instead of over the whole table. Every column in the SELECT list that is not itself an aggregate must appear in GROUP BY.

HAVING filters the groups produced by GROUP BY, in the same way WHERE filters individual rows — the key difference is that HAVING runs after grouping and so can reference aggregate functions, while WHERE cannot.

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 500000;

GROUP BY also accepts multiple expressions, producing one group per unique combination of their values:

SELECT department_id, job_title, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id, job_title
HAVING AVG(salary) > 60000
ORDER BY department_id, job_title;

WHERE predicates

The WHERE clause keeps only the rows for which its search condition evaluates to true. Standard SQL provides several predicate forms, which can be combined with the boolean operators AND, OR, and NOT.

Comparison operators

The standard comparison operators are =, <> (or != in some dialects, though <> is the SQL-standard form), <, >, ⇐, and >=.

SELECT employee_id, first_name, salary
FROM employees
WHERE salary >= 50000;

BETWEEN

BETWEEN low AND high tests whether a value falls within an inclusive range; NOT BETWEEN tests the opposite.

SELECT employee_id, hire_date
FROM employees
WHERE hire_date BETWEEN DATE '2020-01-01' AND DATE '2020-12-31';

IN

IN (value, value, …) tests whether a value matches any member of a list (or the result of a subquery); NOT IN tests the opposite.

SELECT employee_id, department_id
FROM employees
WHERE department_id IN (10, 20, 30);

LIKE

LIKE performs pattern matching against character strings using two wildcards: % matches any sequence of zero or more characters, and _ matches exactly one character. NOT LIKE negates the match.

SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE 'Mc%';

When the literal characters % or _ must be matched instead of treated as wildcards, ESCAPE designates an escape character that neutralizes the wildcard immediately following it:

SELECT product_id, product_code
FROM products
WHERE product_code LIKE '50\%%' ESCAPE '\';

The example above matches any product_code that literally starts with 50%, because the backslash before % escapes it, while the trailing unescaped % still acts as a wildcard for the remainder of the string.

IS NULL and IS NOT NULL

Because NULL represents an unknown or missing value, it cannot be tested with = or <>; IS NULL and IS NOT NULL are the standard predicates for that purpose.

SELECT employee_id, manager_id
FROM employees
WHERE manager_id IS NULL;

Boolean combinators: AND, OR, NOT

AND requires both conditions to be true, OR requires at least one to be true, and NOT negates a condition. Parentheses control evaluation order, since AND binds more tightly than OR.

SELECT employee_id, department_id, salary
FROM employees
WHERE (department_id = 10 OR department_id = 20)
  AND NOT salary < 40000;

Table joins

Joins combine rows from two or more tables based on a related condition. The condition may be written as an explicit ON predicate, or — when the joined tables share identically-named columns — as a USING clause naming those columns.

INNER JOIN

INNER JOIN (or simply JOIN) returns only the rows for which the join condition matches in both tables.

SELECT e.employee_id, e.last_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d
  ON e.department_id = d.department_id;

The equivalent join written with USING, when both tables name the join column identically:

SELECT e.employee_id, e.last_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d
  USING (department_id);

LEFT OUTER JOIN

LEFT OUTER JOIN (or LEFT JOIN) returns every row from the left table, matched with rows from the right table where the condition matches, and NULL for the right table’s columns where it does not.

SELECT e.employee_id, e.last_name, d.department_name
FROM employees AS e
LEFT OUTER JOIN departments AS d
  ON e.department_id = d.department_id;

RIGHT OUTER JOIN

RIGHT OUTER JOIN (or RIGHT JOIN) is the mirror image of LEFT OUTER JOIN: every row from the right table is returned, matched with rows from the left table where possible, and NULL for the left table’s columns otherwise.

SELECT e.employee_id, e.last_name, d.department_name
FROM employees AS e
RIGHT OUTER JOIN departments AS d
  ON e.department_id = d.department_id;

FULL OUTER JOIN

FULL OUTER JOIN (or FULL JOIN) returns every row from both tables, matching where the condition is satisfied and filling in NULL for the columns of whichever side has no match.

SELECT e.employee_id, e.last_name, d.department_name
FROM employees AS e
FULL OUTER JOIN departments AS d
  ON e.department_id = d.department_id;

CROSS JOIN

CROSS JOIN returns the Cartesian product of the two tables — every row of the first table combined with every row of the second — and takes no ON or USING clause.

SELECT s.size_label, c.color_name
FROM sizes AS s
CROSS JOIN colors AS c;

Aggregate and window functions in queries

Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize multiple rows into a single value per group, as used above with GROUP BY and HAVING. Window functions are a related but distinct mechanism: introduced with the OVER clause, they compute a value across a "window" of related rows without collapsing them into a single output row. A window’s rows are typically divided into partitions with PARTITION BY, and ordered within each partition with an ORDER BY written inside the OVER clause.

SELECT employee_id,
       department_id,
       salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

This page only introduces the syntax as it appears inside a SELECT statement. For the full function-by- function reference — including every standard aggregate function, the complete set of window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and others), and the standard frame clauses (ROWS/RANGE BETWEEN) — see Aggregate and Window Functions.