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;