Querying with SELECT — Databases & SQL

TL;DR

SELECT chooses the columns, FROM the table, WHERE the rows, ORDER BY the order and LIMIT how many rows come back.
AND binds more tightly than OR; use parentheses whenever you combine them.
Without ORDER BY the order of the result is not guaranteed, and LIMIT then returns arbitrary rows.
DISTINCT removes duplicate result rows, comparing all selected columns together.
A query is evaluated as FROM, WHERE, SELECT, ORDER BY, LIMIT, which is why standard SQL does not let you use a column alias in WHERE.

SELECT and FROM

Reading data is the most common thing you will do with a database, and it always uses one statement. A SELECT statement asks the database for a result table: you list the columns you want after SELECT and the table they come from after FROM. A * means "all columns":

SELECT *
FROM products
ORDER BY product_id;

product_id  name                      category     price  stock
----------  ------------------------  -----------  -----  -----
1           USB-C cable 2 m           Cables       9      120
2           HDMI cable 3 m            Cables       12     80
3           Wireless mouse            Peripherals  25     45
4           Mechanical keyboard       Peripherals  89     12
5           27-inch monitor           Displays     249    7
6           Laptop stand              Accessories  35     30
7           USB-C hub                 Accessories  45     25
8           Noise-cancelling headset  Audio        129    9
9           Webcam 1080p              Peripherals  59     0
10          Ethernet cable 5 m        Cables       7      200

(ORDER BY sorts the result; it is explained below and used in every example so that the order is predictable.) Usually you name the columns you need, in the order you want them:

SELECT first_name, last_name, country
FROM customers
ORDER BY customer_id;

first_name  last_name  country
----------  ---------  -------
Sanne       de Vries   NL
Kwame       Mensah     NL
Mei         Chen       NL
Omar        Haddad     BE
Lena        Fischer    DE
Priya       Patel      NL
Tom         Jansen     NL
Ana         Silva      PT

A few rules of SQL's syntax: keywords are not case-sensitive (select works too, capitals are a readability convention), line breaks and indentation are free, a statement ends with a semicolon, and -- starts a comment that runs to the end of the line.

Expressions and column aliases

A SELECT list can contain calculations as well as columns. The database evaluates the expression for every row. To give the result a readable name, add a column alias with AS; without one, the column would be called price * stock. Here is the value of the stock of each product:

SELECT name, price, stock, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC, name;

name                      price  stock  stock_value
------------------------  -----  -----  -----------
27-inch monitor           249    7      1743
Ethernet cable 5 m        7      200    1400
Noise-cancelling headset  129    9      1161
USB-C hub                 45     25     1125
Wireless mouse            25     45     1125
USB-C cable 2 m           9      120    1080
Mechanical keyboard       89     12     1068
Laptop stand              35     30     1050
HDMI cable 3 m            12     80     960
Webcam 1080p              59     0      0

The alias exists only in the result; the table is not changed. The word AS is optional in most products (price * stock stock_value also works), but writing it makes the intention clear. An alias that contains spaces needs double quotes: AS "stock value". Functions for text, numbers and dates are covered in Filtering, NULL and Functions.

Filtering rows with WHERE

The WHERE clause keeps only the rows for which a condition is true. The database tests the condition on each row of the table; rows that fail it are left out of the result.

Operator	Meaning	Example
`=`	Equal to (one `=`, not two)	`country = 'NL'`
`<>` or `!=`	Not equal to (`<>` is the standard form)	`status <> 'delivered'`
`<`, `>`	Less than, greater than	`price < 20`
`<=`, `>=`	Less or equal, greater or equal	`signup_date >= '2026-03-01'`

SELECT name, price
FROM products
WHERE price < 20
ORDER BY price;

SELECT first_name, signup_date
FROM customers
WHERE signup_date >= '2026-03-01'
ORDER BY signup_date;

SELECT order_id, status
FROM orders
WHERE status <> 'delivered'
ORDER BY order_id;

name                price
------------------  -----
Ethernet cable 5 m  7
USB-C cable 2 m     9
HDMI cable 3 m      12

first_name  signup_date
----------  -----------
Priya       2026-03-05
Tom         2026-04-11
Ana         2026-05-30

order_id  status
--------  ---------
104       shipped
105       cancelled
107       shipped
108       pending
110       pending

Text values go between single quotes. Double quotes are for names of columns and tables, so WHERE status = "shipped" means something different in standard SQL (and fails in PostgreSQL). Comparing text with = is case-sensitive in SQLite and PostgreSQL: 'cables' does not match 'Cables'. MySQL and SQL Server usually compare case-insensitively, depending on the column's collation (its rules for comparing and sorting text). The date comparison works because the shop stores dates as ISO 8601 text (YYYY-MM-DD), which sorts in date order.

Combining conditions: AND, OR and NOT

AND requires both conditions to be true, OR at least one, and NOT reverses a condition. They follow the rules of Boolean logic, with one twist for missing values that is covered in Three-valued logic.

SELECT first_name, city
FROM customers
WHERE NOT country = 'NL'
ORDER BY customer_id;

first_name  city
----------  -------
Omar        Antwerp
Lena        Cologne
Ana         Lisbon

The precedence trap

Suppose the warehouse asks for "cables and peripherals with fewer than 50 in stock". The obvious translation gives the wrong answer:

-- Wrong: AND is evaluated before OR
SELECT name, category, stock
FROM products
WHERE category = 'Cables' OR category = 'Peripherals' AND stock < 50
ORDER BY product_id;

name                 category     stock
-------------------  -----------  -----
USB-C cable 2 m      Cables       120
HDMI cable 3 m       Cables       80
Wireless mouse       Peripherals  45
Mechanical keyboard  Peripherals  12
Webcam 1080p         Peripherals  0
Ethernet cable 5 m   Cables       200

Cables with 120 and 200 in stock? AND has a higher precedence than OR, just as multiplication comes before addition, so the database read the condition as category = 'Cables' OR (category = 'Peripherals' AND stock < 50): all cables, plus peripherals with low stock. Parentheses say what was meant:

SELECT name, category, stock
FROM products
WHERE (category = 'Cables' OR category = 'Peripherals') AND stock < 50
ORDER BY product_id;

name                 category     stock
-------------------  -----------  -----
Wireless mouse       Peripherals  45
Mechanical keyboard  Peripherals  12
Webcam 1080p         Peripherals  0

Sorting with ORDER BY

A table has no built-in order, and neither does a query result: without an explicit sort, the database returns rows in whatever order is fastest, which can change when data or indexes change. ORDER BY sorts the result: ASC (ascending, the default) or DESC (descending). With several columns, the second decides only between rows that are equal in the first:

SELECT category, name, price
FROM products
ORDER BY category, price DESC;

category     name                      price
-----------  ------------------------  -----
Accessories  USB-C hub                 45
Accessories  Laptop stand              35
Audio        Noise-cancelling headset  129
Cables       HDMI cable 3 m            12
Cables       USB-C cable 2 m           9
Cables       Ethernet cable 5 m        7
Displays     27-inch monitor           249
Peripherals  Mechanical keyboard       89
Peripherals  Webcam 1080p              59
Peripherals  Wireless mouse            25

DESC applies only to the column right before it: categories are sorted A to Z, prices within each category from high to low. You can sort by a column alias, as the stock value example above did with ORDER BY stock_value DESC, name; the second column there breaks the tie between the two products worth 1125.

LIMIT and OFFSET

LIMIT n returns at most n rows; OFFSET m skips the first m rows first. Together with ORDER BY they answer "top 3" questions and split long lists into pages:

-- The three most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

-- The next three ("page 2")
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3;

name                      price
------------------------  -----
27-inch monitor           249
Noise-cancelling headset  129
Mechanical keyboard       89

name          price
------------  -----
Webcam 1080p  59
USB-C hub     45
Laptop stand  35

LIMIT and OFFSET work in SQLite, PostgreSQL and MySQL. SQL Server uses TOP, or the standard OFFSET … FETCH clause (which requires an ORDER BY):

-- SQL Server
SELECT TOP (3) name, price
FROM products
ORDER BY price DESC;

-- SQL Server, and standard SQL (PostgreSQL, Oracle 12c and later, Db2)
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

The standard also allows FETCH FIRST 3 ROWS ONLY without an offset; PostgreSQL and Oracle accept it, SQL Server needs the OFFSET 0 ROWS part.

Removing duplicates with DISTINCT

A query returns one result row per table row, so the same value can appear many times. DISTINCT, written right after SELECT, removes duplicate result rows:

SELECT country
FROM customers
ORDER BY country;

SELECT DISTINCT country
FROM customers
ORDER BY country;

country
-------
BE
DE
NL
NL
NL
NL
NL
PT

country
-------
BE
DE
NL
PT

DISTINCT applies to the whole result row, not to the first column only. With two columns, each unique combination appears once. Missing values count as equal to each other here, so the one customer without a city gives one NULL row:

SELECT DISTINCT city, country
FROM customers
ORDER BY country, city;

city       country
---------  -------
Antwerp    BE
Cologne    DE
NULL       NL
Amsterdam  NL
Rotterdam  NL
Utrecht    NL
Lisbon     PT

To count how many customers each country has, rather than just listing the countries, use GROUP BY (see Aggregation and Grouping).

The logical order of evaluation

SQL is written in one order and evaluated, logically, in another. Knowing the second order explains many error messages:

Step	Clause	What happens
1	`FROM`	Take the rows of the table (or joined tables)
2	`WHERE`	Keep the rows for which the condition is true
3	`GROUP BY`, `HAVING`	Form and filter groups, if the query has them (see Aggregation and Grouping)
4	`SELECT`	Compute the result columns and their aliases; then `DISTINCT` removes duplicates
5	`ORDER BY`	Sort the result (aliases exist now)
6	`LIMIT` / `OFFSET`	Cut out the requested rows

The database may execute the query differently internally (it can use an index to avoid sorting, for instance), but the result is always as if it followed these steps.

Why an alias does not belong in WHERE

WHERE (step 2) runs before SELECT (step 4) has created the alias, so in standard SQL a column alias cannot be used in WHERE, only in ORDER BY. Repeat the expression instead. This version works in every product:

SELECT name, price * stock AS stock_value
FROM products
WHERE price * stock > 1150
ORDER BY stock_value DESC;

name                      stock_value
------------------------  -----------
27-inch monitor           1743
Ethernet cable 5 m        1400
Noise-cancelling headset  1161

Writing WHERE stock_value > 1150 fails in PostgreSQL (column "stock_value" does not exist), in SQL Server (Invalid column name 'stock_value') and in MySQL (Unknown column 'stock_value' in 'where clause'). SQLite is the exception: as a non-standard extension it looks up an unknown name in WHERE among the result column aliases, so this runs and returns the same three rows:

-- Works in SQLite only
SELECT name, price * stock AS stock_value
FROM products
WHERE stock_value > 1150
ORDER BY stock_value DESC;

name                      stock_value
------------------------  -----------
27-inch monitor           1743
Ethernet cable 5 m        1400
Noise-cancelling headset  1161

Do not rely on it. Apart from not being portable, it has a trap: when an alias has the same name as a real column, SQLite's WHERE uses the column. Below, the result column called price holds double the price, but the filter still tests the original price (249 and 129 are over 100; the keyboard's 89 would double to 178, yet it is not included):

SELECT name, price * 2 AS price
FROM products
WHERE price > 100
ORDER BY name;

name                      price
------------------------  -----
27-inch monitor           498
Noise-cancelling headset  258