Database NotesRohit’s interview study guide
/
Chapter 02

Querying Data

Reading data with SELECT: choosing columns, filtering rows, the rules of NULL, sorting, paging, removing duplicates, computing values, CASE, the everyday functions, and the order in which a query is really evaluated.

10 topics
Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#SELECT and FROM

Added

SELECT is the only statement that reads data, and every query has the same skeleton: FROM names the table to read, SELECT lists the columns (or expressions) to return. The result is itself a table, with rows and named columns, which is why you can feed one query's result into another.

Think of FROM as pulling a sheet out of the filing cabinet and SELECT as a stencil you lay over it: only the columns cut out of the stencil show through. The sheet itself is untouched; a query never changes the data it reads.

SQL
SELECT name, price
FROM products;
Result
+---------------------------------------+---------+
| name                                  | price   |
+---------------------------------------+---------+
| ThinkBook 14                          |  899.00 |
| MacBook Air                           | 1299.00 |
| Pixel 10                              |  799.00 |
| iPhone 17                             | 1099.00 |
| SQL Performance Explained             |   39.00 |
| Designing Data-Intensive Applications |   49.00 |
| USB-C Charger                         |   29.00 |
| Noise-cancelling Headphones           |  249.00 |
+---------------------------------------+---------+
8 rows in set (0.000 sec)
What’s happening
  1. FROM products is logically evaluated first: it produces all 8 rows of the table with all 5 columns (id, name, category_id, price, stock).
  2. SELECT name, price then keeps just those two columns, in that order. The column headers in the result are the names you selected.
  3. There's no WHERE, so every row comes back, and no ORDER BY, so the order is whatever the engine produced. Here it happens to be primary-key order because InnoDB read the table through its clustered index, but nothing guarantees it (see Sorting with ORDER BY).
  4. 8 rows in set (0.000 sec) is the client's summary: the row count and how long the server took.
AdvancedSELECT * and why production code avoids it

SELECT * means "every column, in table order". It's perfect at the prompt when you're exploring:

SQL
SELECT * FROM categories;
Result
+----+-------------+-----------+
| id | name        | parent_id |
+----+-------------+-----------+
|  1 | Electronics |      NULL |
|  2 | Laptops     |         1 |
|  3 | Phones      |         1 |
|  4 | Books       |      NULL |
|  5 | Accessories |         1 |
+----+-------------+-----------+
What’s happening
  1. The * expands to id, name, parent_id at the moment the statement is parsed.
  2. That's exactly the problem in application code: add a column (say a 2 MB description TEXT) and every SELECT * starts shipping it over the network, whether or not anyone reads it.
  3. Code that reads columns by position breaks if the column order changes, and joins with SELECT * return duplicate names (id from both tables) that drivers silently collapse into one key.
  4. Selecting only the columns you need also lets MySQL answer from an index alone (a covering index, see Covering indexes). Name your columns in real code.
AdvancedSELECT without a table, and qualified names

FROM is optional when you're only computing values. MySQL also accepts the Oracle-style dummy table DUAL:

SQL
SELECT 1 + 1 AS two, NOW() IS NOT NULL AS has_clock, 'hi' AS greeting;
SELECT 1 + 1 FROM DUAL;
SELECT ch01.products.name FROM ch01.products WHERE ch01.products.id = 2;
Result
+-----+-----------+----------+
| two | has_clock | greeting |
+-----+-----------+----------+
|   2 |         1 | hi       |
+-----+-----------+----------+
+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
+-------------+
| name        |
+-------------+
| MacBook Air |
+-------------+
What’s happening
  1. With no FROM, the query produces exactly one row of computed values. MySQL has no boolean type, so NOW() IS NOT NULL comes back as 1 (true). 0 is false.
  2. Without an alias the column is named after the expression text, 1 + 1. DUAL exists only so code written for Oracle, where FROM is mandatory, still runs.
  3. Names can be fully qualified as database.table.column. You rarely need all three parts, but database.table lets one query read tables from two databases on the same server, and table.column is how you say which id you mean when two joined tables both have one.

#Filtering with WHERE

Added

WHERE keeps only the rows for which a condition is true. It's evaluated for each row the FROM clause produces, before anything is grouped, sorted or selected. A condition is built from comparisons (=, <> or !=, <, <=, >, >=), range and set tests (BETWEEN, IN), pattern matches (LIKE, REGEXP), IS NULL, and the logic operators AND, OR and NOT.

The mental model is a bouncer with a checklist: each row walks up, the bouncer evaluates the checklist against that one row, and only rows that get a clear "yes" are let in. "No" and "don't know" (which is what NULL produces, next topic) are both turned away.

SQL
SELECT id, name, price, stock
FROM products
WHERE price < 1000 AND stock > 10;
Result
+----+---------------------------------------+--------+-------+
| id | name                                  | price  | stock |
+----+---------------------------------------+--------+-------+
|  1 | ThinkBook 14                          | 899.00 |    12 |
|  3 | Pixel 10                              | 799.00 |    20 |
|  5 | SQL Performance Explained             |  39.00 |    50 |
|  6 | Designing Data-Intensive Applications |  49.00 |    35 |
|  7 | USB-C Charger                         |  29.00 |   100 |
+----+---------------------------------------+--------+-------+
5 rows in set (0.001 sec)
What’s happening
  1. FROM products produces the 8 products; WHERE tests each one.
  2. MacBook Air (1299) and iPhone 17 (1099) fail price < 1000. With AND, one false part is enough, so MySQL doesn't need to look at their stock.
  3. Noise-cancelling Headphones pass the price test (249) but fail stock > 10 (8 in stock).
  4. The other five pass both tests and are returned with the four selected columns.
  5. WHERE decides which rows, SELECT decides which columns. Note that price is compared as a DECIMAL, so 1000 is exact.
Spot the bug: laptops and phones under 1000

The goal is "laptops (category 2) or phones (category 3) that cost under 1000". What does this return?

SQL
SELECT id, name, category_id, price FROM products
WHERE category_id = 2 OR category_id = 3 AND price < 1000;
Show answer

It returns three rows, including the 1299 MacBook Air:

Result
+----+--------------+-------------+---------+
| id | name         | category_id | price   |
+----+--------------+-------------+---------+
|  1 | ThinkBook 14 |           2 |  899.00 |
|  2 | MacBook Air  |           2 | 1299.00 |
|  3 | Pixel 10     |           3 |  799.00 |
+----+--------------+-------------+---------+
What’s happening
  1. AND binds tighter than OR, just as * binds tighter than +. So the condition is read as category_id = 2 OR (category_id = 3 AND price < 1000).
  2. Every laptop passes the first half on its own, so the MacBook Air (category 2, 1299) is in, whatever its price.
  3. Phones must also be under 1000: the Pixel (799) is in, the iPhone (1099) is out.
  4. The fix is parentheses: WHERE (category_id = 2 OR category_id = 3) AND price < 1000, which returns only the ThinkBook and the Pixel. Or use category_id IN (2, 3) AND price < 1000, which can't be misread.
AdvancedBETWEEN, IN and the date-range trap
SQL
SELECT id, name, price FROM products WHERE price BETWEEN 39 AND 249;
SELECT id, name, city FROM customers WHERE city IN ('Toronto', 'London');
SELECT id, status, ordered_at FROM orders
WHERE ordered_at BETWEEN '2025-02-01' AND '2025-02-14';
SELECT id, status, ordered_at FROM orders
WHERE ordered_at >= '2025-02-01' AND ordered_at < '2025-02-15';
Result
+----+---------------------------------------+--------+
| id | name                                  | price  |
+----+---------------------------------------+--------+
|  5 | SQL Performance Explained             |  39.00 |
|  6 | Designing Data-Intensive Applications |  49.00 |
|  8 | Noise-cancelling Headphones           | 249.00 |
+----+---------------------------------------+--------+
+----+-------+---------+
| id | name  | city    |
+----+-------+---------+
|  1 | Rohit | Toronto |
|  3 | Liam  | London  |
|  5 | Noah  | Toronto |
+----+-------+---------+
+----+---------+---------------------+
| id | status  | ordered_at          |
+----+---------+---------------------+
|  1 | shipped | 2025-02-01 10:00:00 |
|  3 | shipped | 2025-02-10 09:20:00 |
+----+---------+---------------------+
+----+-----------+---------------------+
| id | status    | ordered_at          |
+----+-----------+---------------------+
|  1 | shipped   | 2025-02-01 10:00:00 |
|  3 | shipped   | 2025-02-10 09:20:00 |
|  4 | cancelled | 2025-02-14 18:00:00 |
+----+-----------+---------------------+
What’s happening
  1. BETWEEN 39 AND 249 is inclusive at both ends: it means price >= 39 AND price <= 249, so the 39.00 book and the 249.00 headphones are both in.
  2. IN ('Toronto', 'London') is shorthand for city = 'Toronto' OR city = 'London', and easier to read and extend.
  3. The third query wants "the first two weeks of February" but misses order 4, placed on 14 February at 18:00. ordered_at is a DATETIME, so the bare date '2025-02-14' is converted to 2025-02-14 00:00:00, and anything later that day is outside the range.
  4. The fourth query uses a half-open range, >= start AND < day after end, which includes the whole last day whatever the time, and works the same for DATE, DATETIME and TIMESTAMP columns. Prefer it for every date range.
AdvancedLIKE, REGEXP and collations

LIKE matches patterns: % is any run of characters (including none), _ is exactly one character. Whether 'a' equals 'A' is decided by the column's collation, and MySQL's default, utf8mb4_0900_ai_ci, is accent-insensitive and case-insensitive:

SQL
SELECT id, name FROM products WHERE name LIKE '%book%';
SELECT id, name FROM products WHERE name LIKE 'Pixel __';
SELECT id, name FROM products WHERE name REGEXP '^[A-Z]{3} ';
SELECT id, name FROM customers WHERE name = 'ROHIT';
SELECT id, name FROM customers WHERE name COLLATE utf8mb4_0900_as_cs = 'ROHIT';
SELECT 'Zoë' = 'zoe' AS accent_insensitive, 'abc' = 'ABC' AS case_insensitive;
Result
+----+--------------+
| id | name         |
+----+--------------+
|  1 | ThinkBook 14 |
|  2 | MacBook Air  |
+----+--------------+
+----+----------+
| id | name     |
+----+----------+
|  3 | Pixel 10 |
+----+----------+
+----+---------------------------+
| id | name                      |
+----+---------------------------+
|  5 | SQL Performance Explained |
+----+---------------------------+
+----+-------+
| id | name  |
+----+-------+
|  1 | Rohit |
+----+-------+
Empty set (0.000 sec)
+--------------------+------------------+
| accent_insensitive | case_insensitive |
+--------------------+------------------+
|                  1 |                1 |
+--------------------+------------------+
What’s happening
  1. '%book%' finds "Book" anywhere in the name, in ThinkBook and MacBook, even though the pattern is lowercase: the comparison uses the column's case-insensitive collation.
  2. 'Pixel __' needs exactly two characters after the space: "Pixel 10" matches; "Pixel 9" or "Pixel 10 Pro" wouldn't.
  3. REGEXP (also spelled RLIKE or REGEXP_LIKE()) uses ICU regular expressions since 8.0. ^[A-Z]{3} means "starts with three letters and a space"; only "SQL Performance Explained" fits. (The ThinkBook starts with five letters.)
  4. name = 'ROHIT' finds Rohit because the collation ignores case. Forcing a case- and accent-sensitive collation (utf8mb4_0900_as_cs) on the column makes the same comparison return nothing.
  5. 'Zoë' = 'zoe' is 1 too: accents are ignored as well. Great for search boxes, a surprise for usernames. The older trick name = BINARY 'ROHIT' still works on 9.7 but warns 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead.
AdvancedPitfall: loose comparisons and trailing spaces

#NULL and three-valued logic

Added

NULL is not a value; it's a marker for "unknown" or "missing". Sofia's city is NULL because we don't know it, not because she lives in a city called nothing. Every comparison with an unknown is itself unknown: is Sofia's city equal to Toronto? We can't say. So SQL logic has three truth values: TRUE, FALSE and UNKNOWN (which MySQL displays as NULL).

A good mental model is a sealed envelope. NULL is an envelope whose contents you haven't seen. "Is the envelope equal to 5?" Unknown. "Are these two sealed envelopes equal?" Also unknown, even if they look the same. The only question you can answer is "is it a sealed envelope?", which is what IS NULL asks. And WHERE only keeps rows whose condition is definitely TRUE.

SQL
SELECT id, name, city FROM customers WHERE city = NULL;
SELECT id, name, city FROM customers WHERE city IS NULL;
SELECT id, name, city FROM customers WHERE city <> 'Toronto';
Result
Empty set (0.000 sec)
+----+-------+------+
| id | name  | city |
+----+-------+------+
|  4 | Sofia | NULL |
+----+-------+------+
+----+------+--------+
| id | name | city   |
+----+------+--------+
|  2 | Maya | Mumbai |
|  3 | Liam | London |
|  6 | Zara | Dubai  |
+----+------+--------+
What’s happening
  1. city = NULL asks "is the city equal to unknown?" For every row the answer is UNKNOWN, even for Sofia, whose city is NULL. WHERE drops anything that isn't TRUE, so the result is empty. MySQL gives no error or warning; the query is valid, just useless.
  2. city IS NULL is the special test for the marker itself. It's always TRUE or FALSE, never unknown, and finds Sofia.
  3. city <> 'Toronto' returns Maya, Liam and Zara but not Sofia. For her the test is NULL <> 'Toronto', which is UNKNOWN, so she is dropped from "not Toronto" as well as from "Toronto". She's in neither half.
  4. To include her, say so: WHERE city <> 'Toronto' OR city IS NULL returns four rows. Forgetting this is one of the most common bugs in reporting queries.
AdvancedTruth tables for NULL, run on 9.7

The truth tables, run on 9.7:

SQL
SELECT NULL = NULL AS eq, NULL <> NULL AS neq, NULL IS NULL AS is_null,
       NULL <=> NULL AS null_safe, 1 <=> NULL AS one_ns;
SELECT NULL AND FALSE AS and_false, NULL AND TRUE AS and_true,
       NULL OR TRUE AS or_true, NULL OR FALSE AS or_false, NOT NULL AS not_null;
SELECT 5 + NULL AS plus, CONCAT('a', NULL) AS cat, NULL > 0 AS gt;
Result
+------+------+---------+-----------+--------+
| eq   | neq  | is_null | null_safe | one_ns |
+------+------+---------+-----------+--------+
| NULL | NULL |       1 |         1 |      0 |
+------+------+---------+-----------+--------+
+-----------+----------+---------+----------+----------+
| and_false | and_true | or_true | or_false | not_null |
+-----------+----------+---------+----------+----------+
|         0 |     NULL |       1 |     NULL |     NULL |
+-----------+----------+---------+----------+----------+
+------+------+------+
| plus | cat  | gt   |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
What’s happening
  1. NULL = NULL and NULL <> NULL are both NULL (unknown): two sealed envelopes can't be compared. IS NULL gives a definite 1.
  2. <=> is MySQL's null-safe equality: it treats two NULLs as equal (1) and NULL vs a value as not equal (0), never unknown. The standard spelling is IS NOT DISTINCT FROM, which PostgreSQL supports; MySQL 9.7 answers it with a 1064 syntax error.
  3. AND and OR only stay unknown when the known part doesn't settle it. NULL AND FALSE is 0, because one false makes AND false whatever the unknown is. NULL OR TRUE is 1 for the same reason. But NULL AND TRUE and NULL OR FALSE depend on the unknown, so they're NULL.
  4. NOT NULL (the expression, not the constraint) is still unknown: the opposite of "don't know" is "don't know".
  5. Arithmetic, string functions and comparisons all propagate NULL: 5 + NULL, CONCAT('a', NULL) and NULL > 0 are all NULL. One missing input makes the whole expression missing.
AdvancedReplacing NULLs: COALESCE, IFNULL and NULLIF
SQL
SELECT name, city,
       COALESCE(city, 'unknown') AS city_label,
       IFNULL(city, '-')         AS ifnull_city,
       NULLIF(city, 'Toronto')   AS nullif_toronto
FROM customers;
Result
+-------+---------+------------+-------------+----------------+
| name  | city    | city_label | ifnull_city | nullif_toronto |
+-------+---------+------------+-------------+----------------+
| Rohit | Toronto | Toronto    | Toronto     | NULL           |
| Maya  | Mumbai  | Mumbai     | Mumbai      | Mumbai         |
| Liam  | London  | London     | London      | London         |
| Sofia | NULL    | unknown    | -           | NULL           |
| Noah  | Toronto | Toronto    | Toronto     | NULL           |
| Zara  | Dubai   | Dubai      | Dubai       | Dubai          |
+-------+---------+------------+-------------+----------------+
What’s happening
  1. COALESCE(a, b, c, …) returns the first argument that isn't NULL. For Sofia city is NULL, so it falls through to 'unknown'; for everyone else it's just their city. It's standard SQL and takes any number of arguments.
  2. IFNULL(a, b) is MySQL's two-argument version: the same result here, with '-' as the fallback. Prefer COALESCE for portability.
  3. NULLIF(a, b) goes the other way: it returns NULL when the two are equal, otherwise a. Both Toronto customers become NULL. Its classic use is avoiding division by zero: total / NULLIF(count, 0) gives NULL instead of dividing by zero.
  4. Sofia's nullif_toronto is NULL because her city was already NULL; NULLIF(NULL, 'Toronto') returns its first argument.
NOT IN and a NULL

"Which employees manage nobody?" What does this return?

SQL
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);
Show answer

Nothing: Empty set. The six people who manage nobody (Chloe, Dev, Farid, Grace, Isla, Jonas) are all missing.

What’s happening
  1. The subquery returns every manager_id, and Asha's is NULL because she has no manager. So the list is (NULL, 1, 1, 2, 1, 5, 5, 1, 8, 1).
  2. id NOT IN (list) means id <> NULL AND id <> 1 AND id <> 2 AND …. For Chloe (id 3) every comparison with a real number is true, but 3 <> NULL is UNKNOWN.
  3. TRUE AND … AND UNKNOWN is UNKNOWN, so WHERE drops Chloe. The same happens to every row: one NULL in a NOT IN list makes the whole test unknown for every row.
  4. Fix it by filtering the NULL out (WHERE manager_id IS NOT NULL inside the subquery) or, better, use NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.id), which isn't affected by NULLs. Both return the six names; I ran both. NOT IN (1, 2, NULL) with a literal list has the same problem.
AdvancedNULLs in aggregates, sorting, DISTINCT and IN

NULL has a few more rules worth knowing, each covered where it applies:

  • COUNT(*) counts rows, COUNT(city) counts non-NULL values: on customers they give 6 and 5, and COUNT(DISTINCT city) gives 4 (Toronto, Mumbai, London, Dubai). SUM, AVG, MIN and MAX also skip NULLs (see COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)).
  • DISTINCT and GROUP BY treat all NULLs as one group, even though NULL = NULL is unknown.
  • ORDER BY puts NULLs first in ascending order in MySQL (see the next topics).
  • A UNIQUE index allows many NULLs, because no NULL is equal to another.
  • IN (3, NULL) still finds the 3s (3 = 3 is true, so the OR is true), but never finds the NULLs.

#Sorting with ORDER BY

Added

A table has no order, so a result has no order either, unless you ask for one with ORDER BY. It sorts by one or more expressions, each ASC (the default) or DESC, and it's the only thing that guarantees the order rows come back in. Everything else ("it's always been in id order") is an accident of the current plan.

Think of sorting a deck of cards: first by suit, and within each suit by rank. With several sort keys, the second key only matters among rows that tie on the first, the third only among rows that tie on the first two, and so on.

SQL
SELECT name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
Result
+-------+---------------+-----------+
| name  | department_id | salary    |
+-------+---------------+-----------+
| Jonas |          NULL |  91000.00 |
| Asha  |             1 | 185000.00 |
| Ben   |             1 | 142000.00 |
| Chloe |             1 | 142000.00 |
| Dev   |             1 | 118000.00 |
| Elena |             2 | 130000.00 |
| Grace |             2 |  99000.00 |
| Farid |             2 |  95000.00 |
| Hiro  |             3 |  88000.00 |
| Isla  |             3 |  72000.00 |
+-------+---------------+-----------+
10 rows in set (0.000 sec)
What’s happening
  1. The first key, department_id, is ascending (ASC is the default). Jonas's NULL department comes first: in MySQL, NULLs sort before every value in ascending order (and last in descending).
  2. Within department 1, the second key salary DESC puts Asha (185000) first and Dev (118000) last.
  3. Ben and Chloe tie on both keys (department 1, 142000). Their relative order is not defined: Ben came first here, but nothing promises that. If it matters, add a third key such as name or id.
  4. DESC applies only to the key it follows. ORDER BY department_id, salary DESC is not "everything descending".
AdvancedPutting NULLs first or last

MySQL has no NULLS FIRST / NULLS LAST clause (PostgreSQL and Oracle do). Sort on a NULL test first instead:

SQL
SELECT name, city FROM customers ORDER BY city;
SELECT name, city FROM customers ORDER BY city IS NULL, city;
Result
+-------+---------+
| name  | city    |
+-------+---------+
| Sofia | NULL    |
| Zara  | Dubai   |
| Liam  | London  |
| Maya  | Mumbai  |
| Rohit | Toronto |
| Noah  | Toronto |
+-------+---------+
+-------+---------+
| name  | city    |
+-------+---------+
| Zara  | Dubai   |
| Liam  | London  |
| Maya  | Mumbai  |
| Rohit | Toronto |
| Noah  | Toronto |
| Sofia | NULL    |
+-------+---------+
What’s happening
  1. Plain ORDER BY city puts Sofia's NULL first, then the cities alphabetically.
  2. city IS NULL evaluates to 0 for known cities and 1 for Sofia. Sorting by it first puts every 0 row before every 1 row.
  3. The second key city then sorts the known cities alphabetically. Sofia ends up last.
  4. Ordering by an expression like this can't use an index on city for the sort, which is fine for small results.
AdvancedORDER BY an alias, a position or an expression
SQL
SELECT name, price * stock AS stock_value FROM products ORDER BY stock_value DESC;
SELECT name, price FROM products ORDER BY 2 DESC LIMIT 3;
Result
+---------------------------------------+-------------+
| name                                  | stock_value |
+---------------------------------------+-------------+
| Pixel 10                              |    15980.00 |
| ThinkBook 14                          |    10788.00 |
| MacBook Air                           |     6495.00 |
| USB-C Charger                         |     2900.00 |
| Noise-cancelling Headphones           |     1992.00 |
| SQL Performance Explained             |     1950.00 |
| Designing Data-Intensive Applications |     1715.00 |
| iPhone 17                             |        0.00 |
+---------------------------------------+-------------+
+--------------+---------+
| name         | price   |
+--------------+---------+
| MacBook Air  | 1299.00 |
| iPhone 17    | 1099.00 |
| ThinkBook 14 |  899.00 |
+--------------+---------+
What’s happening
  1. ORDER BY runs after SELECT, so it can use the alias stock_value (unlike WHERE, see Aliases and computed columns). The Pixel's 20 × 799 = 15980 is the most money sitting on the shelf.
  2. ORDER BY 2 means "the second column in the select list", here price. It's handy at the prompt, but in code it silently changes meaning when someone reorders the columns; prefer names.
  3. You can also order by a column you didn't select, such as ORDER BY created_at while selecting only name, as long as the query doesn't use DISTINCT.
Why is "pending" first?
SQL
SELECT id, status FROM orders ORDER BY status;

The statuses are pending, paid, shipped and cancelled. Alphabetically cancelled would come first. What comes first here?

Show answer

pending, then paid, shipped, and cancelled last:

Result
+----+-----------+
| id | status    |
+----+-----------+
|  6 | pending   |
|  2 | paid      |
|  7 | paid      |
|  1 | shipped   |
|  3 | shipped   |
|  5 | shipped   |
|  8 | shipped   |
|  4 | cancelled |
+----+-----------+
What’s happening
  1. status is an ENUM('pending','paid','shipped','cancelled'). Internally MySQL stores each value as its position in the list: pending = 1, paid = 2, shipped = 3, cancelled = 4.
  2. ORDER BY status sorts by those numbers, not by the text. That's why the order matches the ENUM definition.
  3. To sort alphabetically, sort the text: ORDER BY CAST(status AS CHAR) returns cancelled, paid, paid, pending, shipped… (I ran it). To sort in a business order of your own choosing, use a CASE expression (see CASE expressions).
  4. This is one of several ENUM surprises; ENUM, SET and BOOLEAN has the rest.

#LIMIT and OFFSET

Added

LIMIT n returns at most n rows; LIMIT n OFFSET m skips m rows first. They're applied last, after sorting, which is why LIMIT almost always comes with ORDER BY: "the top 3" only means something once you've said top by what.

Picture the sorted result as a long receipt. OFFSET is how far down you slide your finger; LIMIT is how many lines you then read out. The database still has to produce (and usually sort) everything above your finger, which is why big offsets get slow.

SQL
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;
Result
+--------------+---------+
| name         | price   |
+--------------+---------+
| MacBook Air  | 1299.00 |
| iPhone 17    | 1099.00 |
| ThinkBook 14 |  899.00 |
+--------------+---------+
3 rows in set (0.000 sec)
What’s happening
  1. ORDER BY price DESC sorts all 8 products from most to least expensive.
  2. LIMIT 3 keeps the first three rows of that sorted result: MacBook Air, iPhone 17, ThinkBook 14.
  3. MySQL can be smart about this: with a LIMIT, it doesn't fully sort all rows, it keeps only the best 3 seen so far (a "top-N" priority queue), and with an index on price it could read just three index entries and stop.
  4. Without the ORDER BY, LIMIT 3 would return "some three rows", whichever the plan produced first.
AdvancedPages with OFFSET, and the LIMIT m, n syntax
SQL
SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 3;
SELECT name, price FROM products ORDER BY price DESC LIMIT 3, 3;
SELECT name, price FROM products ORDER BY price DESC LIMIT 100 OFFSET 6;
Result
+---------------------------------------+--------+
| name                                  | price  |
+---------------------------------------+--------+
| Pixel 10                              | 799.00 |
| Noise-cancelling Headphones           | 249.00 |
| Designing Data-Intensive Applications |  49.00 |
+---------------------------------------+--------+
+---------------------------------------+--------+
| name                                  | price  |
+---------------------------------------+--------+
| Pixel 10                              | 799.00 |
| Noise-cancelling Headphones           | 249.00 |
| Designing Data-Intensive Applications |  49.00 |
+---------------------------------------+--------+
+---------------------------+-------+
| name                      | price |
+---------------------------+-------+
| SQL Performance Explained | 39.00 |
| USB-C Charger             | 29.00 |
+---------------------------+-------+
What’s happening
  1. Page 2 with 3 per page skips 3 rows and takes the next 3: Pixel, Headphones, the 49.00 book. In general, page p (from 1) is LIMIT size OFFSET (p - 1) * size.
  2. LIMIT 3, 3 is MySQL's older form, offset first, then count. It's the same query and a common source of off-by-a-page bugs, because the order is the reverse of LIMIT … OFFSET ….
  3. Asking for more rows than remain isn't an error: LIMIT 100 OFFSET 6 just returns the last two products.
  4. Large offsets are slow because the server must produce and throw away every skipped row: OFFSET 100000 reads 100,000 rows to return 20. Keyset pagination (WHERE price < last_seen_price …) avoids that; see Pagination: OFFSET vs keyset.
AdvancedWhat MySQL's LIMIT doesn't support
SQL
SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
SELECT name FROM products
WHERE id IN (SELECT product_id FROM order_items ORDER BY quantity DESC LIMIT 2);
SELECT name FROM products
WHERE id IN (SELECT product_id FROM
             (SELECT product_id FROM order_items ORDER BY quantity DESC LIMIT 2) AS top2);
Result
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FETCH FIRST 3 ROWS ONLY' at line 1
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+---------------------------+
| name                      |
+---------------------------+
| USB-C Charger             |
| SQL Performance Explained |
+---------------------------+
What’s happening
  1. FETCH FIRST n ROWS ONLY is the SQL-standard spelling of LIMIT, used by PostgreSQL, Oracle and SQL Server. MySQL 9.7 still doesn't accept it.
  2. MySQL refuses LIMIT inside a subquery used with IN, ALL, ANY or SOME, with the oddly worded error 1235 that has said "doesn't yet support" for many versions.
  3. The workaround is to wrap the limited query in a derived table (AS top2). The inner query becomes a materialised result, and IN reads from that.
  4. The two largest quantities are product 7 (4 chargers in order 8) and product 5 (3 books in order 7), so those two names come back.
Second-highest salary

A classic. What do these return?

SQL
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
Show answer

The first returns Chloe, 142000.00; the second returns 130000.00.

Result
+-------+-----------+
| name  | salary    |
+-------+-----------+
| Chloe | 142000.00 |
+-------+-----------+
+-----------+
| salary    |
+-----------+
| 130000.00 |
+-----------+
What’s happening
  1. Sorted by salary, the rows are Asha 185000, Ben 142000, Chloe 142000, Elena 130000, … Skipping two rows lands on Chloe, so the first query says the "third-highest salary" is 142000, which is really the second-highest again. (Whether Ben or Chloe is second is itself undefined.)
  2. DISTINCT removes duplicate salaries before ORDER BY and LIMIT: the distinct list is 185000, 142000, 130000, … Skipping two gives 130000, the true third-highest salary.
  3. Which one is right depends on the question: "the third-highest-paid person" or "the third-highest salary". Interviewers ask this to see whether you notice ties.
  4. DENSE_RANK() handles it more clearly and extends to "per department"; see ROW_NUMBER, RANK and DENSE_RANK.

#DISTINCT

Added

SELECT DISTINCT removes duplicate rows from the result: if two rows have the same values in every selected column, only one is kept. It's applied after the select list is computed and before ORDER BY and LIMIT.

Imagine tipping a bag of coloured beads onto the table and keeping one of each colour. The important detail is what "colour" means: DISTINCT compares the whole selected row, not just the first column. Select two columns and it keeps one of each combination.

SQL
SELECT DISTINCT city FROM customers;
Result
+---------+
| city    |
+---------+
| Toronto |
| Mumbai  |
| London  |
| NULL    |
| Dubai   |
+---------+
5 rows in set (0.000 sec)
What’s happening
  1. There are six customers but only five rows: Rohit and Noah both have Toronto, so one Toronto row is dropped.
  2. Sofia's NULL city is kept as a row of its own. For DISTINCT (and GROUP BY) all NULLs count as the same, even though NULL = NULL is unknown in a WHERE.
  3. The order isn't sorted. EXPLAIN FORMAT=TREE shows the plan: Table scan on customers, then Temporary table with deduplication, then a scan of that temporary table, so rows come out in the order each city was first seen. Add ORDER BY city if you want them sorted.
  4. Contrast COUNT(DISTINCT city), which is 4: aggregate functions skip NULLs, so the NULL "city" isn't counted.
AdvancedDISTINCT applies to the whole row
SQL
SELECT DISTINCT city, name FROM customers WHERE city = 'Toronto';
SELECT DISTINCT department_id, salary FROM employees WHERE department_id = 1;
Result
+---------+-------+
| city    | name  |
+---------+-------+
| Toronto | Rohit |
| Toronto | Noah  |
+---------+-------+
+---------------+-----------+
| department_id | salary    |
+---------------+-----------+
|             1 | 185000.00 |
|             1 | 142000.00 |
|             1 | 118000.00 |
+---------------+-----------+
What’s happening
  1. DISTINCT city, name doesn't mean "distinct cities, plus a name". Rohit and Noah differ in name, so both rows stay, and Toronto appears twice.
  2. In department 1 there are four people but only three distinct (department_id, salary) pairs, because Ben and Chloe share (1, 142000.00).
  3. DISTINCT is not a function, so SELECT DISTINCT(city), name is the same as SELECT DISTINCT city, name. The parentheses just wrap the first column.
AdvancedError 3065: ORDER BY a column not selected
SQL
SELECT DISTINCT city FROM customers ORDER BY created_at;
Result
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'ch01.customers.created_at' which is not in SELECT list; this is incompatible with DISTINCT
What’s happening
  1. After DISTINCT, Toronto is one row standing for two customers with two different created_at values. Which one should it sort by? There's no answer.
  2. MySQL 5.7.5 and later refuse the query instead of guessing. (Older versions picked an arbitrary row.)
  3. Either select the column too, or sort by an aggregate after grouping: SELECT city FROM customers GROUP BY city ORDER BY MIN(created_at) sorts each city by its first sign-up.
AdvancedDISTINCT vs GROUP BY, and DISTINCT as a smell

SELECT DISTINCT city FROM customers and SELECT city FROM customers GROUP BY city return the same five rows (I ran both), and MySQL often runs them with the same plan. Use DISTINCT when you only want unique rows and GROUP BY when you want to compute something per group (COUNT(*), SUM(…)).

A DISTINCT added to "fix" duplicate rows is often a smell: the duplicates usually come from a join that multiplies rows (one customer × many orders), and the honest fix is to change the join, for example to an EXISTS (see Anti-joins and semi-joins). DISTINCT also has a cost: it needs a temporary table or a sort over the whole result.

#Aliases and computed columns

Added

The select list isn't limited to stored columns: any expression can be a result column, such as quantity * unit_price or ROUND(price * 0.9, 2). An alias (AS name) gives that column a name. Tables get aliases too (FROM order_items AS oi), which keeps joins readable and is required when you join a table to itself.

Think of the result as a report you're designing: the stored columns are the raw figures, computed columns are the formulas in the extra columns, and aliases are the headings you write at the top. The underlying table never gets those columns; they exist only in this result.

SQL
SELECT oi.order_id,
       p.name                         AS product,
       oi.quantity * oi.unit_price    AS line_total,
       ROUND(oi.unit_price * 0.9, 2)  AS member_price
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id IN (1, 7)
ORDER BY line_total DESC;
Result
+----------+---------------------------+------------+--------------+
| order_id | product                   | line_total | member_price |
+----------+---------------------------+------------+--------------+
|        1 | ThinkBook 14              |     899.00 |       809.10 |
|        7 | ThinkBook 14              |     899.00 |       809.10 |
|        7 | SQL Performance Explained |     117.00 |        35.10 |
|        1 | USB-C Charger             |      58.00 |        26.10 |
+----------+---------------------------+------------+--------------+
4 rows in set (0.000 sec)
What’s happening
  1. order_items AS oi and products AS p are table aliases. After the FROM clause, the tables are called oi and p; oi.quantity says exactly which table's column you mean.
  2. For each of the four order lines in orders 1 and 7, MySQL computes quantity * unit_price: 1 × 899.00 = 899.00, 3 × 39.00 = 117.00, 2 × 29.00 = 58.00. The result keeps the DECIMAL scale of 2.
  3. ROUND(unit_price * 0.9, 2) computes a 10% discount, 899.00 × 0.9 = 809.100, rounded to 809.10. Without ROUND, multiplying by 0.9 adds a decimal place (809.100).
  4. ORDER BY line_total DESC can use the alias because ORDER BY is evaluated after the select list. The two ThinkBook lines tie at 899.00, so their order is undefined.
  5. AS is optional (p.name product works), but always write it: see the quiz below for what goes wrong without it.
AdvancedError 1054: aliases don't exist in WHERE
SQL
SELECT name, price * stock AS stock_value FROM products WHERE stock_value > 5000;
SELECT name, price * stock AS stock_value FROM products HAVING stock_value > 5000;
Result
ERROR 1054 (42S22): Unknown column 'stock_value' in 'where clause'
+--------------+-------------+
| name         | stock_value |
+--------------+-------------+
| ThinkBook 14 |    10788.00 |
| MacBook Air  |     6495.00 |
| Pixel 10     |    15980.00 |
+--------------+-------------+
What’s happening
  1. WHERE is evaluated before the select list (see Logical query processing order), so when it runs, stock_value hasn't been computed and has no name. Error 1054 says so.
  2. The portable fix is to repeat the expression: WHERE price * stock > 5000. Or compute it in a derived table or CTE and filter outside.
  3. MySQL also lets HAVING refer to select-list aliases, and with no GROUP BY and no aggregates it simply filters the finished rows, so the second query works. It's a MySQL extension (PostgreSQL 18 rejects an alias in HAVING with ERROR: column "n" does not exist in my test) and it confuses readers; prefer repeating the expression in WHERE.
A missing comma
SQL
SELECT name price FROM products WHERE id = 1;

How many columns come back, and what are they called?

Show answer

One column, called price, containing the product name:

Result
+--------------+
| price        |
+--------------+
| ThinkBook 14 |
+--------------+
What’s happening
  1. Without a comma, name price isn't two columns. It's the column name followed by an alias, because AS is optional.
  2. So MySQL returns name and calls it price. No error, no warning.
  3. Code that reads row.price now gets 'ThinkBook 14'. Always writing AS for aliases makes this mistake stand out in review.
AdvancedArithmetic: division, DECIMAL and floating point
SQL
SELECT 7 / 2 AS divide, 7 DIV 2 AS int_divide, 7 % 2 AS modulo, 7 / 0 AS by_zero;
SELECT 0.1 + 0.2 = 0.3 AS decimal_math, 0.1e0 + 0.2e0 = 0.3e0 AS float_math,
       0.1e0 + 0.2e0 AS float_sum;
SELECT 1 / 3 AS one_third, 1 / 3 * 3 AS back_again;
Result
+--------+------------+--------+---------+
| divide | int_divide | modulo | by_zero |
+--------+------------+--------+---------+
| 3.5000 |          3 |      1 |    NULL |
+--------+------------+--------+---------+
+--------------+------------+---------------------+
| decimal_math | float_math | float_sum           |
+--------------+------------+---------------------+
|            1 |          0 | 0.30000000000000004 |
+--------------+------------+---------------------+
+-----------+------------+
| one_third | back_again |
+-----------+------------+
|    0.3333 |     0.9999 |
+-----------+------------+
What’s happening
  1. / always does real division in MySQL: 7 / 2 is 3.5000 (four extra decimal places, set by div_precision_increment), not 3 as in PostgreSQL or SQL Server integer division. DIV is integer division; % or MOD() is the remainder.
  2. Dividing by zero gives NULL with Warning 1365 Division by 0. In an INSERT or UPDATE under the default strict SQL mode it's an error instead: inserting 7 / 0 into a DECIMAL column failed with ERROR 1365 (22012): Division by 0.
  3. Literals like 0.1 are exact DECIMALs, so 0.1 + 0.2 = 0.3 is true. Literals with an exponent (0.1e0) are DOUBLEs, and binary floating point can't represent 0.1 exactly: the sum is 0.30000000000000004, just like JavaScript. Store money in DECIMAL, never FLOAT or DOUBLE.
  4. 1 / 3 is rounded to four places (0.3333), and multiplying back gives 0.9999, not 1. When precision matters, multiply before you divide, and round once at the end.

#CASE expressions

Added

CASE is SQL's if/else, and it's an expression: it produces a value, so it can go anywhere a value can, in the select list, WHERE, ORDER BY, GROUP BY, inside SUM(), in an UPDATE's SET. It comes in two forms. The searched form tests a list of conditions, CASE WHEN cond THEN result … ELSE default END. The simple form compares one expression against values, CASE expr WHEN value THEN result … END.

Think of it as a sorting machine with chutes: each row rolls past the WHEN tests in order and drops into the first chute whose test is true. If it passes every chute, it lands in ELSE, and if there's no ELSE, it lands on the floor as NULL.

SQL
SELECT name, price,
       CASE
         WHEN price >= 1000 THEN 'premium'
         WHEN price >= 200  THEN 'mid-range'
         ELSE 'budget'
       END AS price_band
FROM products
ORDER BY price DESC;
Result
+---------------------------------------+---------+------------+
| name                                  | price   | price_band |
+---------------------------------------+---------+------------+
| MacBook Air                           | 1299.00 | premium    |
| iPhone 17                             | 1099.00 | premium    |
| ThinkBook 14                          |  899.00 | mid-range  |
| Pixel 10                              |  799.00 | mid-range  |
| Noise-cancelling Headphones           |  249.00 | mid-range  |
| Designing Data-Intensive Applications |   49.00 | budget     |
| SQL Performance Explained             |   39.00 | budget     |
| USB-C Charger                         |   29.00 | budget     |
+---------------------------------------+---------+------------+
8 rows in set (0.000 sec)
What’s happening
  1. For each product, MySQL evaluates the WHEN conditions top to bottom and stops at the first one that's true.
  2. MacBook Air: 1299 >= 1000 is true → 'premium'. The second WHEN is never looked at.
  3. ThinkBook: the first test fails (899 >= 1000 is false), the second passes (899 >= 200) → 'mid-range'. Because the first test already failed, the second doesn't need AND price < 1000: the order of the WHENs does that work.
  4. The books and the charger fail both tests and fall to ELSE 'budget'.
  5. END AS price_band closes the expression and names the column. The CASE is computed per row; nothing is stored.
AdvancedSimple CASE and the missing ELSE
SQL
SELECT id, status,
       CASE status
         WHEN 'pending' THEN 'Awaiting payment'
         WHEN 'paid'    THEN 'Preparing'
         WHEN 'shipped' THEN 'On its way'
       END AS customer_message
FROM orders ORDER BY id;
Result
+----+-----------+------------------+
| id | status    | customer_message |
+----+-----------+------------------+
|  1 | shipped   | On its way       |
|  2 | paid      | Preparing        |
|  3 | shipped   | On its way       |
|  4 | cancelled | NULL             |
|  5 | shipped   | On its way       |
|  6 | pending   | Awaiting payment |
|  7 | paid      | Preparing        |
|  8 | shipped   | On its way       |
+----+-----------+------------------+
What’s happening
  1. The simple form CASE status WHEN 'pending' … compares status = 'pending', then status = 'paid', and so on. It's shorter when every test is an equality on the same expression.
  2. Order 4 is cancelled, which no WHEN mentions, and there's no ELSE, so the result is NULL.
  3. That's sometimes what you want, but more often a bug waiting for a new status. Add ELSE 'Unknown status' (or ELSE status) so new values show up visibly.
AdvancedPitfall: CASE WHEN NULL never matches
First match wins
SQL
SELECT name, price,
       CASE WHEN price > 100  THEN 'over 100'
            WHEN price > 1000 THEN 'over 1000'
       END AS band
FROM products WHERE id IN (2, 7);

What's the MacBook Air's band (price 1299), and the charger's (29)?

Show answer

MacBook Air is over 100, and the charger is NULL.

Result
+---------------+---------+----------+
| name          | price   | band     |
+---------------+---------+----------+
| MacBook Air   | 1299.00 | over 100 |
| USB-C Charger |   29.00 | NULL     |
+---------------+---------+----------+
What’s happening
  1. For the MacBook, the first test 1299 > 100 is already true, so CASE stops there. The > 1000 branch can never be reached by anything, because anything over 1000 is also over 100.
  2. The charger fails both tests, and with no ELSE the result is NULL.
  3. Put the most specific (narrowest) condition first: WHEN price > 1000 before WHEN price > 100.
AdvancedCASE in ORDER BY: a custom sort order

Suppose the warehouse wants paid orders first (they need packing), then pending, then shipped, with cancelled last, and the oldest first within each:

SQL
SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'paid'    THEN 1
           WHEN 'pending' THEN 2
           WHEN 'shipped' THEN 3
           ELSE 4
         END,
         ordered_at;
Result
+----+-----------+
| id | status    |
+----+-----------+
|  2 | paid      |
|  7 | paid      |
|  6 | pending   |
|  1 | shipped   |
|  3 | shipped   |
|  5 | shipped   |
|  8 | shipped   |
|  4 | cancelled |
+----+-----------+
What’s happening
  1. The CASE turns each status into a sort key: paid → 1, pending → 2, shipped → 3, anything else → 4.
  2. ORDER BY sorts on that number, so both paid orders come first, then the one pending, the four shipped and finally the cancelled one.
  3. Within a status, ordered_at sorts oldest first: order 2 (15 March) before 7 (2 April), and shipped orders 1, 3, 5, 8 in date order.
  4. The sort key isn't in the select list and doesn't need to be. MySQL's FIELD(status, 'paid', 'pending', 'shipped') does the same job in one call, but returns 0 for unlisted values, which sorts them first.
AdvancedIF(), and CASE inside calculations
SQL
SELECT name, IF(stock = 0, 'sold out', 'in stock') AS availability
FROM products WHERE id IN (3, 4);
SELECT name, salary,
       CASE department_id WHEN 1 THEN salary * 1.10
                          WHEN 2 THEN salary * 1.05
                          ELSE salary END AS new_salary
FROM employees WHERE id IN (2, 6, 8, 10);
SELECT CASE WHEN 1 = 1 THEN 1 ELSE 'one' END AS mixed;
Result
+-----------+--------------+
| name      | availability |
+-----------+--------------+
| Pixel 10  | in stock     |
| iPhone 17 | sold out     |
+-----------+--------------+
+-------+-----------+-------------+
| name  | salary    | new_salary  |
+-------+-----------+-------------+
| Ben   | 142000.00 | 156200.0000 |
| Farid |  95000.00 |  99750.0000 |
| Hiro  |  88000.00 |    88000.00 |
| Jonas |  91000.00 |    91000.00 |
+-------+-----------+-------------+
+-------+
| mixed |
+-------+
| 1     |
+-------+
What’s happening
  1. IF(cond, a, b) is MySQL's two-way shorthand for CASE WHEN cond THEN a ELSE b END. It's not standard SQL (and don't confuse it with the IF statement in stored procedures).
  2. The raise: engineering (1) gets 10%, sales (2) 5%, everyone else unchanged. Jonas's NULL department matches no WHEN and goes to ELSE salary.
  3. Notice the display: 156200.0000 has four decimals while 88000.00 has two. The column's type is computed from all branches (salary * 1.10 is DECIMAL(…,4)), and each value is shown as it was produced.
  4. Mixing types in branches forces a common type: THEN 1 ELSE 'one' makes the whole CASE a string, which is why 1 is printed left-aligned like text. Keep every branch the same type.

#String, number and date functions

Added

MySQL has hundreds of built-in functions, and interviews and day-to-day queries use a few dozen of them over and over: building and cutting strings, rounding numbers, and doing calendar arithmetic. They all work per row (unlike aggregates such as SUM, which work per group), and most of them return NULL if any argument is NULL.

The mental model is a toolbox you apply to each row as it passes. One rule matters more than any individual function: wrapping a column in a function in WHERE usually stops MySQL from using an index on that column, so put functions on the constant side of a comparison where you can.

SQL
SELECT name,
       UPPER(name)                        AS shout,
       SUBSTRING_INDEX(email, '@', -1)    AS domain,
       CONCAT(name, ' <', email, '>')     AS display,
       DATE_FORMAT(created_at, '%b %Y')   AS joined,
       DATEDIFF('2025-12-31', created_at) AS days_to_year_end
FROM customers
WHERE id <= 3;
Result
+-------+-------+-------------+---------------------------+----------+------------------+
| name  | shout | domain      | display                   | joined   | days_to_year_end |
+-------+-------+-------------+---------------------------+----------+------------------+
| Rohit | ROHIT | example.com | Rohit <rohit@example.com> | Jan 2025 |              360 |
| Maya  | MAYA  | example.com | Maya <maya@example.com>   | Jan 2025 |              345 |
| Liam  | LIAM  | example.com | Liam <liam@example.com>   | Feb 2025 |              323 |
+-------+-------+-------------+---------------------------+----------+------------------+
3 rows in set (0.001 sec)
What’s happening
  1. UPPER(name) returns an upper-case copy; the stored value is unchanged.
  2. SUBSTRING_INDEX(email, '@', -1) returns everything after the last @. A positive count takes from the left (SUBSTRING_INDEX(email, '@', 1) is rohit), a negative one from the right. It's the go-to function for splitting on a delimiter.
  3. CONCAT joins its arguments into one string. It's the standard way to concatenate in MySQL, because || means OR here (see the last example).
  4. DATE_FORMAT(created_at, '%b %Y') formats a date with % codes: %b is the abbreviated month, %Y the four-digit year. %d is the day and %H:%i the time.
  5. DATEDIFF(a, b) counts days from b to a, ignoring the time part. Rohit joined on 5 January, 360 days before 31 December.
AdvancedMore string functions
SQL
SELECT CONCAT('City: ', city) AS concat_null, CONCAT_WS(', ', name, city) AS concat_ws
FROM customers WHERE id IN (3, 4);
SELECT LENGTH('café') AS bytes, CHAR_LENGTH('café') AS chars,
       LENGTH('🙂') AS emoji_bytes, CHAR_LENGTH('🙂') AS emoji_chars;
SELECT SUBSTRING('ThinkBook 14', 1, 5) AS first5, SUBSTRING('ThinkBook 14', -2) AS last2,
       LEFT('MacBook Air', 3) AS left3, REPLACE('USB-C Charger', '-', ' ') AS replaced,
       TRIM('  hi  ') AS trimmed, LPAD('7', 4, '0') AS padded,
       LOCATE('Book', 'ThinkBook 14') AS pos, REVERSE('abc') AS rev;
Result
+--------------+--------------+
| concat_null  | concat_ws    |
+--------------+--------------+
| City: London | Liam, London |
| NULL         | Sofia        |
+--------------+--------------+
+-------+-------+-------------+-------------+
| bytes | chars | emoji_bytes | emoji_chars |
+-------+-------+-------------+-------------+
|     5 |     4 |           4 |           1 |
+-------+-------+-------------+-------------+
+--------+-------+-------+---------------+---------+--------+-----+------+
| first5 | last2 | left3 | replaced      | trimmed | padded | pos | rev  |
+--------+-------+-------+---------------+---------+--------+-----+------+
| Think  | 14    | Mac   | USB C Charger | hi      | 0007   |   6 | cba  |
+--------+-------+-------+---------------+---------+--------+-----+------+
What’s happening
  1. CONCAT returns NULL if any argument is NULL, so Sofia's label disappears entirely. CONCAT_WS(separator, …) ("with separator") skips NULL arguments, giving just Sofia. Use it for addresses and full names with optional parts.
  2. LENGTH counts bytes and CHAR_LENGTH counts characters. In utf8mb4, é takes 2 bytes, so café is 5 bytes but 4 characters, and the emoji takes 4 bytes. VARCHAR(n) limits are in characters; index-size limits are in bytes.
  3. SQL strings are 1-based: SUBSTRING(s, 1, 5) is the first five characters. A negative start counts from the end, so SUBSTRING(s, -2) is the last two.
  4. LOCATE('Book', 'ThinkBook 14') returns 6, the 1-based position, or 0 if not found (there's no -1 as in JavaScript). LPAD pads on the left to a length, good for fixed-width codes.
  5. I ran these with the client set to utf8mb4. With the container's default latin1 client character set, the same query sends mangled bytes and the counts come out wrong, which is the kind of bug that only shows up with non-English data.
AdvancedRounding and number functions
SQL
SELECT ROUND(2.5) AS r1, ROUND(3.5) AS r2, ROUND(-2.5) AS r3, ROUND(2.5e0) AS r_float,
       ROUND(1234.567, -2) AS r_tens, TRUNCATE(1234.567, 1) AS trunc,
       CEIL(4.1) AS ceil, FLOOR(-4.1) AS floor, MOD(10, 3) AS mod3, ABS(-5) AS abs,
       POWER(2, 10) AS pow;
Result
+----+----+----+---------+--------+--------+------+-------+------+-----+------+
| r1 | r2 | r3 | r_float | r_tens | trunc  | ceil | floor | mod3 | abs | pow  |
+----+----+----+---------+--------+--------+------+-------+------+-----+------+
|  3 |  4 | -3 |       2 |   1200 | 1234.5 |    5 |    -5 |    1 |   5 | 1024 |
+----+----+----+---------+--------+--------+------+-------+------+-----+------+
What’s happening
  1. For exact values (DECIMAL and integer literals), ROUND rounds half away from zero: 2.5 → 3, 3.5 → 4, -2.5 → -3.
  2. For floating-point values it uses the C library, which rounds half to even: ROUND(2.5e0) is 2. The same-looking number rounds differently depending on its type, one more reason to keep money in DECIMAL.
  3. A negative number of places rounds to the left of the point: ROUND(1234.567, -2) is 1200. TRUNCATE just cuts digits off without rounding (1234.5).
  4. CEIL rounds up and FLOOR rounds down, towards positive and negative infinity: FLOOR(-4.1) is -5, not -4.
AdvancedDate and time functions
SQL
SELECT id, ordered_at,
       DATE(ordered_at) AS just_date, YEAR(ordered_at) AS yr,
       MONTHNAME(ordered_at) AS mon, DAYNAME(ordered_at) AS dow,
       DATE_ADD(ordered_at, INTERVAL 7 DAY) AS plus_week, LAST_DAY(ordered_at) AS month_end
FROM orders WHERE id IN (1, 6);
SELECT TIMESTAMPDIFF(MONTH, '2025-01-31', '2025-02-28') AS months,
       TIMESTAMPDIFF(DAY, '2025-01-31', '2025-02-28') AS days,
       DATEDIFF('2025-03-01', '2025-02-28') AS feb_days,
       '2025-01-31' + INTERVAL 1 MONTH AS jan31_plus_month;
SELECT name, hired_on, TIMESTAMPDIFF(YEAR, hired_on, '2026-10-16') AS years_service
FROM employees WHERE id IN (1, 9, 10);
SELECT STR_TO_DATE('16/10/2026', '%d/%m/%Y') AS parsed,
       CAST('2026-10-16' AS DATE) + INTERVAL 1 DAY AS tomorrow;
Result
+----+---------------------+------------+------+----------+----------+---------------------+------------+
| id | ordered_at          | just_date  | yr   | mon      | dow      | plus_week           | month_end  |
+----+---------------------+------------+------+----------+----------+---------------------+------------+
|  1 | 2025-02-01 10:00:00 | 2025-02-01 | 2025 | February | Saturday | 2025-02-08 10:00:00 | 2025-02-28 |
|  6 | 2025-03-20 21:10:00 | 2025-03-20 | 2025 | March    | Thursday | 2025-03-27 21:10:00 | 2025-03-31 |
+----+---------------------+------------+------+----------+----------+---------------------+------------+
+--------+------+----------+------------------+
| months | days | feb_days | jan31_plus_month |
+--------+------+----------+------------------+
|      0 |   28 |        1 | 2025-02-28       |
+--------+------+----------+------------------+
+-------+------------+---------------+
| name  | hired_on   | years_service |
+-------+------------+---------------+
| Asha  | 2018-03-01 |             8 |
| Isla  | 2024-04-01 |             2 |
| Jonas | 2025-06-16 |             1 |
+-------+------------+---------------+
+------------+------------+
| parsed     | tomorrow   |
+------------+------------+
| 2026-10-16 | 2026-10-17 |
+------------+------------+
What’s happening
  1. DATE() drops the time, YEAR(), MONTHNAME() and DAYNAME() pull out parts (EXTRACT(YEAR FROM …) is the standard form), and LAST_DAY() gives the last day of the month, handy for monthly reports.
  2. DATE_ADD(d, INTERVAL 7 DAY) (or d + INTERVAL 7 DAY) does calendar arithmetic and keeps the time.
  3. TIMESTAMPDIFF(unit, start, end) counts complete units: from 31 January to 28 February is 28 days but 0 months, because a full month would reach 31 February, which doesn't exist. Adding a month to 31 January, on the other hand, clamps to the last valid day, 2025-02-28.
  4. That "complete units" rule is why TIMESTAMPDIFF(YEAR, hired_on, today) is the right way to compute years of service or age: Isla, hired 1 April 2024, has 2 complete years on 16 October 2026.
  5. STR_TO_DATE parses text in a given format (the inverse of DATE_FORMAT), and CAST(… AS DATE) converts a standard YYYY-MM-DD string. NOW() returns the time the statement started (the same value for every row); SYSDATE() returns the time it's called.
AdvancedPitfall: functions on columns hide indexes
What do these return?
SQL
SELECT '5' + 3 AS str_plus, CONCAT(5, 3) AS concat_num, '5' || '3' AS pipes;
Show answer

8, 53 and 1, plus a warning:

Result
+----------+------------+-------+
| str_plus | concat_num | pipes |
+----------+------------+-------+
|        8 | 53         |     1 |
+----------+------------+-------+
1 row in set, 1 warning (0.000 sec)
What’s happening
  1. + is always arithmetic in MySQL, never concatenation, so the string '5' is converted to the number 5 and 5 + 3 = 8.
  2. CONCAT always produces a string, converting numbers to text: '5' and '3' join to '53' (shown left-aligned, as text).
  3. || in MySQL is logical OR by default, not concatenation as in standard SQL, PostgreSQL and Oracle. '5' and '3' are both non-zero, so '5' OR '3' is 1.
  4. The warning is 1287 '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead. The PIPES_AS_CONCAT SQL mode switches || to concatenation; otherwise use CONCAT.

#Logical query processing order

Added

You write a query in the order SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but SQL defines its meaning in a different order: FROM (and joins) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Each step takes the previous step's result and produces a new intermediate table. This "logical order" explains almost every "why can't I…" question about SQL.

Picture an assembly line. Raw rows enter at FROM; WHERE throws out individual rows; GROUP BY packs the survivors into boxes; HAVING throws out whole boxes; SELECT writes the label on each remaining row or box; DISTINCT removes identical labels; ORDER BY lines them up; and LIMIT takes the first few off the end of the belt. A station can only use what earlier stations have produced. (The optimizer is free to execute things in another order, such as pushing a filter into a join, as long as the result is the same.)

Written order: SELECT · FROM · WHERE · GROUP BY · HAVING · ORDER BY · LIMIT 1 FROM / JOIN build the rows 2 WHERE filter rows 3 GROUP BY form groups 4 HAVING filter groups 5 SELECT compute, name aliases 6 DISTINCT drop duplicate rows 7 ORDER BY sort (aliases OK) 8 LIMIT keep the first n
Each step only sees what earlier steps produced. Window functions are computed at step 5, after HAVING.
SQL
SELECT   c.city,
         COUNT(DISTINCT o.id)             AS orders,
         SUM(oi.quantity * oi.unit_price) AS revenue
FROM     customers c
JOIN     orders o       ON o.customer_id = c.id
JOIN     order_items oi ON oi.order_id   = o.id
WHERE    o.status <> 'cancelled'
GROUP BY c.city
HAVING   revenue > 150
ORDER BY revenue DESC
LIMIT    2;
Result
+---------+--------+---------+
| city    | orders | revenue |
+---------+--------+---------+
| Toronto |      3 | 2061.00 |
| Mumbai  |      2 | 1164.00 |
+---------+--------+---------+
2 rows in set (0.000 sec)
What’s happening
  1. FROM / JOIN: customers are joined to their orders and each order to its lines, giving one row per order line: 13 rows. Zara, with no orders, is not among them (inner join).
  2. WHERE removes rows whose order is cancelled: order 4's single MacBook line goes, leaving 12 rows.
  3. GROUP BY c.city packs the 12 rows into 4 groups: Toronto (Rohit's orders 1 and 2 and Noah's order 7, 6 lines), Mumbai (Maya's orders 3 and 8, 3 lines), London (Liam's order 5, 2 lines) and NULL (Sofia's order 6, 1 line).
  4. HAVING needs each group's total, so SUM(quantity * unit_price) is computed per group: Toronto 2061.00, Mumbai 1164.00, NULL 249.00, London 127.00. revenue > 150 drops London.
  5. SELECT produces the output columns for the 3 surviving groups. COUNT(DISTINCT o.id) counts orders, not lines (Toronto has 6 lines but 3 orders).
  6. ORDER BY revenue DESC sorts Toronto, Mumbai, NULL, and LIMIT 2 keeps the first two. Sofia's NULL-city group is cut last of all.
AdvancedErrors the processing order explains
SQL
SELECT name, salary * 12 AS yearly FROM employees WHERE yearly > 1000000;
SELECT COUNT(*) AS n FROM employees WHERE COUNT(*) > 1;
SELECT department_id AS dept, COUNT(*) AS headcount
FROM employees GROUP BY dept HAVING headcount > 2 ORDER BY headcount DESC;
Result
ERROR 1054 (42S22): Unknown column 'yearly' in 'where clause'
ERROR 1111 (HY000): Invalid use of group function
+------+-----------+
| dept | headcount |
+------+-----------+
|    1 |         4 |
|    2 |         3 |
+------+-----------+
What’s happening
  1. WHERE (step 2) runs before SELECT (step 5), so the alias yearly doesn't exist yet: error 1054.
  2. WHERE also runs before GROUP BY (step 3), so there are no groups to count yet. An aggregate in WHERE is error 1111, "Invalid use of group function". Aggregate conditions belong in HAVING.
  3. The third query uses the aliases dept in GROUP BY and headcount in HAVING and ORDER BY, and MySQL accepts all three. By the logical order, GROUP BY and HAVING come before SELECT, so standard SQL (and PostgreSQL for HAVING) rejects them; MySQL resolves the alias anyway as an extension. ORDER BY with an alias is legal everywhere.
  4. Engineering has 4 people, Sales 3, Support 2 and Jonas's NULL department 1, so HAVING headcount > 2 keeps two groups.
AdvancedDISTINCT happens before ORDER BY and LIMIT
SQL
SELECT DISTINCT department_id FROM employees ORDER BY department_id LIMIT 2;
Result
+---------------+
| department_id |
+---------------+
|          NULL |
|             1 |
+---------------+
What’s happening
  1. SELECT (step 5) produces 10 department ids, one per employee.
  2. DISTINCT (step 6) reduces them to 4: NULL, 1, 2, 3.
  3. ORDER BY (step 7) sorts them with NULL first, and LIMIT (step 8) keeps two. If LIMIT ran before DISTINCT, you could get 1, 1.
  4. This is the same reason the "second-highest distinct salary" query in LIMIT and OFFSET works.
Where does a window function fit?

Window functions such as ROW_NUMBER() OVER (ORDER BY salary DESC) are computed in step 5, with SELECT. Can you filter on one in WHERE?

Show answer

No. WHERE runs at step 2, before any window function has been computed, so MySQL rejects it:

Result
ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.'

(The stray quote at the end is really in MySQL 9.7's message.) Compute it in a CTE or derived table and filter in the outer query.

What’s happening
  1. Window functions need the final set of rows that FROM, WHERE, GROUP BY and HAVING produce: a row's rank depends on which other rows survived.
  2. So they are evaluated in step 5, after HAVING. WHERE and HAVING can't see them.
  3. The standard pattern: WITH ranked AS (SELECT …, ROW_NUMBER() OVER (…) AS rn FROM …) SELECT … FROM ranked WHERE rn <= 3. See Top N per group.

SQL and MySQL written fresh; MongoDB built from Rohit’s “mongoDB interview” doc. Every query was run on MySQL 9.7, MongoDB 9.0, Redis 8.10 or PostgreSQL 18.

RohitDownloads · All chapters

Sync progress across devices

Type the same private phrase on your Mac and your phone, and your Learned ticks follow you between them — on every study site.

The phrase never leaves this device: only a fingerprint of it is sent, and the server stores a fingerprint of that. Anyone who knows the phrase could see or change your ticks, so pick something you don’t use elsewhere.

Sync is on in this browser.

Sync ID

This ID must be the same on every device. If another device shows a different one, its phrase is different (capital letters count): tap “Turn off here” on it and type the phrase again exactly.