SELECT, FROM and WHERE: Your First Queries

Almost every SQL query you will ever read starts with the same three ideas. SELECT says which columns you want to see. FROM says which table to look in. WHERE says which rows to keep. In this lesson you learn all three, and a fourth idea that surprises every beginner: NULL.

SELECT and FROM: choose columns

The shortest useful query asks for everything in a table.

SELECT * FROM products;

The star means every column. This is fine for a quick look, but in real work you usually name the columns you need. Naming columns makes the result easier to read, faster for big tables and safer when the table gains new columns later.

SELECT name, price FROM products;

The order you write the columns is the order you get them. Try SELECT price, name FROM products; and the two columns swap places. The table itself does not change. A query only reads data, it never changes it.

Give a column a nicer name with AS

You can rename a column in the result with AS. This is called an alias. It is useful when a column name is unclear or when you calculate a new value.

SELECT name AS product, price AS price_in_rupees FROM products;

You can also calculate. Suppose you want the price with 18 per cent tax added, the way a shop in India might show GST. The database does the sum for every row.

SELECT name, price, price * 1.18 AS price_with_tax FROM products;

Use the tax rate of your own country and your own case, this is only an example. The idea to keep is that a SELECT list can hold calculations, not just column names.

WHERE: keep only the rows you want

WHERE filters rows. It comes after FROM. Only rows where the condition is true are kept.

SELECT name, price
FROM products
WHERE price > 500;

name         | price
Desk lamp    | 650
Backpack     | 1400
Laptop stand | 1800
Mouse        | 700

Four of the eight products cost more than 500, so four rows come back. The comparison signs are the ones you know from school: = equal, <> or != not equal, > greater, < less, >= greater or equal, <= less or equal. Notice that SQL uses a single = to compare, not ==.

Comparing text and dates

Text goes in single quotes, and in SQLite the match is case sensitive for =, so 'nairobi' and 'Nairobi' are different. Other engines may ignore case, so it is a good habit to write values exactly as stored.

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

Dates saved as YYYY-MM-DD text compare correctly with the same signs, because 2024-06-01 sorts before 2024-07-01.

SELECT name, joined FROM customers WHERE joined >= '2024-04-01';

That returns the four customers who joined from April 2024 onward: Emma, Farid, Grace and Hari.

NULL: a value that is not there

Hari Singh has no city in our table. The city is NULL, which means “unknown or missing”. NULL is not zero and not an empty text. It is the absence of a value. Because of this, you cannot test for it with =.

-- This returns nothing, even though Hari has no city:
SELECT name FROM customers WHERE city = NULL;

-- This is the correct way:
SELECT name, city FROM customers WHERE city IS NULL;

name       | city
Hari Singh | NULL

To find rows that do have a value, use IS NOT NULL. The reason = NULL fails is logical: if a value is unknown, the database cannot say that it equals something. “Unknown equals unknown” is also unknown, and WHERE only keeps rows that are definitely true. Many real bugs, and many interview questions, come from forgetting this.

The order the database thinks in

You write SELECT first, but the database reads the query in a different order. It first looks at FROM to find the table, then applies WHERE to keep the rows, and only then picks the columns in SELECT. This is why you cannot use a new alias from SELECT inside WHERE in most engines. You can use the real column or repeat the calculation.

-- Works: repeat the calculation
SELECT name, price * 1.18 AS price_with_tax
FROM products
WHERE price * 1.18 > 1000;

Common beginner mistakes

• Forgetting the quotes around text: WHERE city = Nairobi makes the database look for a column named Nairobi.
• Using double quotes for text. Use single quotes for text values.
• Putting a comma after the last column before FROM: SELECT name, price, FROM products is a syntax error.
• Writing WHERE before FROM. The order is always SELECT, FROM, WHERE.

Your task

1. List the name and category of every product in the Stationery category.
2. List the names of customers who joined before '2024-03-01'.
3. Find customers whose city is missing, then find customers whose city is not missing and count them yourself (it should be 7).
4. Show each product name with a column called price_with_tax that is the price times 1.18.

How to check it worked

• The Stationery query returns three rows: Notebook, Pen pack and Sticky notes.
• The date query returns Asha, Ben and Chidi.
• city IS NULL returns only Hari Singh.