SQL syntax reference
How does that SQL keyword work again?
SQL ALL
Checks if all values in a subquery meet a condition.
WITH widget_prices AS (
SELECT price
FROM products
WHERE category = 'Widget'
)
SELECT
title,
price,
category
FROM
products
WHERE
price > ALL (SELECT price FROM widget_prices);
SQL AND
Filters rows where multiple conditions are true.
SELECT
*
FROM
products
WHERE
category = 'Gizmo'
AND price > 50;
SQL ANY
Checks if any value in a subquery meets a condition. ANY and SOME are the same thing.
SELECT
title,
price
FROM
products
WHERE
price > ANY (
SELECT price
FROM products
WHERE category = 'Widget'
);
SQL ARRAY
Lets you work with arrays of values. (Syntax varies by database, but here’s a Postgres-style example.)
SELECT ARRAY[price, 100, 200] AS price_array
FROM products
LIMIT 1;
SQL AVG
Calculates the average value of a numeric column.
SELECT
AVG(price)
FROM
products;
SQL AS
Creates an alias for a column in the results.
SELECT
title AS "Product Name",
category AS "Product Category"
FROM
products;
SQL BETWEEN
Checks if a value is within a range (inclusive).
SELECT
*
FROM
products
WHERE
price BETWEEN 10 AND 20;
See also SQL filtering by date.
SQL CASE
Returns values based on conditions, like an if-else.
SELECT
title,
CASE
WHEN price > 100 THEN 'Expensive'
ELSE 'Affordable'
END AS price_category
FROM
products;
SQL CAST
Converts a value from one data type to another.
CAST(24.99 AS VARCHAR) AS price_text,
CAST('2024-01-01' AS DATE) AS order_date
See also SQL filtering by date.
SQL COUNT
Counts the number of rows.
SELECT
COUNT(*)
FROM
orders;
SQL CURRENT_DATE
Returns the current date from the system.
SELECT
CURRENT_DATE AS today,
CURRENT_DATE - INTERVAL '1' DAY AS yesterday,
CURRENT_DATE + INTERVAL '1' DAY AS tomorrow;
See INTERVAL and SQL filtering by date.
SQL DATE
Creates a date value from a string or extracts the date part from a timestamp.
-- Create a date from a string
SELECT DATE '2024-05-04' AS specific_date;
Most databases will infer ‘2025-05-04` as a date, but you might as well be explicit.
See also SQL filtering by date.
SQL CURRENT_TIME
Returns the current time from the system.
SELECT
CURRENT_TIME AS right_now,
CURRENT_TIME + INTERVAL '30' MINUTE AS thirty_mins_later;
See INTERVAL and SQL filtering by date.
SQL DISTINCT
Returns only unique values.
SELECT
DISTINCT category
FROM
products;
SQL EXCEPT
Returns rows from the first query that aren’t in the second query.
SELECT
id
FROM
people
EXCEPT
SELECT
user_id
FROM
orders;
SQL EXISTS
Checks if a subquery returns any rows.
SELECT
name
FROM
people
WHERE
EXISTS (
SELECT 1
FROM orders
WHERE orders.user_id = people.id
);
SQL EXTRACT
Extracts a specific part (year, month, day, etc.) from a date or timestamp.
SELECT
created_at as "timestamp",
EXTRACT(YEAR FROM created_at) AS "year",
EXTRACT(MONTH FROM created_at) AS "month",
EXTRACT(DAY FROM created_at) AS "day",
EXTRACT(HOUR FROM created_at) AS "hour",
EXTRACT(DOW FROM created_at) AS "day of week"
FROM
orders;
Which would yield:
| timestamp | year | month | day | hour | day of week |
| -------------------------- | ----- | ----- | --- | ---- | ----------- |
| February 11, 2025, 9:40 PM | 2,025 | 2 | 11 | 21 | 3 |
| May 15, 2024, 8:04 AM | 2,024 | 5 | 15 | 8 | 4 |
| December 6, 2025, 10:22 PM | 2,025 | 12 | 6 | 22 | 7 |
| August 22, 2025, 4:30 PM | 2,025 | 8 | 22 | 16 | 6 |
| October 10, 2024, 3:34 AM | 2,024 | 10 | 10 | 3 | 5 |
| November 6, 2025, 4:38 PM | 2,025 | 11 | 6 | 16 | 5 |
See also SQL filtering by date.
SQL FROM
Specifies which table to query.
SELECT
*
FROM
products;
SQL GROUP BY
Groups rows that have the same values in specified columns.
SELECT
category,
COUNT(*)
FROM
products
GROUP BY
category;
SQL HAVING
Filters groups after aggregation (used with GROUP BY).
SELECT
category,
COUNT(*)
FROM
products
GROUP BY
category
HAVING
COUNT(*) > 2;
SQL IN
Checks if a value matches any value in a list or subquery.
Using a list of values:
SELECT
*
FROM
products
WHERE
category IN ('Gizmo', 'Widget');
Using a subquery:
SELECT
*
FROM
orders
WHERE
user_id IN (
SELECT id
FROM people
WHERE state = 'VT'
);
SQL INTERVAL
Specifies a time period for date/time calculations. Common units include years, months, weeks, days, hours, minutes, and seconds.
You can use INTERVAL to add days:
SELECT
created_at,
-- Add 7 days to a date
created_at + INTERVAL '7' DAY AS next_week
FROM
orders;
Subtract months:
SELECT
created_at,
-- Subtract 2 months from a date
created_at - INTERVAL '2' MONTH AS two_months_ago
FROM
orders;
SQL datetime units include:
YEARMONTHDAYHOURMINUTE-SECOND
See also SQL filtering by date.
SQL INNER JOIN
Returns rows when there is a match in both tables.
SELECT
orders.id,
products.title
FROM
orders
INNER JOIN products ON orders.product_id = products.id;
See also Combining tables with SQL joins.
SQL INTERSECT
Returns rows that show up in both queries.
SELECT
id
FROM
people
INTERSECT
SELECT
user_id
FROM
orders;
SQL IS NULL
Checks for missing (null) values.
SELECT
*
FROM
products
WHERE
vendor IS NULL;
SQL JOIN
Combines rows from two or more tables, based on a related column.
SELECT
orders.id,
products.title
FROM
orders
JOIN products ON orders.product_id = products.id;
See also Combining tables with SQL joins.
SQL LEFT JOIN
Returns all rows from the left table, and matched rows from the right table.
SELECT
people.name,
orders.id
FROM
people
LEFT JOIN orders ON people.id = orders.user_id;
See also Combining tables with SQL joins.
SQL LIKE
Filters rows by pattern matching.
SELECT
*
FROM
products
WHERE
title LIKE '%Wool%';
See also SQL filtering by text.
SQL LIMIT
Restricts the number of rows returned.
SELECT
*
FROM
products
LIMIT
3;
SQL MAX
Returns the maximum value in a column.
SELECT
MAX(price)
FROM
products;
SQL MIN
Returns the minimum value in a column.
SELECT
MIN(price)
FROM
products;
SQL MOD
Math function that returns the remainder of a division. Can be used to sample rows with randomly distributed IDs.
SELECT
*
FROM
products
WHERE
MOD(id, 10) = 3;
SQL NOT
Negates a condition.
SELECT
*
FROM
products
WHERE
NOT category = 'Gizmo';
SQL NULL
Represents missing or unknown data.
SELECT
*
FROM
products
WHERE
vendor IS NULL;
SQL ON
Specifies the join condition between tables.
SELECT
orders.id,
products.title
FROM
orders
JOIN products ON orders.product_id = products.id;
SQL OR
Filters rows where at least one condition is true.
SELECT
*
FROM
products
WHERE
category = 'Gizmo'
OR price > 100;
SQL ORDER BY
Sorts the result set by one or more columns.
SELECT
title,
price
FROM
products
ORDER BY
price DESC;
SQL RIGHT JOIN
Returns all rows from the right table, and matched rows from the left table.
SELECT
orders.id,
people.name
FROM
orders
RIGHT JOIN people ON orders.user_id = people.id;
SQL SELECT
Specifies which columns to return from a table.
-- Get all columns
SELECT
*
FROM
products;
-- Get specific columns
SELECT
title,
category
FROM
products;
SQL SOME
Same as ANY - checks if any value in a subquery meets a condition.
SELECT
title,
price
FROM
products
WHERE
price > SOME (
SELECT price
FROM products
WHERE category = 'Widget'
);
SQL SUM
Adds up values in a column.
SELECT
SUM(price)
FROM
products;
SQL UNION
Combines the results of two queries (removes duplicates).
SELECT
title
FROM
products
UNION
SELECT
name
FROM
people;
SQL UNION ALL
Combines the results of two queries and keeps all the rows, even duplicates. So if the same value shows up in both queries, you’ll see it twice.
SELECT
title
FROM
products
UNION ALL
SELECT
name
FROM
people;
SQL WHERE
Filters rows based on specified conditions.
SELECT
*
FROM
products
WHERE
MOD(id, 10) = 3;
SQL WITH
Defines a Common Table Expression (CTE) you can use in your query. It’s like a temporary result set.
WITH expensive_products AS (
SELECT *
FROM products
WHERE price > 100
)
SELECT title FROM expensive_products;