SELECT and FROM basics

Concepts covered: Query Basics

A query is a question written for the database, and the smallest useful one has 2 parts. FROM names the table to read. SELECT lists the columns to bring back, separated by commas. The database finds the table and walks every row, keeping from each one only the columns on the list. What comes back is a new table that lives for the length of the query: the same rows, only the chosen columns.

SELECT
  product_name,
  price
FROM products

Run it and 5 rows come back, 1 per product, each with 2 values: Desk Lamp beside 24.0, Standing Desk beside 349.0, and so on down to USB Hub beside 18.75. The query did not name product_id or category, so they were read off the table and left out before the result was assembled, and in_stock went the same way. The products table is the same afterward as before, because a SELECT reads the table and never edits it, however many times it runs.

Read FROM before SELECT

You write SELECT first and FROM second, and the database works the other way around: it has to locate the table before it can know which columns exist. Read your own queries in the database's order. Start at FROM and picture the table with its column card. Then move up to SELECT and check that every name on the list belongs to that table. A name that does not, such as a misspelled pirce, stops the query with an error saying the column cannot be resolved, before a single row is returned.

The SELECT list sets the column order

SELECT
  price,
  product_name,
  product_id
FROM products

The result's columns appear in the order the SELECT list gives them, so price comes first, even though product_id is the first column in the table. The table's own column order has no hold over the result. A column can also be listed twice, which returns it twice side by side; that reads as a mistake today and becomes useful in the last section of this lesson, where 1 copy is turned into a calculation and the other is kept as it is.

Every row comes back, repeats included

SELECT
  category
FROM products

Naming 1 column still returns 5 rows: lighting, furniture, accessories, furniture, accessories. furniture appears twice because 2 products belong to it, and the database does not collapse repeats on its own. SELECT narrows the columns, never the rows; the result has as many rows as the table until the filtering lesson adds the tool that changes that. A count of rows in a result is therefore a count of rows in the table, which is a quick sanity check when a result looks too short or too long.

A comma separates every pair of columns, and the last column carries no comma before FROM; a stray comma there is a syntax error. The keywords SELECT and FROM can be typed in any mixture of upper and lower case, so select works exactly like SELECT. Upper case is the convention because it makes the skeleton of a long query visible at a glance: keywords stand out, column names stay quiet. A query may end with a semicolon; tools that run several statements in a row use it as a separator, and a single query runs the same with or without it.

What does this query return when run against the products table?

SELECT
  category,
  product_name
FROM products

• 5 rows with 2 columns, product_name first because it comes first in the table
• 5 rows with all 5 columns, because a query always returns the whole row
• 2 rows, 1 for category and 1 for product_name, each listing 5 values
• 5 rows with 2 columns, category first and product_name second

Writing a query from a request follows the same steps every time. Turn each thing the request asks for into a column name from the table's card, spelled exactly as the card spells it. List those names after SELECT in the order the request wants them, with a comma between each pair and none after the last. Put the table's name after FROM. Then read the query back in the database's order: the table first, then each name on the list checked against that table's columns.

About this lesson

This section is part of the Query Structure: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.