SELECT is the statement you will write most. This lesson works through it piece by piece with the Orbit data from lesson 6: three users and five notes.
The shape of a SELECT
SELECT columns and expressions
FROM tables
WHERE row filter
GROUP BY grouping -- lesson 9
HAVING group filter -- lesson 9
ORDER BY sort order
LIMIT how many rows;
Only SELECT is required: SELECT 1 + 1; is valid. The other clauses are optional but must appear in this order.
Choosing columns
SELECT id, title, pinned FROM notes ORDER BY id;
+----+------------------+--------+
| id | title | pinned |
+----+------------------+--------+
| 1 | Shopping list | 1 |
| 2 | Deploy checklist | 0 |
| 3 | Book ideas | 0 |
| 4 | MySQL tips | 1 |
| 5 | Meeting notes | 0 |
+----+------------------+--------+
5 rows in set (0.00 sec)
SELECT * returns every column. It is handy at the prompt, but in application code list the columns you need: less data travels over the network and your code does not break when someone adds a column.
Warning: Without ORDER BY, rows come back in whatever order is cheapest for the server, often but not always primary-key order. If order matters, always write ORDER BY.
Select expressions and name them with AS:
SELECT title,
CHAR_LENGTH(body) AS chars,
UPPER(LEFT(title, 3)) AS code
FROM notes
WHERE user_id = 2
ORDER BY id;
+------------+-------+------+
| title | chars | code |
+------------+-------+------+
| Book ideas | 26 | BOO |
| MySQL tips | 27 | MYS |
+------------+-------+------+
2 rows in set (0.00 sec)
CHAR_LENGTH counts characters; LENGTH counts bytes, which differs for non-ASCII text in utf8mb4.
Filtering with WHERE
WHERE keeps the rows for which the condition is true. Because pinned is 0 or 1, it can stand alone as a condition:
SELECT id, title FROM notes WHERE pinned ORDER BY id;
+----+---------------+
| id | title |
+----+---------------+
| 1 | Shopping list |
| 4 | MySQL tips |
+----+---------------+
2 rows in set (0.00 sec)
The comparison operators are =, <> (or !=), <, <=, >, >=. A few more save typing:
SELECT id FROM notes WHERE user_id IN (1, 3);
SELECT id FROM notes WHERE user_id NOT IN (2);
SELECT id FROM notes WHERE created_at BETWEEN '2026-10-01' AND '2026-10-31';
SELECT id FROM notes WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';
The last two look equivalent, but BETWEEN includes both ends, and '2026-10-31' means midnight at the start of that day, so a note written on the afternoon of 31 October would be missed. For time ranges use the half-open form >= start AND < end.
AND binds tighter than OR
SELECT COUNT(*) FROM notes WHERE user_id = 1 OR user_id = 2 AND pinned;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
SELECT COUNT(*) FROM notes WHERE (user_id = 1 OR user_id = 2) AND pinned;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
The first query means “all of Ana’s notes, plus Ben’s pinned ones”, because AND is evaluated before OR. Use parentheses whenever you mix them.
NULL: the missing value
NULL means “unknown”. Comparing anything with an unknown value gives an unknown result, not true or false:
SELECT NULL = NULL AS equals, NULL IS NULL AS is_null, NULL <=> NULL AS null_safe;
+--------+---------+-----------+
| equals | is_null | null_safe |
+--------+---------+-----------+
| NULL | 1 | 1 |
+--------+---------+-----------+
1 row in set (0.00 sec)
WHERE keeps only rows where the condition is true, so WHERE email = NULL never matches anything. The tools for missing values:
| To test | Write |
|---|---|
| Value is missing | col IS NULL |
| Value is present | col IS NOT NULL |
| Equal, treating two NULLs as equal | a <=> b (MySQL’s null-safe equals) |
| Replace NULL with a fallback | IFNULL(col, 'x') or COALESCE(col, 'x') |
| Turn a value into NULL | NULLIF(col, '') |
Sorting
SELECT id, title, DATE(created_at) AS day
FROM notes
ORDER BY created_at DESC
LIMIT 3;
+----+---------------+------------+
| id | title | day |
+----+---------------+------------+
| 5 | Meeting notes | 2026-11-02 |
| 4 | MySQL tips | 2026-10-12 |
| 3 | Book ideas | 2026-10-05 |
+----+---------------+------------+
3 rows in set (0.00 sec)
DESCsorts descending;ASCis the default.- Sort by several columns:
ORDER BY pinned DESC, created_at DESClists pinned notes first, newest first within each group. - In MySQL, NULLs sort first in ascending order and last in descending order. There is no
NULLS LASTkeyword; writeORDER BY col IS NULL, colto push NULLs to the end.
Custom sort orders with FIELD
FIELD(value, a, b, c) returns the position of value in the list, which gives you any order you like. Show pro users first:
SELECT id, name, plan FROM users ORDER BY FIELD(plan, 'pro', 'free'), id;
+----+------------+------+
| id | name | plan |
+----+------------+------+
| 2 | Ben Carter | pro |
| 3 | Chen Wei | pro |
| 1 | Ana Silva | free |
+----+------------+------+
3 rows in set (0.00 sec)
Limiting and paginating
LIMIT n returns at most n rows; LIMIT n OFFSET m (or the MySQL shorthand LIMIT m, n) skips m rows first. Page 3 of a list with 20 rows per page is LIMIT 20 OFFSET 40.
Offsets get slow on deep pages, because the server still reads and throws away all the skipped rows. Keyset pagination remembers where the previous page ended instead:
SELECT id, title FROM notes WHERE id > 2 ORDER BY id LIMIT 2;
+----+------------+
| id | title |
+----+------------+
| 3 | Book ideas |
| 4 | MySQL tips |
+----+------------+
2 rows in set (0.00 sec)
With an index on the sort column (the primary key here), every page is as fast as the first.
Matching text
LIKE has two wildcards: % for any run of characters and _ for exactly one. With the default case-insensitive collation, LIKE ignores case, so MySQL needs no separate ILIKE:
SELECT id, title FROM notes WHERE title LIKE '%LIST%' ORDER BY id;
+----+------------------+
| id | title |
+----+------------------+
| 1 | Shopping list |
| 2 | Deploy checklist |
+----+------------------+
2 rows in set (0.00 sec)
For regular expressions use REGEXP (or its synonym RLIKE), which also follows the column’s collation:
SELECT id, title FROM notes WHERE title REGEXP '^(b|m)' ORDER BY id;
+----+---------------+
| id | title |
+----+---------------+
| 3 | Book ideas |
| 4 | MySQL tips |
| 5 | Meeting notes |
+----+---------------+
3 rows in set (0.00 sec)
MySQL 8 uses the ICU regular-expression library, so you also get REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR() and REGEXP_INSTR(). A pattern that starts with % cannot use a normal index; lesson 17 shows full-text search for that job.
Branching with CASE and IF
CASE is SQL’s if/else and works anywhere an expression does. MySQL also has the shorter IF(condition, then, else):
SELECT id,
IF(pinned, 'pinned', 'normal') AS kind,
CASE
WHEN CHAR_LENGTH(body) > 25 THEN 'long'
ELSE 'short'
END AS size
FROM notes
ORDER BY id;
+----+--------+-------+
| id | kind | size |
+----+--------+-------+
| 1 | pinned | short |
| 2 | normal | long |
| 3 | normal | long |
| 4 | pinned | long |
| 5 | normal | short |
+----+--------+-------+
5 rows in set (0.00 sec)
Working with dates
SELECT id, DATE_FORMAT(created_at, '%a %d %b') AS written
FROM notes
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-08'
ORDER BY id;
+----+------------+
| id | written |
+----+------------+
| 1 | Thu 01 Oct |
| 2 | Sat 03 Oct |
| 3 | Mon 05 Oct |
+----+------------+
3 rows in set (0.00 sec)
Other helpers you will reach for: NOW() and CURDATE(), DATE(col), YEAR(col) and MONTH(col), DATEDIFF(a, b) and interval arithmetic such as created_at > NOW() - INTERVAL 7 DAY. Lesson 11 has a full tour of built-in functions.
Removing duplicates
SELECT DISTINCT user_id FROM notes ORDER BY user_id;
+---------+
| user_id |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)
DISTINCT applies to the whole selected row: SELECT DISTINCT user_id, pinned returns each distinct combination.
The order of evaluation
SQL is written in one order and evaluated in another:
FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
That explains a common surprise: you cannot use a column alias from SELECT in WHERE, because WHERE runs first. You can use it in ORDER BY, and MySQL also allows it in GROUP BY and HAVING as an extension.
Quick reference
| Task | SQL |
|---|---|
| Columns and expressions | SELECT a, b * 2 AS doubled FROM t |
| Filter | WHERE a = 1 AND (b > 2 OR c IS NULL) |
| List and range | IN (...), >= start AND < end |
| Missing values | IS NULL, IFNULL(col, 'x'), a <=> b |
| Sort | ORDER BY a DESC, b |
| Custom order | ORDER BY FIELD(col, 'x', 'y') |
| Top N / next page | LIMIT n, WHERE id > :last ORDER BY id LIMIT n |
| Text pattern | LIKE '%x%', REGEXP '^x' |
| If / else | IF(c, a, b), CASE WHEN ... END |
| Unique rows | SELECT DISTINCT ... |
Exercise
Which query returns users whose name is missing?
Show answer
Answer: B. Comparing with = NULL yields NULL, which WHERE treats as not true. IS NULL (or the null-safe <=> NULL) is the correct test.
Exercise
With the default utf8mb4_0900_ai_ci collation, which titles does WHERE title LIKE ‘meeting%’ match?
Show answer
Answer: D. The collation decides case sensitivity. The default is case-insensitive, so LIKE ignores case. MySQL has no ILIKE.
Exercise
You need every note created in October 2026, including those written late on 31 October. Which filter is correct?
Show answer
Answer: C. ‘2026-10-31’ means midnight at the start of that day, so BETWEEN misses the rest of it. The half-open range covers the whole month and can use an index on created_at; MONTH() also matches other years and cannot use the index.
Key takeaways
- Always add
ORDER BYwhen the order of rows matters; NULLs sort first ascending in MySQL. - Use parentheses when mixing
ANDandOR. NULLis unknown: test it withIS NULLor<=>, replace it withIFNULLorCOALESCE.- Text comparisons,
LIKEandREGEXPfollow the collation, which ignores case by default. - Filter time ranges with
>= start AND < end; paginate with keyset rather than deep offsets.
FAQ
Is SELECT * really that bad?
It is fine for exploring at the prompt. In application code and views, explicit columns send less data, allow covering indexes (lesson 13) and do not change shape when the table changes.
Are SQL keywords case-sensitive in MySQL?
No: select, SELECT and Select are the same. Column names are case-insensitive too. Table names follow the operating system, which is why lowercase names are the safe convention.
Single quotes, double quotes or backticks?
Single quotes for strings ('Ana'), backticks for identifiers that need quoting (` order ). MySQL also accepts double quotes for strings unless the ANSI_QUOTES` mode is on, but sticking to single quotes keeps your SQL portable.
How do I search for a literal % or _ with LIKE?
Escape it with a backslash: WHERE title LIKE '100\%'. You can choose another escape character with LIKE '100!%' ESCAPE '!'.
How do I pick random rows?
ORDER BY RAND() LIMIT 5 works for small tables but sorts the whole table, which is slow on large ones. For big tables pick random ids in the application, or filter on a random range of the primary key first.