SQL querying tables

Learn SQL with Metabase

Download Metabase for free, or sign up for a free trial of Metabase Cloud

What we’ll cover

• Use SELECT to specify the columns you want to get ›
• * selects all columns ›
• Use FROM to specify the table or subquery you want to query ›
• When not aggregating your tables, LIMIT rows when developing your queries (so you don’t nuke your database) ›
• Add comments with --; they won’t affect the results, but will help people understand your code ›
• End each query with a ; ›
• Change the names of columns with AS ›
• Sort results with ORDER BY, either ASC for ascending or DESC for descending ›
• Select specific columns using a comma-separated list ›
• Understand SQL casing conventions and when case matters ›
• Sample data from tables using the MOD function ›

SQL to get all the rows from a table

If you want to get the whole table and all its rows, like this products table:

| id  | ean           | title                     | category  | vendor                       | price | rating | created_at                  |
| --- | ------------- | ------------------------- | --------- | ---------------------------- | ----- | ------ | --------------------------- |
| 1   | 1018947080336 | Rustic Paper Wallet       | Gizmo     | Swaniawski, Casper and Hilll | 29.46 | 4.6    | July 19, 2017, 7:44 PM      |
| 2   | 7663515285824 | Small Marble Shoes        | Doohickey | Balistreri-Ankunding         | 70.08 | 0      | April 11, 2019, 8:49 AM     |
| 3   | 4966277046676 | Synergistic Granite Chair | Doohickey | Murray, Watsica and Wunsch   | 35.39 | 4      | September 8, 2018, 10:03 PM |

You SELECT columns FROM the table, like so:

SELECT
  *
FROM
  products;

• SELECT is a SQL reserved word that specifies the columns to return. Reserved words are keywords in SQL that have special meanings for the database engines that process the queries.
• * is the wildcard operator that tells the database to return all columns from a table. In general, you should only include the exact columns you want in the results. Do not try this with really large tables without limiting your results, unless you want to make everyone else’s day worse.
• FROM is a SQL reserved word that tells the database which table (or tables) to look for the columns. Table names must be exact (but casing doesn’t matter).
• Note the ; at the end of the query. Metabase is pretty forgiving about whether you include the semi-colon, but other editors/databases may not be. They’ll look for the semi-colon to know the query is complete.

The order of operations your database engine steps through when processing your query is NOT the same as the order of operations as one reads through the code. In general, you don’t have to think about this, as people have put in an unbelievable number of hours optimizing these query engines.

SQL to add comments to your code

You can (and should) add comments to a query with --. Here’s that same query, with explanatory comments:

-- Our first query
SELECT
  * -- The star means to get all columns
FROM
  products; -- The table to query

The database’s query processor knows to ignore anything on a line after --. In fact, you could put an entire sailboat in your query and the results wouldn’t change:

SELECT
  *
FROM
-- An entire sailboat
  products;

SQL to only get some rows from the table

You can LIMIT the results, like so:

SELECT
  *
FROM
  products
LIMIT -- SQL reserved word
  3; -- Only get the first 3 rows

Which will return all columns (*), but only 3 rows. Where possible, limit rows as you’re developing queries to speed up loading times. Fortunately, Metabase automatically limits results to the first 2,000 rows so people don’t blow up their database. But it’s still good practice.

Limit only affects the number of rows returned. If you’re aggregating data, like counting rows and grouping rows (which we’ll cover later), the database will still count and group all the rows, but only return the number of result rows set by LIMIT.

SQL to only get some columns

To return specific columns, like only the title and category columns:

| title                     | category  |
| ------------------------- | --------- |
| Rustic Paper Wallet       | Gizmo     |
| Small Marble Shoes        | Doohickey |
| Synergistic Granite Chair | Doohickey |

SELECT each column, in the order you want the columns displayed in the results. Separate columns with a comma (but don’t add a comma at the end of the list), like so:

SELECT
  title, -- Names of each column must be exact
  category -- No comma at the end of list
FROM
  products;

In fact, so many people have been burned by the missing comma error that some people format their SQL like this just to make the commas easier to see (but more so to make the columns easier to comment out as needed):

SELECT
  title
  ,category -- Comma precedes each column except the first column
  ,vendor -- No comma at the end of list
FROM
  products;

SQL to sort results

You can sort the rows of results with ORDER BY. For example, to sort products by price from lowest to highest:

SELECT
  title,
  price
FROM
  products
ORDER BY     -- Sorts results
  price ASC; -- By the price column in ascending order

Which returns:

| Title                    | Price |
| ------------------------ | ----- |
| Mediocre Paper Car       | 15.69 |
| Rustic Paper Car         | 19.87 |
| Heavy-Duty Copper Gloves | 20.41 |
| Enormous Marble Shoes    | 21.42 |

The ASC keyword is sometimes optional (depending on the database engine), but including ASC in the ORDER BY clause makes the code easier to read, and it’ll always work.

To sort in descending order (highest to lowest), add the DESC keyword:

SELECT
  title,
  price
FROM
  products
ORDER BY
  price DESC; -- Sorts in descending order

To sort by multiple columns, for example, first by category (alphabetically), then by price within each category (highest to lowest):

SELECT
  title,
  category,
  price
FROM
  products
ORDER BY
  category ASC, -- Sorts categories alphabetically (note the comma)
  price DESC; -- Then sorts prices highest to lowest within each category

You might sometimes see people ordering by numbers, like this:

SELECT
  title,
  category,
  price
FROM
  products
ORDER BY
  2 ASC, -- Sorts categories alphabetically (note the comma)
  3 DESC; -- Then sorts prices highest to lowest within each category

Here the numbers correspond with the column’s ordinal position in the SELECT statement. With some databases, ordering by position is the only way to order by aliased columns. But if possible, it’s better to include the column names or aliases so people don’t have to bounce back and forth between the SELECT and ORDER BY sections when reading the code.

SQL to change the names of the columns

Say your table has column names like title and category, but you want each column heading to start with “Product”, like this:

| Product title             | Product category |
| ------------------------- | ---------------- |
| Rustic Paper Wallet       | Gizmo            |
| Small Marble Shoes        | Doohickey        |
| Synergistic Granite Chair | Doohickey        |

To change the name of a column (in the results, not the table itself), use AS to create an alias for the column, like so:

SELECT
  title AS "Product title", -- Note the double quotes, single quotes won't work
  category AS "Product category"
FROM
  products;

SQL aliases are temporary names for values in a query. Aliases don’t have any effect on the underlying database; aliases only exist in the query itself. You typically use aliases to:

• Improve readability.
• Name column expressions (e.g., CONCAT(first_name, ' ', last_name) AS "Full name").
• Name common table expressions (see CTEs).
• Self-join tables.

SQL casing usually doesn’t matter, except for aliases

Using all caps for SQL’s reserved words is a convention that predates syntax highlighting, but letter case doesn’t matter for reserved words or column names. This query works too:

SeLeCt
  *
FrOM
  PROdUCTS;

But, you know: don’t. Write queries that people (including you six months from now) can read. So, all-caps or not (SELECT or select), be consistent.

Casing does apply to aliases, however, so make sure aliases match case letter for letter (with aliases, "Example" is not the same as "example").

SQL to get a sampling of rows

This will only work if your table has an ID row with a random distribution. You can use the MOD function (short for modulo) to sample data based on IDs:

SELECT
  id,
  title,
  category
FROM
  products
WHERE
  -- For this to work, the table's ids must be sequential integers
  MOD(id, 10) = 3;

Which returns:

| id  | title                         | category   |
|-----|-------------------------------|------------|
| 3   | Synergistic Granite Chair     | Doohickey  |
| 13  | Synergistic Steel Chair       | Gizmo      |
| 23  | Fantastic Aluminum Bottle     | Widget     |

And more rows, with each tenth row having an ID ending in 3. How this works: If the ID were 23, for example, MOD(23, 10) would return 3, as 23 divided by 10 leaves a remainder of 3, so the 23rd row would be included in the results. That’s only dividing the results by 10, so if the table has a ton of rows, you’d want to increase the divisor’s magnitude: 100, 1000, etc.

This tip is more of a whet-your-appetite-for-what’s-possible-in-SQL kind of thing. We’ll cover SQL functions (like MOD) in depth later on.