#SQL and querying
AddedThese questions check the parts of SQL that people get wrong under pressure: the order a query is logically evaluated in, what NULL does to counts, sorts and comparisons, how many rows a join or a union returns, and the MySQL-specific quirks of arithmetic and string comparison. Answer each one out loud before opening it. The first paragraph of each answer is what you'd say in the interview; the walkthrough is the reasoning behind it. Every query was run on MySQL 9.7 against the site's sample schema.
Why does WHERE annual > 1500000 fail when annual is defined in the SELECT?
SELECT name, salary * 12 AS annual
FROM employees
WHERE annual > 1500000;Show answer
Because WHERE is evaluated before SELECT, so the alias doesn't exist yet. MySQL says so:
ERROR 1054 (42S22): Unknown column 'annual' in 'where clause'Repeat the expression (WHERE salary * 12 > 1500000), or filter in an outer query. MySQL also lets you use the alias in HAVING, which runs after SELECT:
SELECT name, salary * 12 AS annual
FROM employees
HAVING annual > 1500000
ORDER BY annual DESC;+-------+------------+
| name | annual |
+-------+------------+
| Asha | 2220000.00 |
| Ben | 1704000.00 |
| Chloe | 1704000.00 |
| Elena | 1560000.00 |
+-------+------------+What’s happening
- The logical order is
FROM→WHERE→GROUP BY→HAVING→SELECT→DISTINCT→ORDER BY→LIMIT. The database can execute it differently, but the meaning follows this order. - When
WHEREruns, each row only has its table columns (name,salary, …).annualis created later, inSELECT, hence error 1054. ORDER BYruns afterSELECT, soORDER BY annualis fine in every database.HAVINGwithoutGROUP BYthat refers to aSELECTalias is a MySQL extension: it treats the whole result as one group and allows the alias. PostgreSQL 18 rejects the same shape (tried on a small tablee) withcolumn "e.name" must appear in the GROUP BY clause or be used in an aggregate function. In portable SQL, wrap the query:SELECT * FROM (…) AS t WHERE annual > 1500000.- The same rule explains why window functions can't appear in
WHERE: they're computed in theSELECTstep too.
Learn more: Logical query processing order.
WHERE vs HAVING: which departments have at least three employees?
Write the query, and say why WHERE COUNT(*) >= 3 can't work.
Show answer
WHERE filters rows before grouping; HAVING filters groups after aggregation. An aggregate doesn't exist at WHERE time, so the condition belongs in HAVING:
SELECT department_id, COUNT(*) AS n
FROM employees
GROUP BY department_id
HAVING n >= 3
ORDER BY n DESC;+---------------+---+
| department_id | n |
+---------------+---+
| 1 | 4 |
| 2 | 3 |
+---------------+---+What’s happening
GROUP BY department_idforms groups: 1 (4 people), 2 (3), 3 (2) andNULL(Jonas).COUNT(*)is computed per group;HAVING n >= 3then keeps Engineering and Sales.WHERE COUNT(*) >= 3fails withERROR 1111 (HY000): Invalid use of group function, becauseWHEREjudges one row at a time, before any group exists.- Put row-level conditions in
WHEREwhenever you can (WHERE hired_on >= '2020-01-01'): it removes rows before the grouping work, which is cheaper, and it can use indexes. HAVING nuses the alias — allowed in MySQL. The portable form isHAVING COUNT(*) >= 3.
Learn more: HAVING vs WHERE.
Predict: COUNT(*), COUNT(city) and COUNT(DISTINCT city)
Customers' cities are Toronto, Mumbai, London, NULL, Toronto, Dubai.
SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM customers;Show answer
6, 5, 4.
+----------+-------------+----------------------+
| COUNT(*) | COUNT(city) | COUNT(DISTINCT city) |
+----------+-------------+----------------------+
| 6 | 5 | 4 |
+----------+-------------+----------------------+What’s happening
COUNT(*)counts rows, whatever they contain: 6.COUNT(city)counts non-NULLvalues. Sofia's city isNULL, so 5.COUNT(DISTINCT city)counts different non-NULLvalues: Toronto, Mumbai, London, Dubai — 4. Toronto appears twice but counts once, andNULLisn't a value to count.COUNT(1)is identical toCOUNT(*)in MySQL; there's no performance difference, despite the folklore.- Every other aggregate (
SUM,AVG,MIN,MAX) ignoresNULLs the same way — which is whyAVGcan silently change its denominator.
Learn more: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT).
Where does a NULL city sort, and how do you move it to the end?
SELECT name, city FROM customers ORDER BY city;Show answer
First. MySQL treats NULL as smaller than any value, so it comes first in ascending order and last in descending order:
+-------+---------+
| name | city |
+-------+---------+
| Sofia | NULL |
| Zara | Dubai |
| Liam | London |
| Maya | Mumbai |
| Rohit | Toronto |
| Noah | Toronto |
+-------+---------+To put NULLs last while keeping the ascending order, sort by a "is it null?" flag first:
SELECT name, city FROM customers ORDER BY city IS NULL, city;+-------+---------+
| name | city |
+-------+---------+
| Zara | Dubai |
| Liam | London |
| Maya | Mumbai |
| Rohit | Toronto |
| Noah | Toronto |
| Sofia | NULL |
+-------+---------+What’s happening
city IS NULLis0for real cities and1for Sofia, so sorting by it first puts all real cities before her.- Within the
0group, the second keycitysorts alphabetically. - MySQL has no
NULLS FIRST/NULLS LASTsyntax; PostgreSQL does, and its default is the opposite of MySQL's: on PostgreSQL 18,ORDER BY cityput theNULLrow last. Worth knowing when a query moves between them. - Rohit and Noah tie on Toronto, and their relative order isn't guaranteed. Add a tiebreaker (
, id) whenever the order matters, for example for pagination.
Learn more: Sorting with ORDER BY and MySQL vs PostgreSQL: SQL differences you'll hit first.
Predict six MySQL expressions
SELECT 7 / 2 AS slash, 7 DIV 2 AS div_, 7 % 2 AS modulo,
'10' < '9' AS str_cmp, 10 < 9 AS num_cmp, 1 / 0 AS div_zero;Show answer
3.5000, 3, 1, 1, 0 and NULL (with a warning).
+--------+------+--------+---------+---------+----------+
| slash | div_ | modulo | str_cmp | num_cmp | div_zero |
+--------+------+--------+---------+---------+----------+
| 3.5000 | 3 | 1 | 1 | 0 | NULL |
+--------+------+--------+---------+---------+----------+What’s happening
/is always decimal division in MySQL, even between integers:3.5000(four decimal places by default, fromdiv_precision_increment). PostgreSQL 18 returns3forSELECT 7/2, because integer divided by integer stays an integer there.DIVis integer division (truncating):3.%(orMOD) is the remainder:1.'10' < '9'compares strings, character by character:'1'sorts before'9', so it's true (1). Numbers compare numerically:10 < 9is0. This is why storing numbers inVARCHARcolumns breaks sorting.1 / 0returnsNULLin aSELECT, withWarning 1365 Division by 0. In anINSERTorUPDATEunder the default strict mode (which includesERROR_FOR_DIVISION_BY_ZERO), it's an error instead.- Booleans print as
1and0because MySQL'sBOOLEANis justTINYINT(1).
Learn more: String, number and date functions.
Predict: does 'abc' equal 'abc ' (with trailing spaces)?
SELECT 'abc' = 'abc ' AS no_pad,
'abc' = 'abc ' COLLATE utf8mb4_general_ci AS pad_space;Show answer
It depends on the collation: 0 with MySQL 8.0+'s default utf8mb4_0900_ai_ci, but 1 with utf8mb4_general_ci, the utf8mb4 default on MySQL 5.7.
+--------+-----------+
| no_pad | pad_space |
+--------+-----------+
| 0 | 1 |
+--------+-----------+What’s happening
- Every collation has a pad attribute.
information_schema.COLLATIONSshowsutf8mb4_0900_ai_ciasNO PADandutf8mb4_general_ciasPAD SPACE. PAD SPACEcollations compare as if the shorter string were padded with spaces, so trailing spaces are ignored and the strings are equal.NO PADcollations compare the actual characters, so'abc 'is longer and different.- Upgrading from 5.7 to 8.0 and switching to the new default collation can therefore change query results and even make a unique index accept
'abc'and'abc 'as two values. It's an "old and new" trap worth naming. - Both are case-insensitive (
_ci), so'abc' = 'ABC'is1either way. Use a_binor_cscollation for case-sensitive comparison.
Learn more: Strings: CHAR, VARCHAR, TEXT and character sets.
Spot the bug: BETWEEN on a DATETIME column misses an order
Orders 1 and 3 were placed on 1 February and 10 February 2025. Why does this return only order 1?
SELECT id, ordered_at FROM orders
WHERE ordered_at BETWEEN '2025-02-01' AND '2025-02-10'
ORDER BY ordered_at;Show answer
'2025-02-10' compared with a DATETIME means midnight at the start of 10 February. Order 3 was placed at 09:20 that day, after the upper bound:
+----+---------------------+
| id | ordered_at |
+----+---------------------+
| 1 | 2025-02-01 10:00:00 |
+----+---------------------+Use a half-open range instead:
SELECT id, ordered_at FROM orders
WHERE ordered_at >= '2025-02-01' AND ordered_at < '2025-02-11'
ORDER BY ordered_at;+----+---------------------+
| id | ordered_at |
+----+---------------------+
| 1 | 2025-02-01 10:00:00 |
| 3 | 2025-02-10 09:20:00 |
+----+---------------------+What’s happening
BETWEEN a AND bis inclusive at both ends:>= a AND <= b. That's fine for integers andDATEcolumns.- For
DATETIME, the string'2025-02-10'is converted to2025-02-10 00:00:00. Anything later that day is> b. >= start AND < next dayincludes the whole last day at any precision, including fractional seconds, and it can still use an index onordered_at.- Writing
DATE(ordered_at) BETWEEN …also gives the right rows, but wrapping the column in a function prevents a normal index from being used.
Learn more: Dates and times: DATETIME vs TIMESTAMP and Filtering with WHERE.
Predict the row counts: customers JOIN orders vs LEFT JOIN, and UNION vs UNION ALL
There are 6 customers and 8 orders; Zara has no orders. Predict all four numbers.
SELECT
(SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id) AS left_rows,
(SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id) AS inner_rows;
SELECT
(SELECT COUNT(*) FROM (SELECT city FROM customers UNION SELECT city FROM customers) u) AS union_rows,
(SELECT COUNT(*) FROM (SELECT city FROM customers UNION ALL SELECT city FROM customers) u) AS union_all_rows;Show answer
9 and 8; 5 and 12.
+-----------+------------+
| left_rows | inner_rows |
+-----------+------------+
| 9 | 8 |
+-----------+------------+
+------------+----------------+
| union_rows | union_all_rows |
+------------+----------------+
| 5 | 12 |
+------------+----------------+What’s happening
- The inner join returns one row per matching pair. Every order has exactly one customer, so there are 8 pairs.
- The
LEFT JOINreturns those 8 plus oneNULL-padded row for each customer with no match — Zara — so 9. A join's row count is driven by matches, not by the size of the left table. UNION ALLsimply appends: 6 + 6 = 12 rows.UNIONremoves duplicate rows from the combined result: the distinct cities are Toronto, Mumbai, London, Dubai andNULL— 5. Unlike=,UNIONtreats twoNULLs as duplicates.UNIONcosts a de-duplication step (a temporary table or sort). UseUNION ALLunless you need duplicates removed.
Learn more: LEFT and RIGHT OUTER JOIN and Set operations: UNION, INTERSECT and EXCEPT.
Predict: a CASE with no ELSE
SELECT name,
CASE WHEN stock = 0 THEN 'sold out'
WHEN stock < 10 THEN 'low' END AS stock_flag
FROM products ORDER BY id;Show answer
Products that match neither branch get NULL — a CASE without ELSE defaults to ELSE NULL.
+---------------------------------------+------------+
| name | stock_flag |
+---------------------------------------+------------+
| ThinkBook 14 | NULL |
| MacBook Air | low |
| Pixel 10 | NULL |
| iPhone 17 | sold out |
| SQL Performance Explained | NULL |
| Designing Data-Intensive Applications | NULL |
| USB-C Charger | NULL |
| Noise-cancelling Headphones | low |
+---------------------------------------+------------+What’s happening
CASEtests theWHENs in order and stops at the first true one. The iPhone (stock 0) matches the first branch, so it's "sold out", not "low", even though0 < 10is also true.- The MacBook (5) and headphones (8) match the second branch.
- Everything else matches nothing and falls to the implicit
ELSE NULL. - This matters inside aggregates:
SUM(CASE WHEN … THEN 1 END)returnsNULLfor a group where nothing matched, whileSUM(CASE WHEN … THEN 1 ELSE 0 END)returns0.
Learn more: CASE expressions.
How many rows does COUNT() OVER () return, compared with COUNT()?
Sales has three employees.
SELECT name, COUNT(*) OVER () AS total, salary
FROM employees
WHERE department_id = 2;Show answer
Three rows, each with total = 3. A window function adds a value to every row; an aggregate without OVER collapses the rows into one.
+-------+-------+-----------+
| name | total | salary |
+-------+-------+-----------+
| Elena | 3 | 130000.00 |
| Farid | 3 | 95000.00 |
| Grace | 3 | 99000.00 |
+-------+-------+-----------+What’s happening
WHERE department_id = 2runs first and leaves three rows.COUNT(*) OVER ()uses an empty window — all rows of the result — so each row gets the count of the whole result:3.SELECT name, COUNT(*) FROM … WHERE department_id = 2withoutOVERwould collapse to one row, and underONLY_FULL_GROUP_BYit's rejected becausenameisn't grouped.- That's the interview line: "
GROUP BYreduces rows; window functions keep every row and add a computed column." It's why window functions are the tool for running totals, ranks and percent-of-total.
Learn more: Window functions: OVER and PARTITION BY.
DELETE vs TRUNCATE inside a transaction — predict the counts and the next id
The table t_del has three rows with ids 1–3.
START TRANSACTION; DELETE FROM t_del; ROLLBACK;
SELECT COUNT(*) AS after_delete_rollback FROM t_del;
START TRANSACTION; TRUNCATE TABLE t_del; ROLLBACK;
SELECT COUNT(*) AS after_truncate_rollback FROM t_del;
INSERT INTO t_del (v) VALUES (9);
SELECT * FROM t_del;Show answer
3, then 0, then the new row gets id 1. DELETE is a normal, transactional row operation; TRUNCATE is DDL that commits implicitly, can't be rolled back and resets AUTO_INCREMENT.
+-----------------------+
| after_delete_rollback |
+-----------------------+
| 3 |
+-----------------------+
+-------------------------+
| after_truncate_rollback |
+-------------------------+
| 0 |
+-------------------------+
+----+------+
| id | v |
+----+------+
| 1 | 9 |
+----+------+What’s happening
DELETE FROM t_delremoves rows one by one inside the transaction, writing undo records.ROLLBACKuses them to restore all three rows.TRUNCATE TABLEdrops and recreates the table internally. It causes an implicit commit (of the transaction it's in and of itself), so theROLLBACKafterwards has nothing to undo: the table stays empty.- Because the table was recreated, the
AUTO_INCREMENTcounter starts again, so the next insert gets id1. After aDELETE, it would have been4. - Other differences:
TRUNCATEdoesn't fireDELETEtriggers and fails if another table's foreign key references this one.DROP TABLEremoves the table itself, structure and all. - Use
DELETEwhen you need aWHERE, triggers or the ability to roll back;TRUNCATEto empty a large table fast.
Learn more: DELETE vs TRUNCATE vs DROP and Autocommit and implicit commits.