Chapter 2

Chapter 2 — SELECT Basics

The SELECT statement is the workhorse of SQL. Every time you want to read data from a database — whether it's one row or a million — you use SELECT. This chapter covers the anatomy of a query, choosing which columns to return, filtering rows with WHERE, and combining conditions with AND, OR, and NOT.

Sample data. The examples in this chapter assume the bookshop database contains some rows. If you're following along, add a few authors and books with INSERT statements — Chapter 5 covers INSERT in full, but for now any handful of rows will work. The logic is the same regardless of which specific values are in your table.

1. Anatomy of a SELECT Statement

Every SELECT query has the same basic skeleton. Here it is in annotated form:

SELECT title, price FROM books WHERE price < 10.00 ;

SELECT — the keyword that starts a query

title, price — the columns you want returned

books — the table to read from

price < 10.00 — only rows matching this condition

The WHERE clause is optional — without it, every row in the table is returned. SELECT and FROM are always required.

SQL is not case-sensitive for keywords. SELECT, select, and Select all work. Convention is to write keywords in UPPERCASE and table/column names in lowercase — it makes queries easier to read at a glance. MySQL is also case-insensitive for table and column names on Windows and macOS by default (Linux is case-sensitive — something to watch if you move databases between servers).

2. Choosing Columns

SELECT * — all columns

The asterisk * is shorthand for "all columns". It's useful for quick exploration, but avoid it in production code — if someone adds a column to the table later, your query silently returns it too.

SELECT * FROM books;

book_id	author_id	title	genre	published_year	price	stock
1	1	The Midnight Library	Fiction	2020	8.99	42
2	2	Dune	Sci-Fi	1965	9.99	18
3	3	Sapiens	Non-fiction	2011	10.99	31
4	1	The Humans	Fiction	2013	7.99	25
5	4	Atomic Habits	Self-help	2018	11.99	60

All 7 columns, all 5 rows returned.

Named columns

Specify exactly the columns you need, separated by commas. The result set contains only those columns, in the order you listed them:

SELECT title, genre, price FROM books;

title	genre	price
The Midnight Library	Fiction	8.99
Dune	Sci-Fi	9.99
Sapiens	Non-fiction	10.99
The Humans	Fiction	7.99
Atomic Habits	Self-help	11.99

Column aliases — AS

You can rename a column in the output using AS. The alias only affects the result label — it doesn't change anything in the database:

SELECT title AS book_title, price AS price_gbp, stock AS units_available FROM books;

book_title	price_gbp	units_available
The Midnight Library	8.99	42
Dune	9.99	18
Sapiens	10.99	31
The Humans	7.99	25
Atomic Habits	11.99	60

Aliases with spaces need backticks or quotes: price AS `Price (GBP)`. Single-word aliases don't need them, though it's fine to include them for clarity.

Expressions in SELECT

You're not limited to raw column values — you can do calculations directly in the query. The result is computed row by row:

SELECT title, price, price * 1.20 AS price_with_vat, stock * price AS stock_value FROM books;

title	price	price_with_vat	stock_value
The Midnight Library	8.99	10.788000	377.58
Dune	9.99	11.988000	179.82
Sapiens	10.99	13.188000	340.69
The Humans	7.99	9.588000	199.75
Atomic Habits	11.99	14.388000	719.40

Literal values and text

SELECT first_name, last_name, 'bookshop.com' AS source FROM authors;

first_name	last_name	source
Matt	Haig	bookshop.com
Frank	Herbert	bookshop.com
Yuval Noah	Harari	bookshop.com

The literal string 'bookshop.com' appears in every row — useful for tagging results when combining data from multiple sources.

DISTINCT — removing duplicates

If multiple rows have the same value in a column, DISTINCT collapses them into one:

-- Without DISTINCT: shows genre for every book (with repeats) SELECT genre FROM books; Fiction Sci-Fi Non-fiction Fiction ← duplicate Self-help -- With DISTINCT: each genre appears once SELECT DISTINCT genre FROM books; Fiction Sci-Fi Non-fiction Self-help

3. Filtering with WHERE

The WHERE clause filters which rows are included in the result. MySQL evaluates the condition for every row in the table and only returns rows where the condition is TRUE.

-- Only books priced below £10 SELECT title, price FROM books WHERE price < 10.00;

title	price
The Midnight Library	8.99
Dune	9.99
The Humans	7.99

Comparison operators

Operator	Name	Example	Matches rows where…
=	Equal	genre = 'Fiction'	genre is exactly 'Fiction'
!= or <>	Not equal	genre != 'Fiction'	genre is anything except 'Fiction'
<	Less than	price < 10.00	price is below 10.00
>	Greater than	price > 10.00	price is above 10.00
<=	Less than or equal	price <= 9.99	price is 9.99 or below
>=	Greater than or equal	stock >= 20	stock is 20 or more
BETWEEN … AND …	Inclusive range	price BETWEEN 8 AND 11	price is 8, 11, or anything in between (both ends included)
IN (…)	Matches any value in a list	genre IN ('Fiction', 'Sci-Fi')	genre is either 'Fiction' or 'Sci-Fi'
NOT IN (…)	Matches none of the listed values	genre NOT IN ('Self-help')	genre is anything except 'Self-help'
LIKE	Pattern match	title LIKE 'The%'	title starts with 'The'. `%` = any characters, `_` = exactly one character
IS NULL	Value is absent	nationality IS NULL	the column has no value stored
IS NOT NULL	Value is present	birth_year IS NOT NULL	the column has a value

String comparisons

-- Exact match on a text column (single quotes around strings) SELECT title, genre FROM books WHERE genre = 'Fiction';

title	genre
The Midnight Library	Fiction
The Humans	Fiction

Use single quotes for strings, not double quotes. In standard SQL and MySQL, string values are wrapped in single quotes: 'Fiction'. Double quotes are reserved for identifiers (column/table names with spaces). Mixing them up causes confusing errors. If a string itself contains a single quote, escape it by doubling it: 'O''Brien'.

LIKE — pattern matching

-- % matches any sequence of characters (including none) SELECT title FROM books WHERE title LIKE 'The%'; The Midnight Library The Humans -- _ matches exactly one character SELECT title FROM books WHERE title LIKE 'D___'; Dune -- % at both ends: contains the word anywhere SELECT title FROM books WHERE title LIKE '%Habit%'; Atomic Habits

LIKE with a leading % is slow on large tables. LIKE '%something' or LIKE '%something%' cannot use an index — MySQL must scan every row. LIKE 'something%' (trailing % only) can use an index and is much faster. For full-text search, MySQL has a dedicated FULLTEXT index type — but that's beyond the scope of this course.

BETWEEN

-- Both ends are inclusive — same as price >= 8.00 AND price <= 11.00 SELECT title, price FROM books WHERE price BETWEEN 8.00 AND 11.00;

title	price
The Midnight Library	8.99
Dune	9.99
Sapiens	10.99

IN — matching a list

-- Cleaner than writing: genre = 'Fiction' OR genre = 'Sci-Fi' SELECT title, genre FROM books WHERE genre IN ('Fiction', 'Sci-Fi');

title	genre
The Midnight Library	Fiction
Dune	Sci-Fi
The Humans	Fiction

IS NULL / IS NOT NULL

-- NULL means "no value stored" — you CANNOT use = NULL SELECT first_name, last_name FROM authors WHERE birth_year IS NULL; -- correct -- This will never return any rows, even if birth_year IS NULL: WHERE birth_year = NULL -- WRONG — always FALSE in SQL

NULL is not a value — it's the absence of a value. NULL does not equal anything, not even itself. NULL = NULL evaluates to NULL (not TRUE), which is why WHERE birth_year = NULL never matches any row. Always use IS NULL and IS NOT NULL to test for missing values.

4. Combining Conditions — AND, OR, NOT

A single WHERE clause can contain multiple conditions joined by logical operators. MySQL evaluates the full expression for each row.

AND — both conditions must be true

-- Fiction books that cost less than £10 SELECT title, genre, price FROM books WHERE genre = 'Fiction' AND price < 10.00;

title	genre	price
The Midnight Library	Fiction	8.99
The Humans	Fiction	7.99

OR — either condition is enough

-- Books that are Fiction OR are cheaper than £9 SELECT title, genre, price FROM books WHERE genre = 'Fiction' OR price < 9.00;

title	genre	price
The Midnight Library	Fiction	8.99
The Humans	Fiction	7.99

The Humans matches both conditions. The Midnight Library matches genre = 'Fiction'. No other book is below £9 and non-fiction.

NOT — invert a condition

-- Every book that is NOT Fiction SELECT title, genre FROM books WHERE NOT genre = 'Fiction'; -- Equivalent, and more commonly written as: WHERE genre != 'Fiction'

Operator precedence — AND binds before OR

This is one of the most common SQL mistakes. AND is evaluated before OR, just like multiplication before addition in arithmetic. Without parentheses, the logic may not be what you intended:

-- INTENDED: Fiction books, OR any book published after 2015 -- WRONG — AND binds first, so this reads: -- "Fiction" OR "(price < 9 AND published_year > 2015)" WHERE genre = 'Fiction' OR price < 9.00 AND published_year > 2015; -- RIGHT — parentheses make the grouping explicit WHERE (genre = 'Fiction' OR price < 9.00) AND published_year > 2015;

When in doubt, use parentheses. They cost nothing and make the logic unambiguous — both for MySQL and for whoever reads the query next. Any time you mix AND and OR in the same WHERE clause, wrap the OR groups in parentheses.

Logic truth tables

If you're ever unsure how AND, OR, and NOT combine, these tables show every combination. NULL is included because it silently affects results when columns can be null:

A AND B

TRUETRUETRUE

TRUEFALSEFALSE

FALSETRUEFALSE

FALSEFALSEFALSE

TRUENULLNULL

FALSENULLFALSE

A OR B

TRUETRUETRUE

TRUEFALSETRUE

FALSETRUETRUE

FALSEFALSEFALSE

TRUENULLTRUE

FALSENULLNULL

NOT A

TRUEFALSE

FALSETRUE

NULLNULL

Putting it all together

-- Fiction or Sci-Fi, priced between £8 and £11, with stock available SELECT title, genre, price, stock FROM books WHERE (genre IN ('Fiction', 'Sci-Fi')) AND price BETWEEN 8.00 AND 11.00 AND stock > 0;

title	genre	price	stock
The Midnight Library	Fiction	8.99	42
Dune	Sci-Fi	9.99	18

Chapter Summary

Syntax	What it does
SELECT *	Return all columns. Avoid in production code.
SELECT col1, col2	Return only named columns, in the order listed.
col AS alias	Rename a column in the output. Doesn't change the database.
SELECT DISTINCT col	Remove duplicate values from the result.
WHERE col = 'value'	Filter rows — only rows where the condition is TRUE are returned.
= != < > <= >=	Comparison operators for numbers, strings, and dates.
BETWEEN a AND b	Inclusive range check. Both ends included.
IN ('a', 'b', 'c')	Match any value in a list. Cleaner than multiple OR conditions.
LIKE 'pattern%'	Pattern match. % = any characters, _ = one character.
IS NULL / IS NOT NULL	The only correct way to test for missing values. Never use = NULL.
AND / OR / NOT	Combine conditions. AND binds before OR — use parentheses to be explicit.

Next: Chapter 3 — Sorting and Limiting. Results currently come back in an unpredictable order. Next chapter covers ORDER BY to sort them, LIMIT to cap how many rows are returned, and OFFSET for pagination.