SQL SELECT queries explained: a practical beginner guide

A SELECT query asks a database to show you some of its data. You say which columns you want and which table they live in. If you like, you also say which rows to keep and what order to put them in. The database answers with a grid of rows and columns, a bit like a spreadsheet.

Here is the shortest useful one:

SELECT * FROM menu;

Read it as: show me every column from the menu table. This guide builds up from there, one part at a time: choosing columns, picking rows with WHERE, sorting, limiting, removing repeats and counting. Every query runs right here in your browser, and the end of the guide covers the mistakes beginners make most.

The table used in this guide

The examples use a small café menu. It has three columns: the name of each item, the kind of food it is, and its price in pence. Storing money as whole pence is a common trick, because whole numbers never have rounding surprises.

Each run box below starts by building this table, so every box works on its own. The query you are learning is always the last line. Press run:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT * FROM menu;

item | kind | price
toast | breakfast | 250
porridge | breakfast | 320
bacon roll | breakfast | 450
soup | lunch | 480
cheese toastie | lunch | 520
flapjack | cake | 260

The first line of the answer holds the column names. Every line after it is one row, with a | between the columns. Other database tools draw a proper grid, but the data is the same.

How a SELECT query is built

Every SELECT query has the same shape:

• SELECT names the columns you want. A star, *, means every column.
• FROM names the table to look in.
• The semicolon, ;, ends the query, like a full stop ends a sentence.

The optional parts come after FROM, and always in this order: WHERE, then ORDER BY, then LIMIT. Each one is a clause, and each one has its own section below.

Choosing columns

Most of the time you do not need every column. List the ones you want after SELECT, separated by commas:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item, price FROM menu;

item | price
toast | 250
porridge | 320
bacon roll | 450
soup | 480
cheese toastie | 520
flapjack | 260

The columns come back in the order you list them, so SELECT price, item would put the price first.

Renaming a column with AS

AS gives a column a new name in the answer. The table itself does not change:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item AS dish, price AS pence FROM menu WHERE kind = 'cake';

dish | pence
flapjack | 260

Picking rows with WHERE

WHERE keeps only the rows that pass a test. This query keeps the lunch items:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item, price FROM menu WHERE kind = 'lunch';

item | price
soup | 480
cheese toastie | 520

Text goes in single quotes, like 'lunch'. Numbers do not need quotes, so this finds everything under £3:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item, price FROM menu WHERE price < 300;

item | price
toast | 250
flapjack | 260

The comparisons you can use:

• = equal to
• <> or != not equal to
• < and > less than and greater than
• <= and >= less than or equal, greater than or equal

To test two things at once, join them with AND or OR, for example WHERE kind = 'breakfast' AND price < 400. You will use that in the exercise.

Sorting with ORDER BY

ORDER BY sorts the rows by a column. Smallest first is the default, which you can also write as ASC. Add DESC for biggest first.

Without ORDER BY, the order of the rows is up to the database. It often looks like the order they were added in, but nothing promises that, so sort whenever the order matters.

Only the first few with LIMIT

LIMIT cuts the answer down to a number of rows. Together with ORDER BY, it answers questions like “what are the three most expensive things on the menu?”:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item, price FROM menu ORDER BY price DESC LIMIT 3;

item | price
cheese toastie | 520
soup | 480
bacon roll | 450

LIMIT works in SQLite, MySQL and PostgreSQL. SQL Server writes the same idea differently, as SELECT TOP 3.

Removing repeats with DISTINCT

Ask for the kind column on its own and you get breakfast three times. DISTINCT keeps one of each:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT DISTINCT kind FROM menu;

kind
breakfast
lunch
cake

Counting rows

COUNT(*) counts the rows instead of showing them. With AS, the answer gets a readable name:

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT COUNT(*) AS breakfasts FROM menu WHERE kind = 'breakfast';

breakfasts
3

Without AS breakfasts, the column would be called COUNT(*). SUM, AVG, MIN and MAX work the same way, for totals, averages, smallest and biggest.

Common mistakes

Leaving out FROM

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT * menu;

near "menu": syntax error

A syntax error means the database could not understand the query. The word in the message is where it got stuck, so the problem is just before it: the missing FROM.

Text without quotes

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item FROM menu WHERE kind = lunch;

no such column: lunch

Without quotes, the database thinks lunch is the name of a column. Write 'lunch' and it becomes text to compare with.

Clauses in the wrong order

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT * FROM menu ORDER BY price WHERE kind = 'lunch';

near "WHERE": syntax error

WHERE must come before ORDER BY. Think of it as choosing the rows first, then sorting what is left.

Capital letters in text

In SQLite, which runs these boxes, comparing text with = cares about capitals, so WHERE kind = 'Lunch' finds no rows at all. MySQL ignores capitals with its usual settings. Match the stored text exactly and your query works everywhere.

Double quotes for text

In standard SQL, double quotes are for names of tables and columns, not text. SQLite and MySQL often let "lunch" work as text anyway, but PostgreSQL looks for a column called lunch and stops with an error. Single quotes work in every database.

Using = NULL

A missing value is stored as NULL, and = NULL never matches anything, not even other missing values. Write WHERE price IS NULL instead.

A short exercise

Write one query that shows the name and price of every breakfast item under £4, cheapest first. Remember that the prices are in pence.

To try it out, open the playground from the button at the bottom of the page and choose SQL. Copy the CREATE TABLE and INSERT lines from any box above, then add your query underneath.

One possible answer

CREATE TABLE menu (item TEXT, kind TEXT, price INTEGER);
INSERT INTO menu VALUES
  ('toast', 'breakfast', 250),
  ('porridge', 'breakfast', 320),
  ('bacon roll', 'breakfast', 450),
  ('soup', 'lunch', 480),
  ('cheese toastie', 'lunch', 520),
  ('flapjack', 'cake', 260);

SELECT item, price
FROM menu
WHERE kind = 'breakfast' AND price < 400
ORDER BY price;

item | price
toast | 250
porridge | 320

£4 becomes 400 because the prices are in pence. WHERE comes before ORDER BY, and splitting a long query over several lines is fine: the semicolon is what ends it.

Related reading

• What does a computer program do?, for where databases fit in the programs you use every day.
• Parsing JSON in Python, another common way programs store and pass data around.

Your next step

SELECT is the query you will write most often, so it pays to practise it until it feels automatic. Gwecode’s SQL Level 1 starts from what a database is and gives each part of this guide its own short lesson: choosing columns, WHERE, sorting, LIMIT, DISTINCT and counting, plus making and changing tables of your own. Its level check has you answer a shop owner’s questions with queries you write yourself. Every lesson is free and runs in your browser. Create a free account to keep your progress, and write your first query below.

questions