SQL syntax reference
How does that SQL keyword work again?
Learn SQL with MetabaseDownload Metabase for free, or sign up for a free trial of Metabase Cloud
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;
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 COUNT
Counts the number of rows.
SELECT
COUNT(*)
FROM
orders;
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 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.
SELECT
*
FROM
products
WHERE
category IN ('Gizmo', 'Widget');
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;
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;
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;
SQL LIKE
Filters rows by pattern matching.
SELECT
*
FROM
products
WHERE
title LIKE '%Wool%';
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;
