SQL: understand SELECT, FROM and WHERE

A SQL query begins with a question

A database often contains much more information than you need. SQL allows you to precisely describe the expected result without manually going through each line.

Let's imagine a table produits containing an identifier, a name, a category and a price. We want to answer a simple question: which IT products cost less than 100 TND?

SELECT chooses the result columns

The SELECT clause defines what the result should display. Naming the columns makes the query more readable than systematically using the asterisk.

The order of the columns in SELECT becomes the order of the returned table.

• A comma separates the columns.
• SELECT reads data; he does not modify them.
• An alias with AS can improve the displayed title.

SELECT produit, prix
FROM produits;

FROM indicates the table consulted

FROM produits tells the database where to find the requested columns. In a query with multiple tables, FROM and JOIN will together describe the sources.

A table or column name error normally causes an explicit message. This message is a help: read it before rewriting the entire query.

WHERE filters lines

WHERE compares each row to a condition. Only rows for which the condition is true are kept.

Texts use apostrophes, while numbers are compared like numbers. With AND, both conditions must be true.

SELECT produit, prix
FROM produits
WHERE categorie = 'IT'
  AND prix < 100
ORDER BY prix ASC;

The logical order to memorize

To begin, think of the sentence: choose with SELECT, search in FROM, filter with WHERE, then sort with ORDER BY.

The database processes certain operations in a different internal order, but this writing order is sufficient to produce simple and correct queries.

SELECT colonnes
FROM table
WHERE condition
ORDER BY colonne ASC;

Four common mistakes

The first mistakes rarely come from a complicated concept. They mainly come from punctuation and names.

• Forget a comma between two columns.
• Use a column name that does not exist.
• Write => instead of >=.
• Compare text without apostrophes.

Get started immediately

Reread the query word by word, then try modifying it: change the price threshold, add a column, or reverse the sort.

The free exercise on columns now allows you to check that SELECT and FROM are correctly understood.