Core · DatabasesLesson 1 of 6Free to read

SELECT and WHERE · SQL and Databases

How do you ask a database a question?

Why you need it

Almost every app keeps its data in a database: users, orders, messages. SQL is the language you use to ask that data questions, and it has barely changed in forty years, so what you learn here works in SQLite, MySQL and PostgreSQL alike.

This course uses a tiny shop with three tables: customers, products and orders. Open the table panel next to the editor to see them.

The intuition

A table is a spreadsheet with strict rules: every row is one thing (one customer), every column is one fact about it (name, city).

A SELECT is a sentence: give me these columns from this table where this is true.

SELECT name, city FROM customers WHERE city = 'Shanghai';

The database reads every row, keeps the ones where the condition is true, and hands back only the columns you named.

Definition

SELECT … FROM

Chooses columns from a table. `*` means every column. Each statement ends with a semicolon.

WHERE

Keeps only rows where the condition is true. Text goes in single quotes; combine conditions with AND, OR and NOT.

NULL

A missing value. Nothing is equal to NULL, not even NULL, so you test it with `IS NULL` or `IS NOT NULL`.

Examples

Columns and rows

Run it in the lesson

Pick two columns, then keep only the cheap products.

1
2
3

SELECT name, price
FROM products
WHERE price < 10;

Output

name | price
Notebook | 3.5
Pen | 1.5
Mug | 6.0

Step by step

L1These two columns, in this order.
L2From the products table: 5 rows.
L3Notebook, Pen and Mug cost less than 10.3 rows

AND, OR and NULL

Run it in the lesson

Brackets make the meaning clear. Eli's city is unknown, so city = 'Shanghai' is not true for Eli, and neither is city != 'Shanghai'.

SELECT name FROM customers
WHERE (city = 'Shanghai' OR city = 'Berlin') AND name != 'Bo';

SELECT name FROM customers WHERE city IS NULL;

Output

name
Chen
Dana

name
Eli

Step by step

L2Bo, Chen and Dana match the city test; then Bo is removed.Chen, Dana
L4Only IS NULL finds the missing value.Eli

Common mistakes

Double quotes for text

In standard SQL, text goes in 'single quotes'. "Double quotes" mean a column name, so WHERE city = "Berlin" can silently compare against a column instead of the word.

= NULL

WHERE city = NULL returns nothing, ever. Use WHERE city IS NULL.

Quick quiz

1. What does this print?

SELECT name FROM products WHERE category = 'home' AND price > 10;

A
```
name
Lamp
```
B
```
name
Lamp
Mug
```
C
```
name
Backpack
Lamp
```

Show the answer

Answer: A

Two products are in home, and only the Lamp (18.0) costs more than 10.

2. Which query finds customers whose city is missing?

AWHERE city = NULL
BWHERE city IS NULL
CWHERE city = ''

Show the answer

Answer: B

NULL is not a value you can compare with =. An empty string '' is a real (empty) value, not a missing one.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

PredictRead the result
Type exactly what this query returns, header line first.
ModifyOnly paper
The query lists every product. Change it to list the name and price of products in the paper category only.
WriteShanghai customers
Write a query that returns the name of every customer in Shanghai.

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Terms in this lesson

Term	Chinese	Definition
Table	表	Rows of the same kind of thing; each column holds one fact about it.
Query	查询	A question you ask a database, usually a SELECT statement.
NULL	空值	A missing value; test it with IS NULL.