SQL Basics: Reading Data with SELECT

A bike shop owner wants quick answers. Which city bikes cost less than 450? What are the three priciest models? Which bikes are sold out? Each question is a small request to a table of data, and SQL is the language for making those requests. The command that reads data is called SELECT.

This entry teaches the four clauses you will use most: SELECT to choose columns, WHERE to choose rows, ORDER BY to sort, and LIMIT to cap the number of rows. Every result below was worked out by hand against the same small table, so you can check your own answers. You only need to know what a table is. If that is new to you, read tables, rows, columns and keys first.

The table we will query

The bikes table has one row per model:

You can paste this into a free tool such as SQLite to try the queries yourself. A query never changes the table. SELECT only reads.

SELECT: choosing columns

The simplest query names the columns you want and the table they come from:

SELECT model, price
FROM bikes;

This returns two columns for all eight rows. The first rows are Trailblazer at 640.00, City Glide at 410.00 and Speedster at 880.00, and so on down the table. You can write SELECT * to get every column. The PostgreSQL tutorial points out that * is widely considered poor style in production code, because adding a column later changes what the query returns. Name the columns you need.

You can also rename an output column with AS, or calculate a new one:

SELECT model, price * 0.9 AS sale_price
FROM bikes
WHERE id = 5;

Result:

The number of decimal places shown can differ between systems. The value, 350 times 0.9, is 315.

Removing repeated values

Several bikes share a category. To list each category only once, add DISTINCT after SELECT:

SELECT DISTINCT category
FROM bikes
ORDER BY category;

The table has eight rows, but only four different categories, so four rows come back. Pairing DISTINCT with ORDER BY is a good habit, because the database does not promise any order for distinct values on its own.

WHERE: choosing rows

WHERE keeps only the rows for which a condition is true. Text goes in single quotes.

SELECT model, price
FROM bikes
WHERE category = 'city';

Three rows match:

Combining conditions

Use AND when both conditions must hold, and OR when either is enough:

SELECT model, price
FROM bikes
WHERE category = 'mountain' AND price < 700;

Summit Pro is a mountain bike but costs 1150, so it fails the second condition. Now with OR:

SELECT id, model
FROM bikes
WHERE category = 'kids' OR price > 800
ORDER BY id;

Other handy tests

• BETWEEN 400 AND 600 is inclusive at both ends. It matches City Glide (410), Pathfinder (520) and Breeze (455).
• IN ('city', 'kids') matches any value in the list.
• IS NULL finds missing values. Never write = NULL, because comparing anything to NULL gives "unknown", not true.

ORDER BY: sorting

Without ORDER BY, SQL promises no row order at all. Rows may come back in the order they were stored, or in some other order, and the order can change. If order matters, ask for it.

SELECT model, price
FROM bikes
ORDER BY price;

Ascending order is the default, so the cheapest comes first: Kid Rocket 190, Commuter Lite 350, City Glide 410, Breeze 455, Pathfinder 520, Trailblazer 640, Speedster 880, Summit Pro 1150. Add DESC for the opposite direction.

You can sort by several columns. Later columns break ties left by earlier ones:

SELECT category, model, price
FROM bikes
ORDER BY category, price DESC;

Categories run in alphabetical order, and inside each category the prices fall from high to low.

LIMIT: capping the number of rows

LIMIT returns at most a set number of rows. To see the three most expensive bikes:

SELECT model, price
FROM bikes
ORDER BY price DESC
LIMIT 3;

The ORDER BY here is not optional. PostgreSQL's documentation warns that using LIMIT without a clear ordering gives an unpredictable subset of rows. "Top three" only means something once you say what "top" means.

LIMIT is a maximum, not a promise. Ask for the three cheapest mountain bikes that are in stock:

SELECT model, price
FROM bikes
WHERE category = 'mountain' AND in_stock > 0
ORDER BY price
LIMIT 3;

Only two rows come back, because Pathfinder has no stock and only two mountain bikes qualify.

Where systems differ

LIMIT works in PostgreSQL, SQLite and MySQL. The SQL standard spells the idea FETCH FIRST 3 ROWS ONLY, which PostgreSQL also accepts, and some systems use other words, such as TOP in Microsoft SQL Server. Check the manual of the system you use.

Reading a query in order

A query has a fixed order when you write it: SELECT, FROM, WHERE, ORDER BY, LIMIT. It helps to read it as a sentence: "Select these columns, from this table, where this is true, sorted this way, and give me only this many."

When a query returns something unexpected, build it up one clause at a time. Start with SELECT * FROM bikes LIMIT 5 to see the shape of the data. Add the WHERE condition and check that the right rows survive. Then add the sort, and last the limit. Each small step is easy to check against the table, and the mistake shows up as soon as you add the clause that causes it. This habit also helps when you write longer queries later, with joins and totals.

Common mistakes

• Using double quotes for text. In standard SQL, single quotes mark text values. Double quotes mark names of tables and columns, so "city" is read as a column, not the word. Some systems, such as SQLite and MySQL, may quietly accept double quotes for text, but PostgreSQL does not. Stick to single quotes.
• Forgetting ORDER BY with LIMIT. The rows you get are unpredictable, and a result that looks right today may differ tomorrow.
• Writing = NULL. It never matches. Use IS NULL or IS NOT NULL.
• Mixing AND and OR without brackets. AND is evaluated first. Add parentheses to say what you mean.
• Relying on SELECT *. The output changes when columns are added, and you transfer more data than you need.
• Expecting text matches to ignore case. Whether 'City' equals 'city' depends on the system and its settings.

FAQ

Does SELECT change my data?

No. SELECT only reads. Commands that change data, such as UPDATE and DELETE, are separate, and they deserve extra care.

Is SQL case sensitive?

Keywords such as select and SELECT mean the same thing. Many people write keywords in capitals for readability. Text values inside quotes may be case sensitive, depending on the system.

What does the semicolon do?

It marks the end of a statement. Some tools require it, and others accept a query without it. Including it is a safe habit.

How do I get data from two tables at once?

You use a join. The entry on SQL joins walks through the main types with small examples.

What should I learn after these four clauses?

Practice GROUP BY for counts and totals, then joins. Cleaning the values you filter on matters too, as covered in data cleaning basics. The Data and Databases area lists the rest of the path.