SQL EXISTS, ANY and ALL

SQL Tutorial › Subqueries & CTEs › EXISTS, ANY & ALL

SQL EXISTS, ANY and ALL

AdvancedLesson 42 of 6910 min read7 queries run on MySQL 8.4

EXISTS asks a yes/no question: “is there at least one matching row?” It is the clearest and safest way to filter by the presence or absence of related data. ANY and ALL compare one value with every value a subquery returns: “more than any sales salary”, “more than all of them”.

In this lesson

Filter with EXISTS and NOT EXISTS
Know why NOT EXISTS is safer than NOT IN
Compare against a list with ANY / SOME
Compare against every value with ALL

EXISTS syntax

MySQL

SELECT columnsFROM table_a aWHERE EXISTS (SELECT 1 FROM table_b b WHERE b.key = a.key);

The subquery’s select list does not matter: EXISTS only checks whether any row comes back. SELECT 1 is the usual convention.

Sample data

customers · 7 rows (some columns)

customer_id	customer_name	city
1	Ananya Sharma	Mumbai
2	Rohit Kumar	Delhi
3	Fatima Khan	Hyderabad
4	David Fernandes	Goa
5	Ishita Bose	Kolkata
6	Manoj Pillai	Chennai
7	Sara Thomas	Bengaluru

orders · 10 rows

order_id	customer_id	order_date	status
1001	1	2025-01-05	Delivered
1002	2	2025-01-17	Delivered
1003	1	2025-02-02	Delivered
1004	3	2025-02-14	Cancelled
1005	4	2025-03-08	Delivered
1006	5	2025-03-22	Shipped
1007	2	2025-04-10	Delivered
1008	1	2025-04-28	Pending
1009	3	2025-05-15	Delivered
1010	5	2025-06-01	Delivered

EXISTS: rows that have a match

Customers with at least one order:

SQL

SELECT c.customer_id, c.customer_nameFROM customers cWHERE EXISTS (  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)ORDER BY c.customer_id;

ResultMySQL 8.4

customer_id	customer_name
1	Ananya Sharma
2	Rohit Kumar
3	Fatima Khan
4	David Fernandes
5	Ishita Bose

5 rows in set

How this query works, step by step

For each customer, the subquery looks for an order with the same customer_id.
As soon as one is found, EXISTS is true; MySQL does not need to look for more.
Five customers have orders. Unlike a join, a customer with three orders still appears once.

NOT EXISTS: rows without a match

Customers who have never ordered:

SQL

SELECT c.customer_id, c.customer_nameFROM customers cWHERE NOT EXISTS (  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

ResultMySQL 8.4

customer_id	customer_name
6	Manoj Pillai
7	Sara Thomas

2 rows in set

Add conditions inside the subquery for more specific questions. Customers with no delivered order:

SQL

SELECT c.customer_nameFROM customers cWHERE NOT EXISTS (  SELECT 1 FROM orders o  WHERE o.customer_id = c.customer_id AND o.status = 'Delivered')ORDER BY c.customer_name;

ResultMySQL 8.4

customer_name
Manoj Pillai
Sara Thomas

2 rows in set

NOT EXISTS vs NOT IN with NULLs

Who manages nobody? With NOT IN and a subquery that contains a NULL, the answer is silently empty:

SQL

SELECT first_nameFROM employeesWHERE emp_id NOT IN (SELECT manager_id FROM employees);

ResultMySQL 8.4

Empty set

NOT EXISTS gives the right answer, because it only checks for matching rows:

SQL

SELECT e.first_nameFROM employees eWHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id)ORDER BY e.emp_id;

ResultMySQL 8.4

first_name
Priya
Karan
Neha
Arjun
Rohan
Sneha
Amit

7 rows in set

How this query works, step by step

For each employee e, the subquery looks for anyone whose manager_id is e.emp_id.
The CEO’s NULL manager_id simply never matches; it cannot poison the result.
Seven employees have no direct reports.

Tip

For “rows with no match in another table”, use NOT EXISTS (or LEFT JOIN ... IS NULL). Avoid NOT IN unless the column is declared NOT NULL.

ANY / SOME

value > ANY (subquery) is true if the value is greater than at least one value in the list. Employees who earn more than at least one sales employee:

MySQL

SELECT first_name, salaryFROM employeesWHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 2)  AND dept_id <> 2ORDER BY salary;

ResultMySQL 8.4

first_name	salary
Priya	72000
Karan	72000
Sneha	78000
Kavya	88000
Rahul	98000
Asha	125000

6 rows in set

How this query works, step by step

The subquery returns the Sales salaries: 105,000, 54,000 and 48,000.
> ANY means “more than the smallest of them”, here more than 48,000.
= ANY is the same as IN. SOME is a synonym for ANY.

ALL

value > ALL (subquery) is true only if the value is greater than every value in the list:

MySQL

SELECT first_name, salaryFROM employeesWHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2)ORDER BY salary;

ResultMySQL 8.4

first_name	salary
Asha	125000
Meera	250000

2 rows in set

How this query works, step by step

> ALL means “more than the largest”, here more than 105,000.
Only Asha (125,000) and Meera (250,000) qualify.
<> ALL is the same as NOT IN, including its NULL problem.

Expression	Equivalent
`x > ANY (list)`	`x > MIN(list)`
`x > ALL (list)`	`x > MAX(list)`
`x = ANY (list)`	`x IN (list)`
`x <> ALL (list)`	`x NOT IN (list)`

Exercise: Products that were ordered

List the products that appear in at least one order line, using EXISTS.

Expected resultMySQL 8.4

product_name
Wireless Mouse
Mechanical Keyboard
USB-C Hub
Office Chair
Standing Desk
Notebook Pack
Gel Pens
Desk Lamp

8 rows in set

Hint 1

Correlate order_items.product_id with products.product_id.

SQL

SELECT p.product_nameFROM products pWHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id)ORDER BY p.product_id;

ResultMySQL 8.4

product_name
Wireless Mouse
Mechanical Keyboard
USB-C Hub
Office Chair
Standing Desk
Notebook Pack
Gel Pens
Desk Lamp

8 rows in set

Exercise: Departments without staff

List departments that have no employees, using NOT EXISTS.

Expected resultMySQL 8.4

dept_name
Finance

1 row in set

Hint 1

Look for an employee whose dept_id matches the department.

SQL

SELECT d.dept_nameFROM departments dWHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

ResultMySQL 8.4

dept_name
Finance

1 row in set

Quick quiz

1. What does EXISTS check?

2. Why is NOT EXISTS safer than NOT IN?

3. salary > ALL (10, 20, 30) is true for…

EXISTS FAQ

What is the difference between EXISTS and IN?

IN compares a value with a list returned by the subquery. EXISTS only checks whether a correlated subquery returns any row. They often give the same result, but NOT EXISTS handles NULLs correctly where NOT IN does not.

Is EXISTS faster than IN?

In MySQL 8 the optimizer turns both into semi-joins in most cases, so performance is usually similar. Choose the one that reads best; prefer NOT EXISTS over NOT IN.

What does SELECT 1 mean inside EXISTS?

Nothing special: EXISTS ignores the selected values. SELECT 1 just shows that only the presence of a row matters.

SQL EXPLAIN and Query Optimization

SQL Tutorial › Performance & Objects › EXPLAIN & Query Tuning

SQL EXPLAIN and Query Optimization

AdvancedLesson 61 of 6913 min read7 queries run on MySQL 8.4

When a query is slow, guessing does not help: ask the database how it runs the query. EXPLAIN shows the plan MySQL chose: which tables it reads in which order, which indexes it uses and how many rows it expects. This lesson reads real plans column by column and turns them into a checklist for faster SQL.

In this lesson

Read the key columns of EXPLAIN: type, key, rows, Extra
See the actual run with EXPLAIN ANALYZE
Fix common slow patterns
Follow a practical optimization checklist

A first EXPLAIN

MySQL

EXPLAINSELECT o.order_id, c.customer_nameFROM orders oJOIN customers c ON c.customer_id = o.customer_idWHERE o.status = 'Pending';

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	NULL	ALL	customer_id	NULL	NULL	NULL	1	100	Using where
1	SIMPLE	c	NULL	eq_ref	PRIMARY	PRIMARY	4	company.o.customer_id	1	100	NULL

2 rows in set

How this query works, step by step

Each row of the output is one table access, in the order MySQL performs them.
orders is read first with type = ALL (there is no index on status), and filtered estimates the share of rows that pass the WHERE.
For each such order, customers is read with type = eq_ref through its PRIMARY key: exactly one row per lookup.

The columns that matter

Column	Tells you
`table`	which table this step reads
`type`	how it is read (see below)
`possible_keys`	indexes MySQL considered
`key`	the index actually used (`NULL` = none)
`rows`	estimated rows read in this step
`filtered`	estimated % of those rows that pass the conditions
`Extra`	notes such as `Using where`, `Using index`, `Using filesort`, `Using temporary`

type, from best to worst:

type	Meaning
`const` / `system`	at most one row, found by primary key or unique key with a constant
`eq_ref`	one row per row of the previous table (join on a unique key)
`ref`	several rows via a non-unique index
`range`	an index range (`BETWEEN`, `>`, `IN`, `LIKE 'x%'`)
`index`	full scan of an index
`ALL`	full table scan

const and ref

MySQL

EXPLAIN SELECT * FROM employees WHERE emp_id = 105;

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	const	PRIMARY	PRIMARY	4	const	1	100	NULL

1 row in set

MySQL

EXPLAIN SELECT * FROM employees WHERE dept_id = 2;

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	ref	dept_id	dept_id	5	const	3	100	NULL

1 row in set

How this query works, step by step

Looking up the primary key with a constant is const: MySQL knows at most one row matches.
dept_id has a (foreign key) index, so the second query is ref with key = dept_id.

EXPLAIN ANALYZE: the real run

EXPLAIN ANALYZE executes the query and reports actual times and row counts next to the estimates, as a tree:

MySQL

EXPLAIN ANALYZESELECT c.customer_name, COUNT(*) AS ordersFROM customers cJOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_name;

ResultMySQL 8.4

EXPLAIN
-> Table scan on <temporary> (actual time=0.112..0.112 rows=5 loops=1) -> Aggregate using temporary table (actual time=0.111..0.111 rows=5 loops=1) -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0232..0.0317 rows=10 loops=1) -> Covering index scan on o using customer_id (cost=0.35 rows=1) (actual time=0.0061..0.0101 rows=10 loops=1) -> Single-row index lookup on c using PRIMARY (customer_id=o.customer_id) (cost=0.35 rows=1) (actual time=760e-6..800e-6 rows=1 loops=10)

EXPLAIN

-> Table scan on <temporary> (actual time=0.112..0.112 rows=5 loops=1) -> Aggregate using temporary table (actual time=0.111..0.111 rows=5 loops=1) -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0232..0.0317 rows=10 loops=1) -> Covering index scan on o using customer_id (cost=0.35 rows=1) (actual time=0.0061..0.0101 rows=10 loops=1) -> Single-row index lookup on c using PRIMARY (customer_id=o.customer_id) (cost=0.35 rows=1) (actual time=760e-6..800e-6 rows=1 loops=10)

1 row in set

Read it from the most-indented line outwards: the innermost lines run first. Compare rows= estimates with actual ... rows=: a big difference means stale statistics (ANALYZE TABLE t; refreshes them). The timings are from the build server and change on every run.

EXPLAIN FORMAT=TREE shows the same tree without running the query.

Fix: an index for a filter

MySQL

CREATE INDEX idx_orders_status ON orders (status);EXPLAIN SELECT order_id FROM orders WHERE status = 'Pending';

ResultMySQL 8.4

mysql> CREATE INDEX idx_orders_status ON orders (status)

Query OK, 0 rows affected

mysql> EXPLAIN SELECT order_id FROM orders WHERE status = 'Pending'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	ref	idx_orders_status	idx_orders_status	82	const	1	100	Using index

1 row in set

Before the index the plan was ALL; now it is ref on idx_orders_status.

Fix: avoid filesort with an index on the ORDER BY

MySQL

EXPLAIN SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 3;

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	ALL	NULL	NULL	NULL	NULL	10	100	Using filesort

1 row in set

MySQL

CREATE INDEX idx_orders_date ON orders (order_date);EXPLAIN SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 3;

ResultMySQL 8.4

mysql> CREATE INDEX idx_orders_date ON orders (order_date)

Query OK, 0 rows affected

mysql> EXPLAIN SELECT order_id, order_date FROM orders ORDER BY order_date DE…

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	index	NULL	idx_orders_date	3	NULL	3	100	Backward index scan; Using index

1 row in set

How this query works, step by step

Without an index on order_date, Extra shows Using filesort: MySQL sorts all rows to find the top 3.
With the index, MySQL reads the index backwards and stops after 3 entries: no filesort.

Optimization checklist

Select only the columns you need; avoid SELECT * on wide tables.
Index the columns in WHERE, JOIN and ORDER BY of frequent queries; check with EXPLAIN that key is not NULL.
Keep columns bare in WHERE: order_date >= '2025-01-01', not YEAR(order_date) = 2025.
Avoid leading wildcards (LIKE '%term') on large tables; use full-text search.
Match types: compare text columns with text, numbers with numbers.
Filter early: put conditions in WHERE rather than HAVING when they do not need aggregates.
Use EXISTS / joins instead of IN on huge subquery lists; prefer NOT EXISTS to NOT IN.
Paginate with keyset (WHERE id > last_id) instead of huge OFFSETs.
Use UNION ALL instead of UNION when duplicates are impossible.
Measure: use EXPLAIN ANALYZE and the slow query log, change one thing at a time, and measure again.

Quick quiz

1. Which type is the worst?

2. key = NULL in EXPLAIN means…

3. What does Using filesort indicate?

4. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN and optimization FAQ

How do I find out why a MySQL query is slow?

Run EXPLAIN (or EXPLAIN ANALYZE) on it. Look for type = ALL on large tables, key = NULL, very high rows, and Using filesort or Using temporary in Extra.

What does EXPLAIN do in SQL?

It shows the execution plan the optimizer chose: the order of table access, the indexes used and the estimated number of rows.

How can I optimize a SQL query?

Add indexes that match the filters, joins and sorts; keep indexed columns free of functions; return only needed columns and rows; and verify every change with EXPLAIN ANALYZE.

SQL User-Defined Functions

SQL Tutorial › Performance & Objects › User-Defined Functions

SQL User-Defined Functions

AdvancedLesson 64 of 699 min read3 queries run on MySQL 8.4

MySQL has hundreds of built-in functions, but business rules are your own: GST on a price, a customer tier from total spend, a formatted employee code. A stored function, created with CREATE FUNCTION, packages such a rule so you can use it inside any query, just like UPPER() or ROUND().

In this lesson

Create a function with parameters and RETURNS
Mark functions DETERMINISTIC correctly
Use functions in SELECT, WHERE and ORDER BY
Choose between a function and a procedure

A first function

MySQL

DELIMITER //CREATE FUNCTION price_with_gst(p_price INT)RETURNS INTDETERMINISTICBEGIN  RETURN ROUND(p_price * 1.18);END //DELIMITER ; SELECT product_name, price, price_with_gst(price) AS price_incl_gstFROM productsWHERE category = 'Electronics';

ResultMySQL 8.4

mysql> CREATE FUNCTION price_with_gst(p_price INT)

Query OK, 0 rows affected

mysql> SELECT product_name, price, price_with_gst(price) AS price_incl_gst

product_name	price	price_incl_gst
Wireless Mouse	799	943
Mechanical Keyboard	3499	4129
USB-C Hub	1999	2359

3 rows in set

How this query works, step by step

CREATE FUNCTION price_with_gst(p_price INT) takes one input. Function parameters are always input-only.
RETURNS INT declares the type of the result.
DETERMINISTIC promises that the same input always gives the same output.
RETURN sends back the value; the function is then used in SELECT like a built-in one.

Note

MySQL with binary logging enabled (the default) refuses to create a function unless it is declared DETERMINISTIC, NO SQL or READS SQL DATA. Declare honestly: a function that reads tables is READS SQL DATA.

A function that reads data

Customer tier from total spend:

MySQL

DELIMITER //CREATE FUNCTION customer_tier(p_customer_id INT)RETURNS VARCHAR(10)READS SQL DATABEGIN  DECLARE v_spend INT;  SELECT COALESCE(SUM(oi.quantity * p.price), 0) INTO v_spend  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.customer_id = p_customer_id AND o.status <> 'Cancelled';   RETURN CASE           WHEN v_spend >= 20000 THEN 'Gold'           WHEN v_spend >= 5000  THEN 'Silver'           WHEN v_spend > 0      THEN 'Bronze'           ELSE 'New'         END;END //DELIMITER ; SELECT customer_id, customer_name, customer_tier(customer_id) AS tierFROM customersORDER BY customer_id;

ResultMySQL 8.4

mysql> CREATE FUNCTION customer_tier(p_customer_id INT)

Query OK, 0 rows affected

mysql> SELECT customer_id, customer_name, customer_tier(customer_id) AS tier

customer_id	customer_name	tier
1	Ananya Sharma	Gold
2	Rohit Kumar	Silver
3	Fatima Khan	Bronze
4	David Fernandes	Bronze
5	Ishita Bose	Silver
6	Manoj Pillai	New
7	Sara Thomas	New

7 rows in set

How this query works, step by step

The function sums the customer’s non-cancelled order lines into v_spend.
A CASE turns the amount into a tier.
The query calls the function once per customer row.

Watch out

A function that runs a query is called once for every row. On a large table that is a hidden loop of queries and can be very slow. For reports over many rows, a join with GROUP BY is usually much faster.

Functions in WHERE and ORDER BY

MySQL

DELIMITER //CREATE FUNCTION emp_code(p_last_name VARCHAR(30), p_emp_id INT)RETURNS VARCHAR(12)DETERMINISTICRETURN CONCAT(UPPER(LEFT(p_last_name, 3)), '-', p_emp_id) //DELIMITER ; SELECT emp_id, first_name, emp_code(last_name, emp_id) AS codeFROM employeesWHERE emp_code(last_name, emp_id) LIKE 'S%'ORDER BY code;

ResultMySQL 8.4

mysql> CREATE FUNCTION emp_code(p_last_name VARCHAR(30), p_emp_id INT)

Query OK, 0 rows affected

mysql> SELECT emp_id, first_name, emp_code(last_name, emp_id) AS code

emp_id	first_name	code
104	Karan	SHA-104
105	Vikram	SIN-105

2 rows in set

How this query works, step by step

A one-line function does not need BEGIN ... END.
It can be used in WHERE and ORDER BY like any expression: employees whose code starts with S (Shah and Singh).

Manage functions

Task	Statement
List	`SHOW FUNCTION STATUS WHERE Db = 'company';`
See the code	`SHOW CREATE FUNCTION customer_tier;`
Remove	`DROP FUNCTION IF EXISTS customer_tier;`

Function vs procedure

	Function	Procedure
Called with	inside SQL: `SELECT f(x)`	`CALL p(x)`
Returns	exactly one value (`RETURN`)	result sets and/or OUT parameters
Parameters	IN only	IN, OUT, INOUT
Transactions (`COMMIT`)	not allowed	allowed
Typical use	calculations, formatting	multi-step operations

Quick quiz

1. How is a stored function used?

2. What does DETERMINISTIC mean?

3. Which can contain COMMIT?

User-defined function FAQ

How do I create a function in MySQL?

Use CREATE FUNCTION name(params) RETURNS type [DETERMINISTIC | READS SQL DATA] BEGIN ... RETURN value; END, wrapped in DELIMITER // in the mysql client.

What is the difference between a function and a stored procedure in SQL?

A function returns one value and is used inside queries. A procedure is executed with CALL, can return result sets and OUT parameters, and can manage transactions.

Why do I get error 1418 when creating a function?

Binary logging requires functions to declare their nature. Add DETERMINISTIC, NO SQL or READS SQL DATA to the definition.

SQL CROSS JOIN

SQL Tutorial › Joins & Set Operators › CROSS JOIN

SQL CROSS JOIN

IntermediateLesson 37 of 698 min read4 queries run on MySQL 8.4

A CROSS JOIN pairs every row of one table with every row of another, with no join condition. 3 rows × 4 rows = 12 rows. That sounds useless until you need every combination: all sizes in all colours, every product in every month, every employee on every training day. It is also what you get by accident when you forget a join condition.

In this lesson

Build a Cartesian product with CROSS JOIN
Generate combinations and report grids
Fill gaps so missing combinations show 0
Recognise an accidental cross join

CROSS JOIN syntax

MySQL

SELECT columnsFROM table1CROSS JOIN table2;

Every combination

SQL

CREATE TABLE sizes  (size VARCHAR(5));CREATE TABLE colours (colour VARCHAR(10));INSERT INTO sizes VALUES ('S'), ('M'), ('L');INSERT INTO colours VALUES ('Black'), ('White');SELECT s.size, c.colour, CONCAT(c.colour, '-', s.size) AS skuFROM sizes sCROSS JOIN colours cORDER BY c.colour, s.size;

ResultMySQL 8.4

mysql> CREATE TABLE sizes (size VARCHAR(5))

Query OK, 0 rows affected

mysql> CREATE TABLE colours (colour VARCHAR(10))

Query OK, 0 rows affected

mysql> INSERT INTO sizes VALUES ('S'), ('M'), ('L')

Query OK, 3 rows affected

mysql> INSERT INTO colours VALUES ('Black'), ('White')

Query OK, 2 rows affected

mysql> SELECT s.size, c.colour, CONCAT(c.colour, '-', s.size) AS sku

size	colour	sku
L	Black	Black-L
M	Black	Black-M
S	Black	Black-S
L	White	White-L
M	White	White-M
S	White	White-S

6 rows in set

How this query works, step by step

sizes has 3 rows and colours has 2.
CROSS JOIN pairs each size with each colour: 3 × 2 = 6 rows.
CONCAT builds a product code for each combination.

Fill gaps in a report

Units sold per category per month. A plain GROUP BY simply leaves out combinations with no sales. Build a grid of every category × every month with a cross join, aggregate the real sales separately, then LEFT JOIN the sales onto the grid:

MySQL

SELECT g.category, g.month, COALESCE(s.units, 0) AS unitsFROM (  SELECT c.category, m.month  FROM (SELECT DISTINCT category FROM products) c  CROSS JOIN (SELECT DISTINCT DATE_FORMAT(order_date, '%Y-%m') AS month FROM orders) m) gLEFT JOIN (  SELECT p.category, DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity) AS units  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category, month) s ON s.category = g.category AND s.month = g.monthORDER BY g.category, g.month;

ResultMySQL 8.4

category	month	units
Electronics	2025-01	1
Electronics	2025-02	2
Electronics	2025-03	2
Electronics	2025-04	1
Electronics	2025-05	1
Electronics	2025-06	0
Furniture	2025-01	1
Furniture	2025-02	1
Furniture	2025-03	1
Furniture	2025-04	4
Furniture	2025-05	0
Furniture	2025-06	0
Stationery	2025-01	5
Stationery	2025-02	0
Stationery	2025-03	14
Stationery	2025-04	0
Stationery	2025-05	0
Stationery	2025-06	5

18 rows in set

How this query works, step by step

The grid g is every category crossed with every month that has orders: 3 × 6 = 18 rows.
The subquery s is the real sales, one row per category and month that sold something.
The LEFT JOIN keeps the whole grid; months with no sales for a category get NULL, which COALESCE turns into 0.

The accidental cross join

Listing two tables with a comma and forgetting the WHERE produces a cross join:

SQL

SELECT COUNT(*) AS rows_returnedFROM employees, departments;

ResultMySQL 8.4

rows_returned
60

1 row in set

How this query works, step by step

FROM employees, departments with no condition pairs all 12 employees with all 5 departments.
12 × 5 = 60 rows, almost all of them meaningless.
On two tables of 100,000 rows this is 10 billion rows and can bring a server down. Always write explicit JOIN ... ON.

CROSS JOIN with a single row

Crossing with a one-row result attaches a value to every row, for example the company average:

SQL

SELECT e.first_name, e.salary, a.avg_salary,       e.salary - a.avg_salary AS diff_from_avgFROM employees eCROSS JOIN (SELECT ROUND(AVG(salary)) AS avg_salary FROM employees) aWHERE e.dept_id = 1;

ResultMySQL 8.4

first_name	salary	avg_salary	diff_from_avg
Asha	125000	88333	36667
Rahul	98000	88333	9667
Priya	72000	88333	-16333
Karan	72000	88333	-16333
Amit	25000	88333	-63333

5 rows in set

Quick quiz

1. Table A has 5 rows and B has 4. How many rows does A CROSS JOIN B return?

2. What does SELECT * FROM a, b without WHERE produce?

3. Why cross join categories with months in a report?

CROSS JOIN FAQ

What is a Cartesian product in SQL?

The set of all combinations of rows from two tables. A CROSS JOIN returns it.

What is the difference between CROSS JOIN and INNER JOIN?

CROSS JOIN has no join condition and returns every combination. INNER JOIN returns only the combinations that satisfy the ON condition.

Is CROSS JOIN slow?

The result grows as rows × rows, so it is fine for small lookup lists but dangerous on large tables.

SQL CTE (WITH Clause)

SQL Tutorial › Subqueries & CTEs › CTE (WITH Clause)

SQL CTE (WITH Clause)

AdvancedLesson 43 of 6911 min read5 queries run on MySQL 8.4

A CTE (Common Table Expression) gives a subquery a name at the top of your query with WITH name AS (...). You then use it like a table. CTEs turn a nested, inside-out query into readable steps from top to bottom, which is why they are now the standard way to write analytical SQL, and a favourite interview topic.

In this lesson

Write a CTE with WITH ... AS (...)
Chain several CTEs, each building on the last
Reuse one CTE several times
Choose between a CTE, a subquery and a view

CTE syntax

MySQL

WITH cte_name AS (  SELECT ...)SELECT ...FROM cte_name;

A CTE exists only for the one statement that follows it. MySQL supports CTEs from version 8.0.

Sample data

orders · 10 rows

order_id	customer_id	order_date	status
1001	1	2025-01-05	Delivered
1002	2	2025-01-17	Delivered
1003	1	2025-02-02	Delivered
1004	3	2025-02-14	Cancelled
1005	4	2025-03-08	Delivered
1006	5	2025-03-22	Shipped
1007	2	2025-04-10	Delivered
1008	1	2025-04-28	Pending
1009	3	2025-05-15	Delivered
1010	5	2025-06-01	Delivered

order_items · 16 rows

order_id	product_id	quantity
1001	2	1
1001	6	5
1002	4	1
1003	1	1
1003	2	1
1004	5	1
1005	6	10
1005	7	4
1006	3	2
1006	8	1
1007	1	1
1008	4	2
1008	8	2
1009	2	1
1010	6	3
1010	7	2

products · 9 rows (some columns)

product_id	product_name	category	price
1	Wireless Mouse	Electronics	799
2	Mechanical Keyboard	Electronics	3499
3	USB-C Hub	Electronics	1999
4	Office Chair	Furniture	8999
5	Standing Desk	Furniture	18999
6	Notebook Pack	Stationery	249
7	Gel Pens	Stationery	199
8	Desk Lamp	Furniture	1499
9	Monitor Arm	Furniture	2799

A first CTE

Order totals, then the orders above 5,000:

SQL

WITH order_totals AS (  SELECT oi.order_id, SUM(oi.quantity * p.price) AS total  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY oi.order_id)SELECT order_id, totalFROM order_totalsWHERE total > 5000ORDER BY total DESC;

ResultMySQL 8.4

order_id	total
1008	20996
1004	18999
1002	8999
1006	5497

4 rows in set

How this query works, step by step

WITH order_totals AS (...) defines a named result: one row per order with its total.
The main query reads order_totals like a table.
WHERE total > 5000 works directly on the computed column, no repeated expression needed.

Compare with the same query as a derived table: the logic is identical, but you read it from the inside out.

SQL

SELECT order_id, totalFROM (  SELECT oi.order_id, SUM(oi.quantity * p.price) AS total  FROM order_items oi JOIN products p ON p.product_id = oi.product_id  GROUP BY oi.order_id) AS order_totalsWHERE total > 5000ORDER BY total DESC;

ResultMySQL 8.4

order_id	total
1008	20996
1004	18999
1002	8999
1006	5497

4 rows in set

Several CTEs in steps

Separate CTEs with commas. Each one may use the ones defined before it. Customer spend, the average spend, and who is above it:

SQL

WITH order_totals AS (  SELECT o.order_id, o.customer_id, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.order_id, o.customer_id),customer_spend AS (  SELECT customer_id, SUM(total) AS spend, COUNT(*) AS orders  FROM order_totals  GROUP BY customer_id),average AS (  SELECT AVG(spend) AS avg_spend FROM customer_spend)SELECT c.customer_name, cs.orders, cs.spend, ROUND(a.avg_spend) AS avg_spendFROM customer_spend csJOIN customers c ON c.customer_id = cs.customer_idCROSS JOIN average aWHERE cs.spend > a.avg_spendORDER BY cs.spend DESC;

ResultMySQL 8.4

customer_name	orders	spend	avg_spend
Ananya Sharma	3	30038	10653

1 row in set

How this query works, step by step

order_totals: the value of each non-cancelled order.
customer_spend: built from order_totals, one row per customer.
average: one row with the average customer spend.
The final query joins the steps and keeps customers above average.
Each step can be tested on its own by temporarily selecting from it, which makes debugging easy.

Reusing a CTE

A CTE can be referenced more than once in the same query. Each department’s payroll compared with the largest department payroll:

MySQL

WITH dept_pay AS (  SELECT dept_id, SUM(salary) AS payroll  FROM employees  WHERE dept_id IS NOT NULL  GROUP BY dept_id)SELECT d.dept_id, d.payroll,       ROUND(100 * d.payroll / (SELECT MAX(payroll) FROM dept_pay), 1) AS pct_of_largestFROM dept_pay dORDER BY d.payroll DESC;

ResultMySQL 8.4

dept_id	payroll	pct_of_largest
1	392000	100.0
2	207000	52.8
3	133000	33.9
4	78000	19.9

4 rows in set

CTE with UPDATE and DELETE

In MySQL a CTE can also feed an UPDATE or DELETE. Restock every product that sold 3 or more units:

MySQL

WITH best_sellers AS (  SELECT product_id FROM order_items GROUP BY product_id HAVING SUM(quantity) >= 3)UPDATE productsSET stock = stock + 100WHERE product_id IN (SELECT product_id FROM best_sellers); SELECT product_id, product_name, stock FROM products ORDER BY product_id;

ResultMySQL 8.4

mysql> WITH best_sellers AS (

Query OK, 5 rows affected

mysql> SELECT product_id, product_name, stock FROM products ORDER BY product_id

product_id	product_name	stock
1	Wireless Mouse	120
2	Mechanical Keyboard	145
3	USB-C Hub	0
4	Office Chair	112
5	Standing Desk	5
6	Notebook Pack	400
7	Gel Pens	350
8	Desk Lamp	130
9	Monitor Arm	18

9 rows in set

CTE vs subquery vs view

	CTE	Derived table (subquery)	View
Named	yes	alias only	yes
Readable top to bottom	yes	no (inside out)	yes
Reusable in the same query	yes	no	yes
Lives after the query	no	no	yes, stored in the database
Recursive	yes (Recursive CTE)	no	no

Exercise: Category revenue share

Write a CTE cat_rev with revenue per product category, then show each category’s revenue and its percentage of total revenue.

Expected resultMySQL 8.4

category	revenue	pct
Furniture	50493	69.9
Electronics	16093	22.3
Stationery	5676	7.9

3 rows in set

Hint 1

In the main query, divide by (SELECT SUM(revenue) FROM cat_rev).

MySQL

WITH cat_rev AS (  SELECT p.category, SUM(oi.quantity * p.price) AS revenue  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category)SELECT category, revenue,       ROUND(100 * revenue / (SELECT SUM(revenue) FROM cat_rev), 1) AS pctFROM cat_revORDER BY revenue DESC;

ResultMySQL 8.4

category	revenue	pct
Furniture	50493	69.9
Electronics	16093	22.3
Stationery	5676	7.9

3 rows in set

Quick quiz

1. How long does a CTE exist?

2. How do you define two CTEs?

WITH a AS (...), b AS (...) SELECT ...

3. Can a later CTE use an earlier one?

CTE FAQ

What is a CTE in SQL?

A Common Table Expression is a named temporary result defined with WITH name AS (SELECT ...) and used by the statement that follows.

What is the difference between a CTE and a subquery?

They often produce the same plan. A CTE is named, defined up front, can be referenced several times and can be recursive; a subquery is written inline where it is used.

Does MySQL support CTEs?

Yes, from MySQL 8.0, including recursive CTEs.

Are CTEs faster than subqueries?

Not inherently. MySQL may merge a CTE into the outer query or materialize it once; the main benefit is readability.

SQL Correlated Subqueries

SQL Tutorial › Subqueries & CTEs › Correlated Subqueries

SQL Correlated Subqueries

AdvancedLesson 41 of 6910 min read6 queries run on MySQL 8.4

A correlated subquery refers to a column of the outer query, so it cannot run on its own: conceptually it is re-run for every outer row. That makes it the natural way to ask row-by-row questions such as “is this employee the best paid in their own department?” and “what is this customer’s latest order?”.

In this lesson

Recognise a correlated subquery
Trace how it runs row by row
Solve “greatest per group” problems
Rewrite with joins or window functions for speed

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id	salary
100	Meera	NULL	250000
101	Asha	1	125000
102	Rahul	1	98000
103	Priya	1	72000
104	Karan	1	72000
105	Vikram	2	105000
106	Neha	2	54000
107	Arjun	2	48000
108	Kavya	3	88000
109	Rohan	3	45000
110	Sneha	4	78000
111	Amit	1	25000

Correlated vs non-correlated

	Non-correlated	Correlated
Refers to the outer query	no	yes
Can run on its own	yes	no
Runs	once	conceptually once per outer row

Above their department’s average

SQL

SELECT e.first_name, e.dept_id, e.salaryFROM employees eWHERE e.salary > (  SELECT AVG(x.salary)  FROM employees x  WHERE x.dept_id = e.dept_id)ORDER BY e.dept_id, e.salary DESC;

ResultMySQL 8.4

first_name	dept_id	salary
Asha	1	125000
Rahul	1	98000
Vikram	2	105000
Kavya	3	88000

4 rows in set

How this query works, step by step

MySQL takes the first outer row, say Asha (dept 1, 125,000).
The inner query runs with e.dept_id = 1: the Engineering average is 78,400.
125,000 > 78,400, so Asha is kept.
Next row, Priya (dept 1, 72,000): same average, 72,000 is not higher, so she is dropped.
This repeats for every employee, each compared with their own department. The CEO’s department is NULL, so x.dept_id = NULL matches nothing, the average is NULL and she is dropped.

Greatest per group

The top earner in each department:

SQL

SELECT e.dept_id, e.first_name, e.salaryFROM employees eWHERE e.salary = (  SELECT MAX(x.salary)  FROM employees x  WHERE x.dept_id = e.dept_id)ORDER BY e.dept_id;

ResultMySQL 8.4

dept_id	first_name	salary
1	Asha	125000
2	Vikram	105000
3	Kavya	88000
4	Sneha	78000

4 rows in set

How this query works, step by step

For each employee, the subquery finds the maximum salary of their department.
The employee is kept only if they earn exactly that maximum.
If two people tied for the top, both would be returned.

Latest order of each customer

SQL

SELECT o.customer_id, o.order_id, o.order_date, o.statusFROM orders oWHERE o.order_date = (  SELECT MAX(o2.order_date)  FROM orders o2  WHERE o2.customer_id = o.customer_id)ORDER BY o.customer_id;

ResultMySQL 8.4

customer_id	order_id	order_date	status
1	1008	2025-04-28	Pending
2	1007	2025-04-10	Delivered
3	1009	2025-05-15	Delivered
4	1005	2025-03-08	Delivered
5	1010	2025-06-01	Delivered

5 rows in set

Correlated subquery in SELECT

Number of orders next to each customer:

SQL

SELECT c.customer_name,       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS ordersFROM customers cORDER BY orders DESC, c.customer_name;

ResultMySQL 8.4

customer_name	orders
Ananya Sharma	3
Fatima Khan	2
Ishita Bose	2
Rohit Kumar	2
David Fernandes	1
Manoj Pillai	0
Sara Thomas	0

7 rows in set

How this query works, step by step

For each customer row, the subquery counts that customer’s orders.
Customers without orders get 0 (COUNT of an empty set), unlike an inner join, which would drop them.

Correlated UPDATE

A correlated subquery can also supply the new value in an UPDATE. Copy the products into a product_sales table, then fill in each product’s total units ordered:

MySQL

CREATE TABLE product_sales AS SELECT product_id, product_name, 0 AS units FROM products;UPDATE product_sales psSET units = (SELECT COALESCE(SUM(oi.quantity), 0) FROM order_items oi WHERE oi.product_id = ps.product_id);SELECT * FROM product_sales ORDER BY units DESC, product_id;

ResultMySQL 8.4

mysql> CREATE TABLE product_sales AS SELECT product_id, product_name, 0 AS un…

Query OK, 9 rows affected

mysql> UPDATE product_sales ps

Query OK, 8 rows affected

mysql> SELECT * FROM product_sales ORDER BY units DESC, product_id

product_id	product_name	units
6	Notebook Pack	18
7	Gel Pens	6
2	Mechanical Keyboard	3
4	Office Chair	3
8	Desk Lamp	3
1	Wireless Mouse	2
3	USB-C Hub	2
5	Standing Desk	1
9	Monitor Arm	0

9 rows in set

Performance and alternatives

Running a query once per row sounds slow, and on large tables it can be. MySQL 8’s optimizer often rewrites correlated subqueries into joins automatically, and an index on the correlated column (dept_id, customer_id) helps a lot. Two common rewrites:

Join to a pre-aggregated derived table:

SQL

SELECT e.dept_id, e.first_name, e.salaryFROM employees eJOIN (SELECT dept_id, MAX(salary) AS max_salary FROM employees GROUP BY dept_id) m  ON m.dept_id = e.dept_id AND m.max_salary = e.salaryORDER BY e.dept_id;

ResultMySQL 8.4

dept_id	first_name	salary
1	Asha	125000
2	Vikram	105000
3	Kavya	88000
4	Sneha	78000

4 rows in set

Or a window function, covered in Top N per Group.

Exercise: Most expensive product per category

List the most expensive product of each category using a correlated subquery.

Expected resultMySQL 8.4

category	product_name	price
Electronics	Mechanical Keyboard	3499
Furniture	Standing Desk	18999
Stationery	Notebook Pack	249

3 rows in set

Hint 1

Compare price with the MAX(price) of the same category.

SQL

SELECT p.category, p.product_name, p.priceFROM products pWHERE p.price = (SELECT MAX(x.price) FROM products x WHERE x.category = p.category)ORDER BY p.category;

ResultMySQL 8.4

category	product_name	price
Electronics	Mechanical Keyboard	3499
Furniture	Standing Desk	18999
Stationery	Notebook Pack	249

3 rows in set

Quick quiz

1. What makes a subquery correlated?

2. Can a correlated subquery be run on its own?

Without the outer query, e.dept_id is unknown.

3. What usually makes correlated subqueries fast on big tables?

Correlated subquery FAQ

What is a correlated subquery?

A subquery that uses values from the outer query, so it is evaluated for each row of the outer query.

What is the difference between a correlated and a nested subquery?

A plain nested subquery is independent and runs once. A correlated subquery depends on the outer row and conceptually runs once per row.

Are correlated subqueries slow?

They can be on large tables without indexes. MySQL often optimizes them into joins; window functions or joins to aggregated derived tables are common faster alternatives.

SQL Constraints and DEFAULT

SQL Tutorial › Database Design › Constraints & DEFAULT

SQL Constraints and DEFAULT

IntermediateLesson 50 of 6911 min read7 queries run on MySQL 8.4

Constraints are rules the database enforces on every insert and update, so bad data never gets in, no matter which application, script or person writes it. This lesson shows all six MySQL constraints on one table, what each error looks like, and covers DEFAULT in detail. Each constraint then has its own lesson.

In this lesson

The six constraints and what each one prevents
Column-level vs table-level and named constraints
Give columns default values with DEFAULT
List, add and drop constraints

The six constraints

Constraint	Rule	Lesson
`NOT NULL`	the column must have a value	NOT NULL
`UNIQUE`	no two rows may share a value	UNIQUE
`PRIMARY KEY`	unique + not null; identifies each row	PRIMARY KEY
`FOREIGN KEY`	the value must exist in another table	FOREIGN KEY
`CHECK`	the value must satisfy a condition	CHECK
`DEFAULT`	value used when none is given	this lesson

All of them in one table

SQL

CREATE TABLE members (  member_id  INT PRIMARY KEY,  email      VARCHAR(100) NOT NULL UNIQUE,  full_name  VARCHAR(60)  NOT NULL,  dept_id    INT,  age        INT CHECK (age >= 18),  plan       VARCHAR(10)  NOT NULL DEFAULT 'free',  joined_on  DATE         DEFAULT (CURRENT_DATE),  CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members (member_id, email, full_name, dept_id, age)VALUES (1, 'asha@example.com', 'Asha Rao', 1, 30);SELECT member_id, email, plan, joined_on IS NOT NULL AS has_join_date FROM members;

ResultMySQL 8.4

mysql> CREATE TABLE members (

Query OK, 0 rows affected

mysql> INSERT INTO members (member_id, email, full_name, dept_id, age)

Query OK, 1 row affected

mysql> SELECT member_id, email, plan, joined_on IS NOT NULL AS has_join_date …

member_id	email	plan	has_join_date
1	asha@example.com	free	1

1 row in set

How this query works, step by step

PRIMARY KEY on member_id, NOT NULL UNIQUE on email, CHECK on age, a named FOREIGN KEY on dept_id.
The insert leaves out plan and joined_on, so their defaults are used: 'free' and today’s date.
The SELECT confirms the defaults were applied.

What each violation looks like

Each of these inserts breaks one rule (every block starts from the table above with Asha’s row):

MySQL

CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members VALUES (1, 'asha@example.com', 'Asha Rao', 1, 30);INSERT INTO members VALUES (2, 'asha@example.com', 'Second Asha', 1, 25);

ResultMySQL 8.4

mysql> CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NO…

Query OK, 0 rows affected

mysql> INSERT INTO members VALUES (1, 'asha@example.com', 'Asha Rao', 1, 30)

Query OK, 1 row affected

mysql> INSERT INTO members VALUES (2, 'asha@example.com', 'Second Asha', 1, 25)

ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'members.email'

MySQL

CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members VALUES (3, 'teen@example.com', 'Young User', 1, 15);

ResultMySQL 8.4

mysql> CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NO…

Query OK, 0 rows affected

mysql> INSERT INTO members VALUES (3, 'teen@example.com', 'Young User', 1, 15)

ERROR 3819 (HY000): Check constraint 'members_chk_1' is violated.

MySQL

CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, full_name VARCHAR(60) NOT NULL, dept_id INT, age INT CHECK (age >= 18), CONSTRAINT fk_member_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO members VALUES (4, 'x@example.com', 'Nobody', 42, 30);

ResultMySQL 8.4

mysql> CREATE TABLE members (member_id INT PRIMARY KEY, email VARCHAR(100) NO…

Query OK, 0 rows affected

mysql> INSERT INTO members VALUES (4, 'x@example.com', 'Nobody', 42, 30)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`members`, CONSTRAINT `fk_member_dept` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))

How this query works, step by step

Error 1062: the email is already used (UNIQUE).
Error 3819: age 15 fails CHECK (age >= 18).
Error 1452: there is no department 42 (FOREIGN KEY).

Column-level vs table-level

A constraint written next to a column applies to that column. A constraint written as a separate line (table-level) can cover several columns and can be named:

MySQL

CREATE TABLE enrolments (  student_id INT,  course_id  INT,  enrolled   DATE NOT NULL,  CONSTRAINT pk_enrolments PRIMARY KEY (student_id, course_id),  CONSTRAINT chk_enrolled CHECK (enrolled >= '2020-01-01'));

Tip

Name your constraints (fk_member_dept, chk_age). The name appears in error messages and is what you use to drop the constraint later.

DEFAULT

DEFAULT gives a column a value when an INSERT does not supply one. Since MySQL 8.0.13 a default can be an expression in parentheses:

MySQL

CREATE TABLE tasks (  task_id    INT PRIMARY KEY,  title      VARCHAR(50) NOT NULL,  status     VARCHAR(12) NOT NULL DEFAULT 'open',  priority   INT DEFAULT 3,  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,  code       VARCHAR(40) DEFAULT (UUID()));INSERT INTO tasks (task_id, title) VALUES (1, 'Write test plan');INSERT INTO tasks (task_id, title, status, priority) VALUES (2, 'Fix login bug', 'in_progress', 1);INSERT INTO tasks (task_id, title, priority) VALUES (3, 'Review', DEFAULT);SELECT task_id, title, status, priority, created_at IS NOT NULL AS stamped, CHAR_LENGTH(code) AS code_len FROM tasks;

ResultMySQL 8.4

mysql> CREATE TABLE tasks (

Query OK, 0 rows affected

mysql> INSERT INTO tasks (task_id, title) VALUES (1, 'Write test plan')

Query OK, 1 row affected

mysql> INSERT INTO tasks (task_id, title, status, priority) VALUES (2, 'Fix l…

Query OK, 1 row affected

mysql> INSERT INTO tasks (task_id, title, priority) VALUES (3, 'Review', DEFA…

Query OK, 1 row affected

mysql> SELECT task_id, title, status, priority, created_at IS NOT NULL AS sta…

task_id	title	status	priority	stamped	code_len
1	Write test plan	open	3	1	36
2	Fix login bug	in_progress	1	1	36
3	Review	open	3	1	36

3 rows in set

How this query works, step by step

Task 1 gives only id and title: status 'open', priority 3, a timestamp and a generated UUID are filled in.
Task 2 gives its own status and priority, so the defaults are not used for those.
Task 3 writes the keyword DEFAULT explicitly, which also uses the default value.

Note

An explicit NULL is not replaced by the default: INSERT ... (priority) VALUES (NULL) stores NULL. Add NOT NULL if that must not happen.

See a table’s constraints

MySQL

SELECT constraint_name, constraint_typeFROM information_schema.table_constraintsWHERE table_schema = 'company' AND table_name = 'employees';

ResultMySQL 8.4

CONSTRAINT_NAME	CONSTRAINT_TYPE
PRIMARY	PRIMARY KEY
employees_ibfk_1	FOREIGN KEY

2 rows in set

SHOW CREATE TABLE employees; shows all constraints in the original syntax.

Add and drop constraints later

MySQL

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);ALTER TABLE products ALTER stock SET DEFAULT 0;SELECT constraint_name, constraint_type FROM information_schema.table_constraintsWHERE table_schema = 'company' AND table_name = 'products';ALTER TABLE products DROP CHECK chk_price;

ResultMySQL 8.4

mysql> ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0)

Query OK, 9 rows affected

mysql> ALTER TABLE products ALTER stock SET DEFAULT 0

Query OK, 0 rows affected

mysql> SELECT constraint_name, constraint_type FROM information_schema.table_…

CONSTRAINT_NAME	CONSTRAINT_TYPE
PRIMARY	PRIMARY KEY
chk_price	CHECK

2 rows in set

mysql> ALTER TABLE products DROP CHECK chk_price

Query OK, 0 rows affected

Adding a constraint fails if existing rows already break it, so clean the data first.

Quick quiz

1. Which constraint makes sure a value exists in another table?

2. An INSERT gives NULL explicitly for a column with DEFAULT 3. What is stored?

3. Why name a constraint?

MySQL generates names like members_chk_1 otherwise.

Constraints FAQ

What are constraints in SQL?

Rules defined on columns or tables that the database enforces: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT.

What is the difference between a primary key and a unique key?

Both prevent duplicates. A table has only one primary key and it cannot be NULL; it can have many unique keys, which allow NULLs.

Can a DEFAULT value be a function?

Yes, since MySQL 8.0.13, as an expression in parentheses, e.g. DEFAULT (UUID()) or DEFAULT (CURRENT_DATE). CURRENT_TIMESTAMP works without parentheses for DATETIME and TIMESTAMP.

SQL CAST and CONVERT

SQL Tutorial › Functions & Grouping › CAST & CONVERT

SQL CAST and CONVERT

IntermediateLesson 30 of 698 min read7 queries run on MySQL 8.4

Sometimes a value has the wrong type for the job: numbers stored as text, dates arriving as strings, an average that needs fixed decimals. CAST and CONVERT change the type of a value inside a query. Knowing them also explains the strange results MySQL’s automatic conversion can produce.

In this lesson

Convert values with CAST(x AS type) and CONVERT(x, type)
Turn text into numbers and dates, and back
Control precision with DECIMAL(p,s)
Spot implicit conversion surprises

CAST syntax

MySQL

CAST(expression AS type)CONVERT(expression, type)

The target types MySQL accepts in CAST are: SIGNED, UNSIGNED, DECIMAL(p,s), DOUBLE, FLOAT, CHAR, DATE, TIME, DATETIME, YEAR, BINARY and JSON. Note: INT and VARCHAR are not valid targets in MySQL; use SIGNED and CHAR.

Text to number

MySQL

SELECT CAST('42' AS SIGNED)            AS to_int,       CAST('19.99' AS DECIMAL(6,2))   AS to_decimal,       CAST('3.7' AS SIGNED)           AS truncated_int,       '10' + 5                        AS implicit;

ResultMySQL 8.4

to_int	to_decimal	truncated_int	implicit
42	19.99	3	15

1 row in set

How this query works, step by step

CAST('42' AS SIGNED) turns the text into the integer 42.
DECIMAL(6,2) keeps two decimals.
Casting the text '3.7' to an integer reads digits up to the . and gives 3, with a “truncated incorrect value” warning. (Casting the number 3.7, without quotes, rounds to 4.)
'10' + 5 shows implicit conversion: MySQL silently converts the text for arithmetic and returns 15.

Sorting numbers stored as text

A common bug: codes stored in a VARCHAR sort alphabetically, so '10' comes before '9':

SQL

CREATE TABLE tickets (ticket_no VARCHAR(10));INSERT INTO tickets VALUES ('9'), ('10'), ('2'), ('100');SELECT ticket_no FROM tickets ORDER BY ticket_no;SELECT ticket_no FROM tickets ORDER BY CAST(ticket_no AS UNSIGNED);

ResultMySQL 8.4

mysql> CREATE TABLE tickets (ticket_no VARCHAR(10))

Query OK, 0 rows affected

mysql> INSERT INTO tickets VALUES ('9'), ('10'), ('2'), ('100')

Query OK, 4 rows affected

mysql> SELECT ticket_no FROM tickets ORDER BY ticket_no

ticket_no
10
100
2
9

4 rows in set

mysql> SELECT ticket_no FROM tickets ORDER BY CAST(ticket_no AS UNSIGNED)

ticket_no
2
9
10
100

4 rows in set

How this query works, step by step

The first sort compares text character by character: '1' < '2' < '9', so 10 and 100 come first.
Casting to UNSIGNED sorts by numeric value: 2, 9, 10, 100.
The real fix is to store numbers in a numeric column.

Number to text

MySQL

SELECT emp_id,       CAST(emp_id AS CHAR)                      AS id_text,       CONCAT('EMP-', emp_id)                    AS implicit_text,       CHAR_LENGTH(CAST(salary AS CHAR))         AS salary_digitsFROM employeesLIMIT 3;

ResultMySQL 8.4

emp_id	id_text	implicit_text	salary_digits
100	100	EMP-100	6
101	101	EMP-101	6
102	102	EMP-102	5

3 rows in set

CONCAT converts numbers to text automatically, so an explicit CAST is rarely needed there.

Text to date

MySQL

SELECT CAST('2026-01-31' AS DATE)                       AS iso_text,       CAST('2026-01-31 18:30:00' AS DATETIME)          AS with_time,       CAST('2026-01-31 18:30:00' AS DATE)              AS date_only,       STR_TO_DATE('31-01-2026', '%d-%m-%Y')            AS other_format;

ResultMySQL 8.4

iso_text	with_time	date_only	other_format
2026-01-31	2026-01-31 18:30:00	2026-01-31	2026-01-31

1 row in set

How this query works, step by step

CAST(... AS DATE) understands the ISO format YYYY-MM-DD.
Casting a datetime to DATE drops the time.
For any other layout use STR_TO_DATE with a format string (Date Functions).

DECIMAL for fixed precision

MySQL

SELECT dept_id,       AVG(salary)                      AS raw_avg,       CAST(AVG(salary) AS DECIMAL(10,2)) AS avg_2dpFROM employeesWHERE dept_id IN (1, 2)GROUP BY dept_id;

ResultMySQL 8.4

dept_id	raw_avg	avg_2dp
1	78400.0000	78400.00
2	69000.0000	69000.00

2 rows in set

CONVERT and character sets

CONVERT(x, type) is MySQL’s alternative syntax for CAST. CONVERT(x USING charset) converts between character sets:

MySQL

SELECT CONVERT('123', SIGNED) AS same_as_cast,       CHARSET(CONVERT('café' USING latin1)) AS latin1_charset;

ResultMySQL 8.4

same_as_cast	latin1_charset
123	latin1

1 row in set

Implicit conversion surprises

When you compare text with a number, MySQL converts the text to a number. Text that does not start with digits becomes 0:

MySQL

SELECT 'abc' = 0     AS text_equals_zero,       '12abc' = 12  AS leading_digits,       '1e3' = 1000  AS scientific;

ResultMySQL 8.4

text_equals_zero	leading_digits	scientific
1	1	1

1 row in set

How this query works, step by step

'abc' becomes 0, so 'abc' = 0 is true (1), with a warning.
'12abc' becomes 12.
'1e3' is read as scientific notation, 1000.

Watch out

Comparing a VARCHAR column with a number (for example WHERE phone = 9845012345) converts every row’s value, cannot use an index, and can match unexpected rows. Compare text columns with quoted text.

Quick quiz

1. Which CAST is valid in MySQL?

2. What does SELECT '10' + 5 return in MySQL?

3. Why do the text values ’10’, ‘9’, ‘100’ sort as ’10’, ‘100’, ‘9’?

CAST and CONVERT FAQ

What is the difference between CAST and CONVERT in MySQL?

CAST(x AS type) is standard SQL. CONVERT(x, type) does the same in MySQL, and CONVERT(x USING charset) also converts character sets.

How do I convert a string to a number in MySQL?

Use CAST(text AS SIGNED), CAST(text AS UNSIGNED) or CAST(text AS DECIMAL(p,s)). Adding 0 (text + 0) also works through implicit conversion.

How do I convert a string to a date in SQL?

CAST('2026-01-31' AS DATE) works for ISO dates. For other formats use STR_TO_DATE(text, format).

Why can’t I CAST to INT in MySQL?

MySQL’s CAST accepts SIGNED [INTEGER] and UNSIGNED [INTEGER] as integer targets, not INT. (MySQL 8.0.17 and later also accept CAST(x AS FLOAT) and DOUBLE.)

SQL String Functions

SQL Tutorial › Functions & Grouping › String Functions

SQL String Functions

IntermediateLesson 26 of 6912 min read8 queries run on MySQL 8.4

Real data needs cleaning and formatting: full names from first and last names, domains from email addresses, codes padded with zeros, stray spaces trimmed. String functions do this inside the query, so the database returns data ready to use.

In this lesson

Join text with CONCAT and CONCAT_WS
Measure and change case with LENGTH, CHAR_LENGTH, UPPER, LOWER
Cut text with SUBSTRING, LEFT, RIGHT, SUBSTRING_INDEX
Clean text with TRIM, REPLACE, LPAD

Quick reference

Function	Example	Result
`CONCAT(a, b, ...)`	`CONCAT('SQA', 'Tools')`	SQATools
`CONCAT_WS(sep, a, b)`	`CONCAT_WS('-', 'IN', 'MH')`	IN-MH
`LENGTH(s)`	`LENGTH('café')`	5 (bytes)
`CHAR_LENGTH(s)`	`CHAR_LENGTH('café')`	4 (characters)
`UPPER(s)` / `LOWER(s)`	`UPPER('pune')`	PUNE
`SUBSTRING(s, pos, len)`	`SUBSTRING('Database', 1, 4)`	Data
`LEFT(s, n)` / `RIGHT(s, n)`	`RIGHT('2026-01', 2)`	01
`INSTR(s, sub)`	`INSTR('a@b.com', '@')`	2
`SUBSTRING_INDEX(s, d, n)`	`SUBSTRING_INDEX('a@b.com', '@', -1)`	b.com
`REPLACE(s, from, to)`	`REPLACE('2026/01/31', '/', '-')`	2026-01-31
`TRIM(s)`	`TRIM(' hi ')`	hi
`LPAD(s, n, pad)`	`LPAD('7', 3, '0')`	007
`REVERSE(s)`	`REVERSE('abc')`	cba

Sample data

customers · 7 rows

customer_id	customer_name	email	city	joined_on
1	Ananya Sharma	ananya@example.com	Mumbai	2024-01-15
2	Rohit Kumar	rohit.k@example.com	Delhi	2024-02-03
3	Fatima Khan	fatima@example.com	Hyderabad	2024-03-21
4	David Fernandes	NULL	Goa	2024-05-09
5	Ishita Bose	ishita@example.com	Kolkata	2024-07-30
6	Manoj Pillai	manoj.p@example.com	Chennai	2025-01-11
7	Sara Thomas	sara.t@example.com	Bengaluru	2025-03-02

CONCAT and CONCAT_WS

SQL

SELECT emp_id,       CONCAT(first_name, ' ', last_name)           AS full_name,       CONCAT_WS(' | ', job_title, city)            AS role_and_cityFROM employeesWHERE dept_id = 2;

ResultMySQL 8.4

emp_id	full_name	role_and_city
105	Vikram Singh	Sales Manager \| Mumbai
106	Neha Gupta	Sales Executive \| Mumbai
107	Arjun Das	Sales Executive \| Kolkata

3 rows in set

How this query works, step by step

CONCAT glues its arguments together in order; the ' ' adds the space.
CONCAT_WS (“with separator”) puts ' | ' between each pair of values.

CONCAT returns NULL if any argument is NULL, while CONCAT_WS skips NULLs:

MySQL

SELECT customer_name,       CONCAT(customer_name, ' <', email, '>')     AS with_concat,       CONCAT_WS(' ', customer_name, email)        AS with_concat_wsFROM customersWHERE customer_id IN (3, 4);

ResultMySQL 8.4

customer_name	with_concat	with_concat_ws
Fatima Khan	Fatima Khan <fatima@example.com>	Fatima Khan fatima@example.com
David Fernandes	NULL	David Fernandes

2 rows in set

UPPER, LOWER and LENGTH

MySQL

SELECT customer_name,       UPPER(customer_name)       AS upper_name,       LOWER(city)                AS lower_city,       CHAR_LENGTH(customer_name) AS name_lengthFROM customersLIMIT 3;

ResultMySQL 8.4

customer_name	upper_name	lower_city	name_length
Ananya Sharma	ANANYA SHARMA	mumbai	13
Rohit Kumar	ROHIT KUMAR	delhi	11
Fatima Khan	FATIMA KHAN	hyderabad	11

3 rows in set

Note

LENGTH counts bytes, CHAR_LENGTH counts characters. They differ for accented letters, Hindi or emoji in utf8mb4, so use CHAR_LENGTH for “how many letters”.

SUBSTRING, LEFT and RIGHT

Build a short employee code: the first three letters of the last name in capitals plus the employee id:

MySQL

SELECT first_name, last_name,       CONCAT(UPPER(LEFT(last_name, 3)), emp_id) AS emp_code,       SUBSTRING(first_name, 2, 3)              AS letters_2_to_4FROM employeesWHERE dept_id = 1;

ResultMySQL 8.4

first_name	last_name	emp_code	letters_2_to_4
Asha	Rao	RAO101	sha
Rahul	Mehta	MEH102	ahu
Priya	Nair	NAI103	riy
Karan	Shah	SHA104	ara
Amit	Verma	VER111	mit

5 rows in set

How this query works, step by step

LEFT(last_name, 3) takes the first three characters: Rao, Meh, Nai…
UPPER capitalises them and CONCAT appends the id: RAO101.
SUBSTRING(first_name, 2, 3) starts at position 2 (SQL counts from 1) and takes 3 characters.

Split an email: INSTR and SUBSTRING_INDEX

MySQL

SELECT email,       INSTR(email, '@')                 AS at_position,       SUBSTRING_INDEX(email, '@', 1)    AS username,       SUBSTRING_INDEX(email, '@', -1)   AS domainFROM customersWHERE email IS NOT NULLLIMIT 4;

ResultMySQL 8.4

email	at_position	username	domain
ananya@example.com	7	ananya	example.com
rohit.k@example.com	8	rohit.k	example.com
fatima@example.com	7	fatima	example.com
ishita@example.com	7	ishita	example.com

4 rows in set

How this query works, step by step

INSTR(email, '@') returns the position of @.
SUBSTRING_INDEX(email, '@', 1) returns everything before the first @.
With -1 it counts from the right and returns everything after the last @.

TRIM and REPLACE: clean messy input

MySQL

SELECT CONCAT('[', TRIM('   Pune  '), ']')            AS trimmed,       CONCAT('[', LTRIM('   Pune  '), ']')           AS left_trimmed,       TRIM(LEADING '0' FROM '000742')                AS no_zeros,       REPLACE('98450-12345', '-', '')                AS digits_only,       REPLACE(REPLACE(' +91 98450 ', '+91', ''), ' ', '') AS phone;

ResultMySQL 8.4

trimmed	left_trimmed	no_zeros	digits_only	phone
[Pune]	[Pune ]	742	9845012345	98450

1 row in set

How this query works, step by step

TRIM removes spaces from both ends; the brackets show where the text starts and ends.
LTRIM removes only the leading spaces.
TRIM(LEADING '0' FROM ...) removes a chosen character from the start.
REPLACE swaps every occurrence of one text for another; nesting it applies several replacements.

LPAD and RPAD

MySQL

SELECT order_id,       CONCAT('INV-', LPAD(order_id - 1000, 5, '0')) AS invoice_noFROM ordersLIMIT 3;

ResultMySQL 8.4

order_id	invoice_no
1001	INV-00001
1003	INV-00003
1008	INV-00008

3 rows in set

How this query works, step by step

order_id - 1000 turns 1001 into 1.
LPAD(..., 5, '0') pads it on the left with zeros to 5 characters: 00001.
CONCAT adds the prefix: INV-00001.

String functions in WHERE

Customers whose email is on a domain other than example.com:

MySQL

SELECT customer_name, emailFROM customersWHERE SUBSTRING_INDEX(email, '@', -1) <> 'example.com';

ResultMySQL 8.4

Empty set

Watch out

Wrapping a column in a function inside WHERE (for example WHERE UPPER(city) = 'PUNE') usually prevents MySQL from using an index on that column. Prefer conditions on the raw column, such as LIKE 'Pu%', on large tables.

Exercise: Initials

Show each employee’s initials, for example A.R. for Asha Rao.

Expected resultMySQL 8.4

first_name	last_name	initials
Meera	Kapoor	M.K.
Asha	Rao	A.R.
Rahul	Mehta	R.M.
Priya	Nair	P.N.
Karan	Shah	K.S.
Vikram	Singh	V.S.
Neha	Gupta	N.G.
Arjun	Das	A.D.
Kavya	Iyer	K.I.
Rohan	Joshi	R.J.
Sneha	Kulkarni	S.K.
Amit	Verma	A.V.

12 rows in set

Hint 1

LEFT(first_name, 1) and LEFT(last_name, 1), joined with dots by CONCAT.

MySQL

SELECT first_name, last_name,       CONCAT(LEFT(first_name, 1), '.', LEFT(last_name, 1), '.') AS initialsFROM employees;

ResultMySQL 8.4

first_name	last_name	initials
Meera	Kapoor	M.K.
Asha	Rao	A.R.
Rahul	Mehta	R.M.
Priya	Nair	P.N.
Karan	Shah	K.S.
Vikram	Singh	V.S.
Neha	Gupta	N.G.
Arjun	Das	A.D.
Kavya	Iyer	K.I.
Rohan	Joshi	R.J.
Sneha	Kulkarni	S.K.
Amit	Verma	A.V.

12 rows in set

Exercise: Email domains

Count customers per email domain, ignoring customers without an email.

Expected resultMySQL 8.4

domain	customers
example.com	6

1 row in set

Hint 1

Group by SUBSTRING_INDEX(email, '@', -1).

MySQL

SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS customersFROM customersWHERE email IS NOT NULLGROUP BY domain;

ResultMySQL 8.4

domain	customers
example.com	6

1 row in set

Quick quiz

1. What does CONCAT('a', NULL, 'b') return in MySQL?

2. At which position does SQL start counting characters?

SUBSTRING('Data', 1, 1) is D.

3. Which function returns the part of an email after the @?

4. LPAD('42', 5, '0') returns…

String functions FAQ

How do I concatenate strings in MySQL?

Use CONCAT(a, b, ...), or CONCAT_WS(separator, a, b, ...) to add a separator and skip NULLs. MySQL does not use + or || for text by default.

How do I extract part of a string in SQL?

Use SUBSTRING(text, start, length), LEFT(text, n), RIGHT(text, n), or SUBSTRING_INDEX(text, delimiter, count) to split on a character.

What is the difference between LENGTH and CHAR_LENGTH?

LENGTH returns the number of bytes; CHAR_LENGTH returns the number of characters. They differ for multi-byte characters such as é or emoji.

How do I remove spaces from a string in SQL?

TRIM() removes leading and trailing spaces, LTRIM() and RTRIM() one side only, and REPLACE(text, ' ', '') removes every space.

SQL Numeric Functions

SQL Tutorial › Functions & Grouping › Numeric Functions

SQL Numeric Functions

IntermediateLesson 27 of 699 min read7 queries run on MySQL 8.4

Reports are full of arithmetic: rounding averages, working out percentages, splitting items into boxes, finding even ids. MySQL’s numeric functions handle all of it in the query. This lesson covers the ones you will use weekly and the integer-division trap that catches many people.

In this lesson

Round with ROUND, TRUNCATE, CEIL and FLOOR
Use MOD, DIV, ABS, POWER and SQRT
Calculate percentages without rounding errors
Know how / behaves in MySQL

Quick reference

MySQL

SELECT ROUND(1234.567, 2)    AS round_2,       ROUND(1234.567, -2)   AS round_hundreds,       TRUNCATE(1234.567, 1) AS truncate_1,       CEIL(4.1)             AS ceil_,       FLOOR(4.9)            AS floor_,       ABS(-15)              AS abs_,       MOD(17, 5)            AS mod_,       17 DIV 5              AS int_div,       17 / 5                AS division,       POWER(2, 10)          AS power_,       SQRT(144)             AS sqrt_;

ResultMySQL 8.4

round_2	round_hundreds	truncate_1	ceil_	floor_	abs_	mod_	int_div	division	power_	sqrt_
1234.57	1200	1234.5	5	4	15	2	3	3.4000	1024	12

1 row in set

How this query works, step by step

ROUND(x, 2) rounds to 2 decimals; a negative precision rounds to tens, hundreds…: 1,200.
TRUNCATE(x, 1) cuts after one decimal without rounding: 1234.5.
CEIL rounds up to the next whole number, FLOOR down.
MOD(17, 5) is the remainder (2) and 17 DIV 5 the whole-number quotient (3).
/ always returns a decimal in MySQL: 3.4000.

ROUND an average

MySQL

SELECT dept_id,       AVG(salary)           AS raw_avg,       ROUND(AVG(salary), 1) AS avg_1dp,       ROUND(AVG(salary))    AS avg_wholeFROM employeesWHERE dept_id IS NOT NULLGROUP BY dept_idORDER BY dept_id;

ResultMySQL 8.4

dept_id	raw_avg	avg_1dp	avg_whole
1	78400.0000	78400.0	78400
2	69000.0000	69000.0	69000
3	66500.0000	66500.0	66500
4	78000.0000	78000.0	78000

4 rows in set

How this query works, step by step

AVG returns an exact decimal with 4 places.
ROUND(..., 1) keeps one decimal; ROUND(x) with no precision rounds to a whole number.
Exact values ending in .5 round away from zero, so 63,500.5 would become 63,501.

CEIL: how many boxes?

Each box holds 24 units. How many boxes does each product’s stock need?

MySQL

SELECT product_name, stock,       CEIL(stock / 24) AS boxes_needed,       MOD(stock, 24)   AS units_in_last_boxFROM productsWHERE stock > 0;

ResultMySQL 8.4

product_name	stock	boxes_needed	units_in_last_box
Wireless Mouse	120	5	0
Mechanical Keyboard	45	2	21
Office Chair	12	1	12
Standing Desk	5	1	5
Notebook Pack	300	13	12
Gel Pens	250	11	10
Desk Lamp	30	2	6
Monitor Arm	18	1	18

8 rows in set

How this query works, step by step

stock / 24 gives a fraction, such as 120 / 24 = 5 or 45 / 24 = 1.875.
CEIL rounds up, because a partly filled box is still a box.
MOD(stock, 24) shows how many units go in the last box (0 means it is full).

Percentages

Each department’s share of the total payroll:

MySQL

SELECT dept_id,       SUM(salary) AS payroll,       ROUND(100 * SUM(salary) / (SELECT SUM(salary) FROM employees), 1) AS pct_of_totalFROM employeesGROUP BY dept_idORDER BY payroll DESC;

ResultMySQL 8.4

dept_id	payroll	pct_of_total
1	392000	37.0
NULL	250000	23.6
2	207000	19.5
3	133000	12.5
4	78000	7.4

5 rows in set

How this query works, step by step

The subquery (SELECT SUM(salary) FROM employees) is the company total: 1,060,000.
Each department’s payroll is multiplied by 100 before dividing, which keeps precision.
ROUND(..., 1) formats the percentage.

Note

In MySQL / never truncates: 7 / 2 is 3.5000. In SQL Server, PostgreSQL and SQLite, dividing two integers drops the fraction (7 / 2 = 3), so write 7 * 1.0 / 2 there.

MOD: even and odd

MySQL

SELECT emp_id, first_name,       CASE WHEN MOD(emp_id, 2) = 0 THEN 'even' ELSE 'odd' END AS id_typeFROM employeesWHERE dept_id = 2;

ResultMySQL 8.4

emp_id	first_name	id_type
105	Vikram	odd
106	Neha	even
107	Arjun	odd

3 rows in set

Every other row, a common interview question:

MySQL

SELECT emp_id, first_nameFROM employeesWHERE MOD(emp_id, 2) = 0;

ResultMySQL 8.4

emp_id	first_name
100	Meera
102	Rahul
104	Karan
106	Neha
108	Kavya
110	Sneha

6 rows in set

ABS, SIGN and differences

How far is each salary from 75,000, regardless of direction?

SQL

SELECT first_name, salary,       salary - 75000       AS difference,       ABS(salary - 75000)  AS distance,       SIGN(salary - 75000) AS above_or_belowFROM employeesWHERE dept_id IN (1, 4)ORDER BY distance;

ResultMySQL 8.4

first_name	salary	difference	distance	above_or_below
Priya	72000	-3000	3000	-1
Karan	72000	-3000	3000	-1
Sneha	78000	3000	3000	1
Rahul	98000	23000	23000	1
Asha	125000	50000	50000	1
Amit	25000	-50000	50000	-1

6 rows in set

Random numbers

RAND() returns a random number from 0 to 1. Pick one random product (fine for small tables; slow on large ones because every row is sorted):

MySQL

SELECT product_name FROM products ORDER BY RAND() LIMIT 1;

A random whole number from 1 to 6, like a dice roll: FLOOR(1 + RAND() * 6).

Exercise: Price with tax

Show each product’s price, the 18% GST amount rounded to 2 decimals, and the total price rounded to a whole number.

Expected resultMySQL 8.4

product_name	price	gst	price_with_gst
Wireless Mouse	799	143.82	943
Mechanical Keyboard	3499	629.82	4129
USB-C Hub	1999	359.82	2359
Office Chair	8999	1619.82	10619
Standing Desk	18999	3419.82	22419
Notebook Pack	249	44.82	294
Gel Pens	199	35.82	235
Desk Lamp	1499	269.82	1769
Monitor Arm	2799	503.82	3303

9 rows in set

Hint 1

ROUND(price * 0.18, 2) and ROUND(price * 1.18).

SQL

SELECT product_name, price,       ROUND(price * 0.18, 2) AS gst,       ROUND(price * 1.18)    AS price_with_gstFROM products;

ResultMySQL 8.4

product_name	price	gst	price_with_gst
Wireless Mouse	799	143.82	943
Mechanical Keyboard	3499	629.82	4129
USB-C Hub	1999	359.82	2359
Office Chair	8999	1619.82	10619
Standing Desk	18999	3419.82	22419
Notebook Pack	249	44.82	294
Gel Pens	199	35.82	235
Desk Lamp	1499	269.82	1769
Monitor Arm	2799	503.82	3303

9 rows in set

Quick quiz

1. What does ROUND(1234.567, -2) return?

2. What is CEIL(2.01)?

3. What does 7 / 2 return in MySQL?

MySQL’s / returns a decimal. Use DIV for integer division.

4. How do you get the remainder of 17 divided by 5?

MOD returns 2. 17 % 5 also works in MySQL.

Numeric functions FAQ

How do I round to 2 decimal places in SQL?

Use ROUND(value, 2). To cut without rounding, use TRUNCATE(value, 2) in MySQL.

What is the difference between CEIL and FLOOR?

CEIL (or CEILING) rounds up to the nearest whole number; FLOOR rounds down.

Why does integer division give a decimal in MySQL?

MySQL’s / operator always returns a decimal result. Use DIV for whole-number division, or FLOOR(a / b).

How do I calculate a percentage in SQL?

Multiply by 100 before dividing and round the result: ROUND(100 * part / total, 1).