Home / SQL / Reference

SQL reference: keywords, clauses and functions

Every SQL keyword and function you will use, explained in plain English, each with an example query and its real result. Differences between SQLite, PostgreSQL and MySQL are noted where they matter.

Querying

  • SELECT Choose which columns (or calculations) a query returns.
  • FROM Say which table (or tables, or subquery) the rows come from.
  • AS (aliases) Give a column or a table a new name for this query.
  • DISTINCT Remove duplicate rows from the result.
  • ORDER BY Sort the result.
  • LIMIT and OFFSET Return only the first n rows (after skipping m).

Filtering

  • WHERE Keep only the rows for which a condition is true.
  • AND, OR, NOT Combine conditions.
  • BETWEEN Test whether a value is in a range, both ends included.
  • IN Test whether a value equals any value in a list.
  • LIKE Match text against a pattern.
  • IS NULL Test for a missing (NULL) value.

Numbers and NULL

  • CASE If/else logic that returns a value.
  • COALESCE Return the first value that is not NULL.
  • NULLIF Return NULL if a equals b, otherwise a.
  • CAST Convert a value to another type.
  • ROUND Round a number to a given number of decimal places.
  • ABS Return the absolute value (remove the minus sign).

Text functions

  • UPPER and LOWER Change text to capitals or small letters.
  • LENGTH Return the number of characters in a text value.
  • SUBSTR Take part of a text value.
  • INSTR Return the position of some text inside another, or 0.
  • TRIM Remove spaces (or given characters) from the ends of text.
  • REPLACE Replace every occurrence of some text.
  • || and CONCAT Join text values together.

Dates

Aggregating

  • COUNT Count rows, non-NULL values, or different values.
  • SUM Add up values.
  • AVG Return the average (mean) of values.
  • MIN and MAX Return the smallest and largest value.
  • GROUP BY Split rows into groups and aggregate each group.
  • HAVING Filter groups after aggregation.
  • GROUP_CONCAT Join the values of a group into one text value.

Joins

  • INNER JOIN Combine rows of two tables that match on a condition.
  • LEFT JOIN Keep every row of the left table, matched or not.
  • Self join Join a table to itself, with two aliases.
  • CROSS JOIN Pair every row of one table with every row of another.

Subqueries, CTEs and sets

Window functions

Tables and design

Changing data