Lesson 07 beginner

Querying Data with SELECT

Read data with SELECT: choose columns and expressions, filter with WHERE, IN, BETWEEN and correct AND/OR grouping, handle NULL with IS NULL and the null-safe <=> operator, sort (including custom orders with FIELD), paginate with LIMIT and keyset pagination, match text with LIKE and REGEXP under MySQL's case-insensitive collations, branch with CASE and IF, format dates, and learn the order in which a query is evaluated.

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 testWrite
Value is missingcol IS NULL
Value is presentcol IS NOT NULL
Equal, treating two NULLs as equala <=> b (MySQL’s null-safe equals)
Replace NULL with a fallbackIFNULL(col, 'x') or COALESCE(col, 'x')
Turn a value into NULLNULLIF(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)
  • DESC sorts descending; ASC is the default.
  • Sort by several columns: ORDER BY pinned DESC, created_at DESC lists 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 LAST keyword; write ORDER BY col IS NULL, col to 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

TaskSQL
Columns and expressionsSELECT a, b * 2 AS doubled FROM t
FilterWHERE a = 1 AND (b > 2 OR c IS NULL)
List and rangeIN (...), >= start AND < end
Missing valuesIS NULL, IFNULL(col, 'x'), a <=> b
SortORDER BY a DESC, b
Custom orderORDER BY FIELD(col, 'x', 'y')
Top N / next pageLIMIT n, WHERE id > :last ORDER BY id LIMIT n
Text patternLIKE '%x%', REGEXP '^x'
If / elseIF(c, a, b), CASE WHEN ... END
Unique rowsSELECT DISTINCT ...

Exercise

Which query returns users whose name is missing?

  1. SELECT * FROM users WHERE name = NULL;
  2. SELECT * FROM users WHERE name IS NULL;
  3. SELECT * FROM users WHERE name = ”;
  4. SELECT * FROM users WHERE NOT name;
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?

  1. Only titles starting with lowercase “meeting”
  2. None, because LIKE needs an exact case match
  3. Only “Meeting notes” if you write ILIKE
  4. “Meeting notes”, because comparisons ignore case
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?

  1. created_at BETWEEN ‘2026-10-01’ AND ‘2026-10-31’
  2. MONTH(created_at) = 10
  3. created_at >= ‘2026-10-01’ AND created_at < ‘2026-11-01’
  4. created_at LIKE ‘2026-10%’
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 BY when the order of rows matters; NULLs sort first ascending in MySQL.
  • Use parentheses when mixing AND and OR.
  • NULL is unknown: test it with IS NULL or <=>, replace it with IFNULL or COALESCE.
  • Text comparisons, LIKE and REGEXP follow 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.

↑ ↓ navigate Enter open Esc close