Pricing
Log in Get started
Docs Learn
Home
Courses
Metabase Basics
Grow your data skills
Cheat sheets

SQL syntax reference

How does that SQL keyword work again?

Learn SQL with Metabase

Download 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;