Querying Data with SELECT
Concept
SELECT reads data. MySQL follows standard SQL and adds conveniences such as LIMIT, REGEXP, the <=> null-safe operator, and the short TABLE and VALUES statements (8.0.19+).
Logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
Why It Matters
Everything else — reports, APIs, dashboards, test verification — is built on SELECT.
Syntax
SELECT [DISTINCT] select_expr [AS alias], ...
FROM table_references
[WHERE condition]
[GROUP BY ...] [HAVING ...]
[ORDER BY col [ASC|DESC], ...]
[LIMIT [offset,] row_count | LIMIT row_count OFFSET offset]
[FOR UPDATE | FOR SHARE];Example
9.1 Columns, aliases, expressions, DISTINCT MySQL division returns a decimal: SELECT 7 / 2; → 3.5000. Integer division uses DIV: SELECT 7 DIV 2; → 3. Modulo: 7 % 2 or MOD(7, 2) → 1.
9.2 WHERE and operators NULL-safe equality (MySQL):
NOT IN with NULLs returns nothing: WHERE dept_id NOT IN (1, NULL) → 0 rows.
9.3 LIKE and REGEXP Remember: with the default _ci collation, LIKE and = are case-insensitive. Use LIKE BINARY 'M%' or a _bin/_cs collation for case-sensitive matching.
9.4 ORDER BY and LIMIT NULLs sort first in ascending order in MySQL. To put them last: ORDER BY dept_id IS NULL, dept_id.
Custom order: ORDER BY FIELD(status, 'PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED').
Keyset pagination for large tables: WHERE product_id > :last_id ORDER BY product_id LIMIT 20.
MySQL does not support NULLS FIRST/LAST, FETCH FIRST, or TOP.
9.5 TABLE and VALUES statements (8.0.19+) 9.6 Locking reads (Module 17 covers locking in detail.)
SELECT product_name AS product,
price,
stock,
price * stock AS stock_value,
ROUND(price * 1.18, 2) AS price_with_gst
FROM products
ORDER BY stock_value DESC;+---------------+----------+-------+-------------+----------------+
| product | price | stock | stock_value | price_with_gst |
+---------------+----------+-------+-------------+----------------+
| Laptop | 75000.00 | 15 | 1125000.00 | 88500.00 |
| Monitor | 18000.00 | 25 | 450000.00 | 21240.00 |
| Desk Chair | 12000.00 | 30 | 360000.00 | 14160.00 |
| Mouse | 800.00 | 200 | 160000.00 | 944.00 |
| Notebook | 50.00 | 1000 | 50000.00 | 59.00 |
| Standing Desk | 35000.00 | 0 | 0.00 | 41300.00 |
+---------------+----------+-------+-------------+----------------+SELECT DISTINCT category FROM products ORDER BY category; -- Electronics, Furniture, Stationery
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- || is OR in MySQL!SELECT first_name, salary FROM employees WHERE salary > 100000; -- Asha, Vikram, Rahul
SELECT first_name FROM employees WHERE salary BETWEEN 90000 AND 150000; -- Vikram, Neha, Rahul, Arjun, Meera
SELECT first_name FROM employees WHERE dept_id IN (2, 3); -- Rahul, Priya, Arjun
SELECT name FROM customers WHERE email IS NULL; -- Diya Patel
SELECT order_id FROM orders WHERE status <> 'DELIVERED'; -- 1003, 1004, 1005
SELECT first_name FROM employees WHERE (dept_id = 1 OR dept_id = 2) AND salary > 100000; -- Asha, Vikram, RahulSELECT first_name FROM employees WHERE dept_id <=> NULL; -- Kiran (same as IS NULL)
SELECT COUNT(*) FROM employees WHERE NOT (dept_id <=> 1); -- 4 (includes Kiran)
SELECT COUNT(*) FROM employees WHERE dept_id <> 1; -- 3 (Kiran excluded: NULL <> 1 is UNKNOWN)SELECT first_name FROM employees WHERE first_name LIKE 'M%'; -- Meera
SELECT first_name FROM employees WHERE first_name LIKE '_r%'; -- Priya, Arjun
SELECT product_name FROM products WHERE product_name LIKE '%desk%'; -- Desk Chair, Standing Desk (case-insensitive collation)
-- Regular expressions (MySQL 8 uses ICU)
SELECT dtc_code FROM dtc_events WHERE dtc_code REGEXP '^P0[0-9]{3}$'; -- generic powertrain codes
SELECT REGEXP_LIKE('EHBAUREV250000001', '^[A-HJ-NPR-Z0-9]{17}$') AS valid_vin; -- VINs exclude I, O, Q → 1
SELECT REGEXP_REPLACE('ECU-01_BMS', '[^A-Z0-9]', '') AS cleaned; -- ECU01BMS
SELECT REGEXP_SUBSTR('SW v2.4.0 build 17', '[0-9]+\\.[0-9]+\\.[0-9]+') AS version; -- 2.4.0SELECT product_name, price FROM products ORDER BY price DESC LIMIT 3; -- Laptop, Standing Desk, Monitor
SELECT product_id, product_name FROM products ORDER BY product_id LIMIT 2 OFFSET 2; -- 103, 104
SELECT product_id, product_name FROM products ORDER BY product_id LIMIT 2, 2; -- same (offset, count)TABLE departments; -- same as SELECT * FROM departments
TABLE departments ORDER BY dept_name LIMIT 2;
VALUES ROW(1, 'a'), ROW(2, 'b'); -- produces a 2-row result (columns column_0, column_1)SELECT stock FROM products WHERE product_id = 101 FOR UPDATE; -- exclusive row lock (inside a transaction)
SELECT stock FROM products WHERE product_id = 101 FOR SHARE; -- shared lock
SELECT * FROM jobs WHERE status = 'QUEUED' ORDER BY job_id LIMIT 1 FOR UPDATE SKIP LOCKED; -- work-queue patternExpected Result
SELECT customer_id, name, country
FROM customers
WHERE (country = 'India' AND email IS NOT NULL) OR country = 'UK'
ORDER BY customer_id;+-------------+--------------+---------+
| customer_id | name | country |
+-------------+--------------+---------+
| 1 | Ananya Gupta | India |
| 2 | Ben Carter | UK |
+-------------+--------------+---------+Common Mistakes
Using || for concatenation (it is OR unless PIPES_AS_CONCAT mode is set).
WHERE col = NULL.
LIMIT without ORDER BY.
Expecting case-sensitive LIKE.
Deep OFFSET pagination on large tables.
Aliases used in WHERE (not allowed; allowed in HAVING/ORDER BY).
Best Practices
Select explicit columns; use CONCAT() for strings.
Always pair LIMIT with a deterministic ORDER BY.
Use <=> or IS NULL for NULL-aware comparisons.
Prefer range predicates on bare columns for index usage.
Interview Relevance
High: "How do you paginate in MySQL?", "What does <=> do?", "What is the difference between LIKE and REGEXP?", "How are NULLs sorted in MySQL?", "/ vs DIV?".
Practice Questions
List employees hired after 2020, oldest first.
Show the 2 cheapest products.
Find customers whose email ends with example.com.
Sort orders so PENDING first, then SHIPPED, DELIVERED, CANCELLED.
Validate a list of VINs with REGEXP_LIKE.