SQL Transactions and ACID

SQL Tutorial › Modifying Data › Transactions & ACID

SQL Transactions and ACID

IntermediateLesson 22 of 6913 min read8 queries run on MySQL 8.4

A transaction groups several statements into one unit of work that either succeeds completely or leaves no trace. Moving money between two accounts is the textbook case: the debit and the credit must both happen, or neither. This lesson runs real transactions on MySQL 8.4 and explains the ACID guarantees behind them.

In this lesson

Start, commit and roll back a transaction
Undo part of a transaction with SAVEPOINT
What autocommit does
The four ACID properties and the isolation levels

The problem transactions solve

Transfer 2,000 from Asha’s account to Ravi’s account. That is two updates. If the server crashes, or the second update fails, after the first one, 2,000 disappears. A transaction makes the two updates atomic.

Every example in this lesson starts with this table:

MySQL

CREATE TABLE accounts (  account_id INT PRIMARY KEY,  holder     VARCHAR(30) NOT NULL,  balance    INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000);

START TRANSACTION and COMMIT

MySQL

CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = balance - 2000 WHERE account_id = 1;UPDATE accounts SET balance = balance + 2000 WHERE account_id = 2;COMMIT; SELECT * FROM accounts;

ResultMySQL 8.4

mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …

Query OK, 0 rows affected

mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)

Query OK, 2 rows affected

mysql> START TRANSACTION

Query OK, 0 rows affected

mysql> UPDATE accounts SET balance = balance – 2000 WHERE account_id = 1

Query OK, 1 row affected

mysql> UPDATE accounts SET balance = balance + 2000 WHERE account_id = 2

Query OK, 1 row affected

mysql> COMMIT

Query OK, 0 rows affected

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	8000
2	Ravi	5000

2 rows in set

How this query works, step by step

START TRANSACTION (or BEGIN) opens a transaction. From now on, changes are pending.
The two UPDATEs move the money. Other sessions still see the old balances.
COMMIT makes both changes permanent and visible to everyone at the same moment.
The total money in the bank is still 13,000.

ROLLBACK

ROLLBACK throws away every change since START TRANSACTION:

MySQL

CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = 0 WHERE account_id = 1;SELECT * FROM accounts;ROLLBACK;SELECT * FROM accounts;

ResultMySQL 8.4

mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …

Query OK, 0 rows affected

mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)

Query OK, 2 rows affected

mysql> START TRANSACTION

Query OK, 0 rows affected

mysql> UPDATE accounts SET balance = 0 WHERE account_id = 1

Query OK, 1 row affected

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	0
2	Ravi	3000

2 rows in set

mysql> ROLLBACK

Query OK, 0 rows affected

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	10000
2	Ravi	3000

2 rows in set

How this query works, step by step

Inside the transaction Asha’s balance is set to 0, and this session sees the change.
ROLLBACK undoes it.
The final SELECT shows the original 10,000.

When a statement fails mid-transaction

Ravi tries to send 5,000 but has only 3,000. The credit to Asha runs first, then the debit breaks the CHECK (balance >= 0) rule:

MySQL

CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = balance + 5000 WHERE account_id = 1;UPDATE accounts SET balance = balance - 5000 WHERE account_id = 2;

ResultMySQL 8.4

mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …

Query OK, 0 rows affected

mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)

Query OK, 2 rows affected

mysql> START TRANSACTION

Query OK, 0 rows affected

mysql> UPDATE accounts SET balance = balance + 5000 WHERE account_id = 1

Query OK, 1 row affected

mysql> UPDATE accounts SET balance = balance – 5000 WHERE account_id = 2

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

The failed statement changed nothing, but the transaction is still open, and the first update is still pending:

MySQL

SELECT * FROM accounts;ROLLBACK;SELECT * FROM accounts;

ResultMySQL 8.4

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	15000
2	Ravi	3000

2 rows in set

mysql> ROLLBACK

Query OK, 0 rows affected

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	10000
2	Ravi	3000

2 rows in set

How this query works, step by step

The first SELECT shows Asha at 15,000: MySQL does not undo the whole transaction automatically when one statement fails.
Your code must react to the error by calling ROLLBACK.
After the rollback both balances are back to 10,000 and 3,000.

Watch out

In application code, always wrap transactions in try/except (or try/catch): on any error, ROLLBACK; only when every statement succeeded, COMMIT.

SAVEPOINT: partial rollback

A savepoint marks a point inside a transaction that you can roll back to without losing the earlier work:

MySQL

CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;SAVEPOINT after_fee;UPDATE accounts SET balance = balance + 9999 WHERE account_id = 2;ROLLBACK TO SAVEPOINT after_fee;COMMIT; SELECT * FROM accounts;

ResultMySQL 8.4

mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …

Query OK, 0 rows affected

mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)

Query OK, 2 rows affected

mysql> START TRANSACTION

Query OK, 0 rows affected

mysql> UPDATE accounts SET balance = balance – 1000 WHERE account_id = 1

Query OK, 1 row affected

mysql> SAVEPOINT after_fee

Query OK, 0 rows affected

mysql> UPDATE accounts SET balance = balance + 9999 WHERE account_id = 2

Query OK, 1 row affected

mysql> ROLLBACK TO SAVEPOINT after_fee

Query OK, 0 rows affected

mysql> COMMIT

Query OK, 0 rows affected

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	9000
2	Ravi	3000

2 rows in set

How this query works, step by step

A fee of 1,000 is taken from Asha.
SAVEPOINT after_fee remembers this point.
A wrong credit to Ravi is made, then undone with ROLLBACK TO SAVEPOINT after_fee.
COMMIT saves the fee only: Asha 9,000, Ravi unchanged.

Autocommit

By default MySQL runs in autocommit mode: every statement outside an explicit transaction is committed immediately.

MySQL

SELECT @@autocommit;

ResultMySQL 8.4

@@autocommit
1

1 row in set

SET autocommit = 0; keeps a transaction open until you COMMIT or ROLLBACK. Most applications leave autocommit on and use START TRANSACTION where they need several statements to succeed together.

Note

DDL statements such as CREATE, ALTER, DROP and TRUNCATE cause an implicit commit in MySQL: they commit any open transaction and cannot be rolled back.

ACID

Property	Means	In the transfer example
Atomicity	all or nothing	debit and credit both happen, or neither
Consistency	rules always hold	`CHECK (balance >= 0)` is never broken
Isolation	concurrent transactions do not see each other’s half-done work	nobody sees the money “in flight”
Durability	committed data survives a crash	after `COMMIT`, a power cut cannot undo the transfer

MySQL’s default storage engine, InnoDB, provides all four.

Isolation levels

MySQL

SELECT @@transaction_isolation;

ResultMySQL 8.4

@@transaction_isolation
REPEATABLE-READ

1 row in set

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	no	possible	possible
REPEATABLE READ (MySQL default)	no	no	mostly prevented in InnoDB
SERIALIZABLE	no	no	no

Dirty read: seeing another transaction’s uncommitted change.
Non-repeatable read: reading the same row twice and getting different values.
Phantom read: running the same query twice and getting new rows.

Change it for the next transaction with SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.

Locking reads

When you read a row in order to update it, lock it so nobody changes it in between:

MySQL

CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) NOT NULL, balance INT NOT NULL CHECK (balance >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000); START TRANSACTION;SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;COMMIT;SELECT * FROM accounts;

ResultMySQL 8.4

mysql> CREATE TABLE accounts (account_id INT PRIMARY KEY, holder VARCHAR(30) …

Query OK, 0 rows affected

mysql> INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ravi', 3000)

Query OK, 2 rows affected

mysql> START TRANSACTION

Query OK, 0 rows affected

mysql> SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE

balance
10000

1 row in set

mysql> UPDATE accounts SET balance = balance – 500 WHERE account_id = 1

Query OK, 1 row affected

mysql> COMMIT

Query OK, 0 rows affected

mysql> SELECT * FROM accounts

account_id	holder	balance
1	Asha	9500
2	Ravi	3000

2 rows in set

How this query works, step by step

SELECT ... FOR UPDATE reads Asha’s balance and locks her row until the transaction ends.
Another session trying to update or lock the same row waits.
After COMMIT the lock is released.

Quick quiz

1. What does ROLLBACK do?

2. A statement fails inside a MySQL transaction. What happens to the earlier statements?

3. Which ACID property guarantees that committed data survives a crash?

4. What is MySQL InnoDB’s default isolation level?

@@transaction_isolation shows REPEATABLE-READ.

Transactions FAQ

What is a transaction in SQL?

A transaction is a group of SQL statements executed as one unit: either all of them are committed, or all are rolled back.

What is the difference between COMMIT and ROLLBACK?

COMMIT makes the changes of the current transaction permanent. ROLLBACK discards them.

What are ACID properties?

Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions do not interfere) and Durability (committed data survives failures).

Can I roll back a TRUNCATE or DROP in MySQL?

No. DDL statements commit implicitly in MySQL, so they cannot be undone with ROLLBACK.

SQL Aggregate Functions

SQL Tutorial › Functions & Grouping › Aggregate Functions

SQL Aggregate Functions

BeginnerLesson 23 of 6911 min read9 queries run on MySQL 8.4

Aggregate functions turn many rows into one value: how many orders, total revenue, average salary, the cheapest product. They are the building blocks of every report and dashboard, and the start of GROUP BY, which applies them per group.

In this lesson

Count rows with COUNT(*), COUNT(column) and COUNT(DISTINCT ...)
Total and average with SUM and AVG
Find extremes with MIN and MAX
How aggregates treat NULL, and joining values with GROUP_CONCAT

The five core aggregates

Function	Returns	Skips NULL?
`COUNT(*)`	number of rows	counts every row
`COUNT(col)`	number of non-NULL values	yes
`SUM(col)`	total	yes
`AVG(col)`	average (sum ÷ count of non-NULL)	yes
`MIN(col)` / `MAX(col)`	smallest / largest value	yes

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id	salary	commission	hire_date
100	Meera	NULL	250000	NULL	2015-01-05
101	Asha	1	125000	NULL	2018-04-12
102	Rahul	1	98000	NULL	2019-07-01
103	Priya	1	72000	NULL	2021-01-18
104	Karan	1	72000	NULL	2022-08-29
105	Vikram	2	105000	12000	2017-11-20
106	Neha	2	54000	6000	2022-03-14
107	Arjun	2	48000	4500	2023-06-05
108	Kavya	3	88000	NULL	2020-09-09
109	Rohan	3	45000	NULL	2024-01-22
110	Sneha	4	78000	NULL	2019-02-25
111	Amit	1	25000	NULL	2025-06-02

COUNT

SQL

SELECT COUNT(*)                AS employees,       COUNT(dept_id)          AS with_department,       COUNT(commission)       AS with_commission,       COUNT(DISTINCT dept_id) AS departmentsFROM employees;

ResultMySQL 8.4

employees	with_department	with_commission	departments
12	11	3	4

1 row in set

How this query works, step by step

COUNT(*) counts all 12 rows, NULLs or not.
COUNT(dept_id) skips the CEO, whose dept_id is NULL: 11.
COUNT(commission) counts the 3 sales staff.
COUNT(DISTINCT dept_id) counts different departments: 4.

SUM and AVG

MySQL

SELECT SUM(salary)            AS payroll,       AVG(salary)            AS avg_salary,       ROUND(AVG(salary), 0)  AS avg_roundedFROM employees;

ResultMySQL 8.4

payroll	avg_salary	avg_rounded
1060000	88333.3333	88333

1 row in set

How this query works, step by step

SUM(salary) adds the 12 salaries: 1,060,000.
AVG(salary) divides that by 12. MySQL returns an exact decimal with four places: 88333.3333.
ROUND(..., 0) rounds to a whole number for display.

MIN and MAX

MIN and MAX work on numbers, text (alphabetical) and dates:

SQL

SELECT MIN(salary)     AS lowest_salary,       MAX(salary)     AS highest_salary,       MIN(hire_date)  AS first_hire,       MAX(hire_date)  AS latest_hire,       MIN(first_name) AS first_alphabeticallyFROM employees;

ResultMySQL 8.4

lowest_salary	highest_salary	first_hire	latest_hire	first_alphabetically
25000	250000	2015-01-05	2025-06-02	Amit

1 row in set

Note

MAX(salary) tells you the highest salary, not who earns it. To get the whole row, use ORDER BY salary DESC LIMIT 1, a subquery, or a window function.

Aggregates with WHERE

WHERE filters rows before they are aggregated:

SQL

SELECT COUNT(*) AS engineers, SUM(salary) AS engineering_payroll, MAX(salary) AS top_salaryFROM employeesWHERE dept_id = 1;

ResultMySQL 8.4

engineers	engineering_payroll	top_salary
5	392000	125000

1 row in set

How this query works, step by step

WHERE dept_id = 1 keeps the 5 Engineering employees.
The aggregates run on those 5 rows only.

Aggregates on expressions

Total revenue from all order lines, quantity × price:

SQL

SELECT SUM(oi.quantity * p.price) AS revenue,       SUM(oi.quantity)           AS units_sold,       COUNT(DISTINCT oi.order_id) AS ordersFROM order_items oiJOIN products p ON p.product_id = oi.product_id;

ResultMySQL 8.4

revenue	units_sold	orders
72262	38	10

1 row in set

How this query works, step by step

The join adds each product’s price to its order lines.
For every line MySQL calculates quantity * price, then SUM adds them all.
COUNT(DISTINCT oi.order_id) counts orders, not lines: 16 lines belong to 10 orders.

Aggregates and NULL

MySQL

SELECT AVG(commission)              AS avg_of_sellers,       AVG(COALESCE(commission, 0)) AS avg_of_everyone,       SUM(commission)              AS totalFROM employees;

ResultMySQL 8.4

avg_of_sellers	avg_of_everyone	total
7500.0000	1875.0000	22500

1 row in set

How this query works, step by step

AVG(commission) ignores the 9 NULLs: 22,500 ÷ 3 = 7,500.
COALESCE(commission, 0) turns NULL into 0, so the average is over all 12 rows: 22,500 ÷ 12 = 1,875.
Which answer is right depends on what NULL means in your data.

An aggregate over zero rows returns NULL (except COUNT, which returns 0):

SQL

SELECT COUNT(*) AS n, SUM(salary) AS total, MAX(salary) AS topFROM employeesWHERE dept_id = 5;

ResultMySQL 8.4

n	total	top
0	NULL	NULL

1 row in set

GROUP_CONCAT: join values into one string

MySQL’s GROUP_CONCAT collects values into a comma-separated list:

MySQL

SELECT GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS engineering_teamFROM employeesWHERE dept_id = 1;

ResultMySQL 8.4

engineering_team
Amit, Asha, Karan, Priya, Rahul

1 row in set

The standard SQL equivalent is STRING_AGG (PostgreSQL, SQL Server).

Mixing aggregates and plain columns

This query fails in MySQL 8.4, because first_name is not aggregated and there is no GROUP BY. Which of the 12 names should go next to the single maximum?

MySQL

SELECT first_name, MAX(salary)FROM employees;

ResultMySQL 8.4

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'company.employees.first_name'; this is incompatible with sql_mode=only_full_group_by

Tip

Either aggregate every selected column, or add the plain columns to a GROUP BY. That rule comes from the default ONLY_FULL_GROUP_BY mode.

Exercise: Product statistics

In one row show the number of products, the cheapest price, the most expensive price and the total stock.

Expected resultMySQL 8.4

products	cheapest	dearest	units_in_stock
9	199	18999	780

1 row in set

Hint 1

COUNT(*), MIN(price), MAX(price), SUM(stock).

SQL

SELECT COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest, SUM(stock) AS units_in_stockFROM products;

ResultMySQL 8.4

products	cheapest	dearest	units_in_stock
9	199	18999	780

1 row in set

Exercise: Delivered orders

How many orders have the status ‘Delivered’, and what was the first and last delivered order date?

Expected resultMySQL 8.4

delivered	first_date	last_date
7	2025-01-05	2025-06-01

1 row in set

Hint 1

Filter with WHERE, then use COUNT, MIN and MAX.

SQL

SELECT COUNT(*) AS delivered, MIN(order_date) AS first_date, MAX(order_date) AS last_dateFROM ordersWHERE status = 'Delivered';

ResultMySQL 8.4

delivered	first_date	last_date
7	2025-01-05	2025-06-01

1 row in set

Quick quiz

1. What is the difference between COUNT(*) and COUNT(email)?

Only COUNT(*) counts every row.

2. Values are 10, 20, NULL. What is AVG?

3. What does SUM(salary) return when no rows match?

4. Which function returns the latest date?

MAX works on dates too.

Aggregate functions FAQ

What are aggregate functions in SQL?

Functions that compute one value from many rows: COUNT, SUM, AVG, MIN and MAX, plus others such as GROUP_CONCAT in MySQL.

Does COUNT count NULL values?

COUNT(*) counts every row. COUNT(column) counts only non-NULL values of that column.

Can I use aggregate functions in a WHERE clause?

No. WHERE filters individual rows before aggregation. Use HAVING to filter on aggregates, or a subquery.

Why does AVG return so many decimal places in MySQL?

AVG of integers returns an exact DECIMAL with four extra digits. Wrap it in ROUND(AVG(col), 2) to format it.

SQL SELF JOIN

SQL Tutorial › Joins & Set Operators › SELF JOIN

SQL SELF JOIN

IntermediateLesson 36 of 699 min read6 queries run on MySQL 8.4

A self join joins a table to itself. It is how you answer questions about rows that point to other rows of the same table: an employee and their manager, two customers in the same city, a product and a cheaper one in its category. The only new idea is that both copies of the table need different aliases.

In this lesson

Join a table to itself with two aliases
List employees with their manager
Keep the top of the hierarchy with LEFT JOIN
Compare and pair rows of the same table

Sample data

manager_id stores the emp_id of each employee’s manager, another row in the same table:

employees · 12 rows (some columns)

emp_id	first_name	job_title	manager_id
100	Meera	CEO	NULL
101	Asha	Engineering Manager	100
102	Rahul	Senior Developer	101
103	Priya	QA Engineer	101
104	Karan	Developer	102
105	Vikram	Sales Manager	100
106	Neha	Sales Executive	105
107	Arjun	Sales Executive	105
108	Kavya	Marketing Lead	100
109	Rohan	Content Writer	108
110	Sneha	HR Manager	100
111	Amit	Intern	101

Employees and their managers

SQL

SELECT e.emp_id, e.first_name AS employee, m.first_name AS managerFROM employees eJOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;

ResultMySQL 8.4

emp_id	employee	manager
101	Asha	Meera
102	Rahul	Asha
103	Priya	Asha
104	Karan	Rahul
105	Vikram	Meera
106	Neha	Vikram
107	Arjun	Vikram
108	Kavya	Meera
109	Rohan	Kavya
110	Sneha	Meera
111	Amit	Asha

11 rows in set

How this query works, step by step

employees e is the copy that plays the employee; employees m is the copy that plays the manager.
ON m.emp_id = e.manager_id finds, for each employee, the row whose id equals their manager_id.
The CEO’s manager_id is NULL, so she has no match and the inner join drops her: 11 rows.

Keep the CEO with LEFT JOIN

SQL

SELECT e.first_name AS employee,       COALESCE(m.first_name, '(no manager)') AS managerFROM employees eLEFT JOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;

ResultMySQL 8.4

employee	manager
Meera	(no manager)
Asha	Meera
Rahul	Asha
Priya	Asha
Karan	Rahul
Vikram	Meera
Neha	Vikram
Arjun	Vikram
Kavya	Meera
Rohan	Kavya
Sneha	Meera
Amit	Asha

12 rows in set

Count direct reports

Group by the manager copy to see how many people report to each manager:

SQL

SELECT m.emp_id, m.first_name AS manager, COUNT(*) AS direct_reportsFROM employees eJOIN employees m ON m.emp_id = e.manager_idGROUP BY m.emp_id, m.first_nameORDER BY direct_reports DESC, m.emp_id;

ResultMySQL 8.4

emp_id	manager	direct_reports
100	Meera	4
101	Asha	3
105	Vikram	2
102	Rahul	1
108	Kavya	1

5 rows in set

How this query works, step by step

Each row of the join is one employee-manager pair.
Grouping by the manager counts their pairs.
Meera has 4 direct reports and Asha 3. People who manage nobody do not appear.

Employees who earn more than their manager

A classic interview question:

SQL

SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS manager_salaryFROM employees eJOIN employees m ON m.emp_id = e.manager_idWHERE e.salary > m.salary;

ResultMySQL 8.4

Empty set

In the sample data nobody does, so the result is an Empty set. Change the condition to e.salary > m.salary * 0.75 to find people paid close to their manager.

Pairs of rows

Employees who work in the same city, each pair listed once:

SQL

SELECT a.first_name AS person_1, b.first_name AS person_2, a.cityFROM employees aJOIN employees b ON b.city = a.city AND a.emp_id < b.emp_idWHERE a.city = 'Pune'ORDER BY a.emp_id, b.emp_id;

ResultMySQL 8.4

person_1	person_2	city
Priya	Kavya	Pune
Priya	Rohan	Pune
Kavya	Rohan	Pune

3 rows in set

How this query works, step by step

b.city = a.city pairs people from the same city.
a.emp_id < b.emp_id removes pairs of a person with themselves and keeps only one order of each pair (Priya-Kavya, not also Kavya-Priya).
Pune has three employees, so there are 3 pairs.

Two levels up

Chain the self join to reach the manager’s manager:

SQL

SELECT e.first_name AS employee, m.first_name AS manager, g.first_name AS managers_managerFROM employees eJOIN employees m ON m.emp_id = e.manager_idJOIN employees g ON g.emp_id = m.manager_idORDER BY e.emp_id;

ResultMySQL 8.4

employee	manager	managers_manager
Rahul	Asha	Meera
Priya	Asha	Meera
Karan	Rahul	Asha
Neha	Vikram	Meera
Arjun	Vikram	Meera
Rohan	Kavya	Meera
Amit	Asha	Meera

7 rows in set

For any depth, use a recursive CTE.

Exercise: Same-price products

List pairs of different products in the same category where the first is cheaper than the second, for the Stationery and Electronics categories.

Expected resultMySQL 8.4

category	cheaper	dearer
Electronics	Wireless Mouse	USB-C Hub
Electronics	Wireless Mouse	Mechanical Keyboard
Electronics	USB-C Hub	Mechanical Keyboard
Stationery	Gel Pens	Notebook Pack

4 rows in set

Hint 1

Join products a to products b on category, with a.price < b.price.

SQL

SELECT a.category, a.product_name AS cheaper, b.product_name AS dearerFROM products aJOIN products b ON b.category = a.category AND a.price < b.priceWHERE a.category IN ('Stationery', 'Electronics')ORDER BY a.category, a.price, b.price;

ResultMySQL 8.4

category	cheaper	dearer
Electronics	Wireless Mouse	USB-C Hub
Electronics	Wireless Mouse	Mechanical Keyboard
Electronics	USB-C Hub	Mechanical Keyboard
Stationery	Gel Pens	Notebook Pack

4 rows in set

Quick quiz

1. Why does a self join need two aliases?

Without aliases, employees.emp_id would be ambiguous.

2. Why is the CEO missing from employees e JOIN employees m ON m.emp_id = e.manager_id?

3. What does a.emp_id < b.emp_id do in a pairs query?

SELF JOIN FAQ

What is a self join in SQL?

A self join is a normal join (inner or outer) in which a table is joined to itself, using two different aliases.

When do you use a self join?

For hierarchies such as employees and managers, for comparing rows within the same table, and for finding pairs or duplicates.

Is SELF JOIN a keyword?

No. You write JOIN or LEFT JOIN with the same table twice.

SQL FULL OUTER JOIN

SQL Tutorial › Joins & Set Operators › FULL OUTER JOIN

SQL FULL OUTER JOIN

IntermediateLesson 35 of 699 min read5 queries run on MySQL 8.4

A FULL OUTER JOIN keeps every row from both tables: matched rows are combined, and rows without a partner appear with NULLs on the other side. PostgreSQL, SQL Server and Oracle have the keyword; MySQL does not, but you can build exactly the same result from a left join, a right join and UNION.

In this lesson

What a full outer join returns
Emulate it in MySQL with UNION
Return only the unmatched rows of both sides
Compare two tables to find differences

The tables

employees · 12 rows (some columns)

emp_id	first_name	dept_id
100	Meera	NULL
101	Asha	1
102	Rahul	1
103	Priya	1
104	Karan	1
105	Vikram	2
106	Neha	2
107	Arjun	2
108	Kavya	3
109	Rohan	3
110	Sneha	4
111	Amit	1

departments · 5 rows

dept_id	dept_name	location
1	Engineering	Bengaluru
2	Sales	Mumbai
3	Marketing	Pune
4	HR	Delhi
5	Finance	Mumbai

What a FULL OUTER JOIN should return

every employee with a department, combined with it (11 rows);
Meera, who has no department, with NULL department columns;
Finance, which has no employees, with NULL employee columns.

In PostgreSQL you would write FROM employees e FULL OUTER JOIN departments d ON d.dept_id = e.dept_id. In MySQL that is a syntax error:

MySQL

SELECT e.first_name, d.dept_nameFROM employees eFULL OUTER JOIN departments d ON d.dept_id = e.dept_id;

ResultMySQL 8.4

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 'FULL OUTER JOIN departments d ON d.dept_id = e.dept_id' at line 3

Emulate it with UNION

MySQL

SELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idUNIONSELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY dept_id, emp_id;

ResultMySQL 8.4

emp_id	first_name	dept_id	dept_name
100	Meera	NULL	NULL
101	Asha	1	Engineering
102	Rahul	1	Engineering
103	Priya	1	Engineering
104	Karan	1	Engineering
111	Amit	1	Engineering
105	Vikram	2	Sales
106	Neha	2	Sales
107	Arjun	2	Sales
108	Kavya	3	Marketing
109	Rohan	3	Marketing
110	Sneha	4	HR
NULL	NULL	5	Finance

13 rows in set

How this query works, step by step

The LEFT JOIN returns all 12 employees, Meera with NULL department columns.
The RIGHT JOIN returns all departments: the 11 matched employee rows plus Finance with NULL employee columns.
UNION stacks both results and removes exact duplicates, so each matched row appears once: 11 + 1 + 1 = 13 rows.
ORDER BY at the end sorts the combined result; NULL dept_id (Meera) comes first.

Watch out

Use UNION, not UNION ALL, here; otherwise every matched row would appear twice. If the tables themselves contain duplicate rows you need to keep, use the UNION ALL + anti-join version below instead.

A version that keeps genuine duplicates

MySQL

SELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idUNION ALLSELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idWHERE e.emp_id IS NULLORDER BY dept_id, emp_id;

ResultMySQL 8.4

emp_id	first_name	dept_id	dept_name
100	Meera	NULL	NULL
101	Asha	1	Engineering
102	Rahul	1	Engineering
103	Priya	1	Engineering
104	Karan	1	Engineering
111	Amit	1	Engineering
105	Vikram	2	Sales
106	Neha	2	Sales
107	Arjun	2	Sales
108	Kavya	3	Marketing
109	Rohan	3	Marketing
110	Sneha	4	HR
NULL	NULL	5	Finance

13 rows in set

How this query works, step by step

The first part returns every employee, matched or not.
The second part returns only departments without employees (WHERE e.emp_id IS NULL).
UNION ALL just appends them, so no duplicate check is needed: faster on large tables.

Only the unmatched rows

Rows that exist on one side only, from either table:

SQL

SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idWHERE d.dept_id IS NULLUNION ALLSELECT e.emp_id, e.first_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idWHERE e.emp_id IS NULL;

ResultMySQL 8.4

emp_id	first_name	dept_name
100	Meera	NULL
NULL	NULL	Finance

2 rows in set

Practical use: compare two tables

A full outer join is the classic way to reconcile two lists, for example the stock in the system against a warehouse count:

MySQL

CREATE TABLE warehouse_count (product_id INT PRIMARY KEY, counted INT);INSERT INTO warehouse_count VALUES (1, 120), (2, 44), (4, 12), (10, 7); SELECT p.product_id, p.stock, w.counted, 'only in system' AS issueFROM products p LEFT JOIN warehouse_count w ON w.product_id = p.product_idWHERE w.product_id IS NULL AND p.stock > 0UNION ALLSELECT w.product_id, p.stock, w.counted, 'only in warehouse'FROM products p RIGHT JOIN warehouse_count w ON w.product_id = p.product_idWHERE p.product_id IS NULLUNION ALLSELECT p.product_id, p.stock, w.counted, 'count differs'FROM products p JOIN warehouse_count w ON w.product_id = p.product_idWHERE p.stock <> w.countedORDER BY product_id;

ResultMySQL 8.4

mysql> CREATE TABLE warehouse_count (product_id INT PRIMARY KEY, counted INT)

Query OK, 0 rows affected

mysql> INSERT INTO warehouse_count VALUES (1, 120), (2, 44), (4, 12), (10, 7)

Query OK, 4 rows affected

mysql> SELECT p.product_id, p.stock, w.counted, 'only in system' AS issue

product_id	stock	counted	issue
2	45	44	count differs
5	5	NULL	only in system
6	300	NULL	only in system
7	250	NULL	only in system
8	30	NULL	only in system
9	18	NULL	only in system
10	NULL	7	only in warehouse

7 rows in set

How this query works, step by step

The first part lists in-stock products the warehouse did not count.
The second part lists counted items unknown to the system (product 10).
The third part lists products where both exist but the numbers differ (product 2: 45 vs 44).
Products 1 and 4 match exactly and are not reported.

Quick quiz

1. Does MySQL 8.4 support the FULL OUTER JOIN keyword?

2. Why use UNION rather than UNION ALL in the simple emulation?

FULL OUTER JOIN FAQ

How do I do a FULL OUTER JOIN in MySQL?

Write a LEFT JOIN and a RIGHT JOIN with the same columns and combine them with UNION. For large tables, use UNION ALL with WHERE left.id IS NULL on the right-join half.

What is the difference between FULL OUTER JOIN and UNION?

A full outer join combines columns from two tables side by side, matching rows by a key. UNION stacks the rows of two queries with the same columns on top of each other.

When do I need a full outer join?

When you must see all rows from both sides, for example to reconcile two lists and find what is missing or different on each side.

SQL RIGHT JOIN

SQL Tutorial › Joins & Set Operators › RIGHT JOIN

SQL RIGHT JOIN

IntermediateLesson 34 of 698 min read4 queries run on MySQL 8.4

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row of the right table and fills the left table’s columns with NULL where nothing matches. Anything a right join does, a left join with the tables swapped does too, which is why you will see it less often, but you must be able to read it.

In this lesson

Keep every row of the right table with RIGHT JOIN
Find right-side rows with no match
Rewrite a RIGHT JOIN as a LEFT JOIN
Know when a RIGHT JOIN reads naturally

RIGHT JOIN syntax

MySQL

SELECT columnsFROM left_table lRIGHT JOIN right_table r ON r.key = l.key;

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id
100	Meera	NULL
101	Asha	1
102	Rahul	1
103	Priya	1
104	Karan	1
105	Vikram	2
106	Neha	2
107	Arjun	2
108	Kavya	3
109	Rohan	3
110	Sneha	4
111	Amit	1

departments · 5 rows

dept_id	dept_name	location
1	Engineering	Bengaluru
2	Sales	Mumbai
3	Marketing	Pune
4	HR	Delhi
5	Finance	Mumbai

Every department, with its employees

SQL

SELECT d.dept_name, e.first_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY d.dept_id, e.first_name;

ResultMySQL 8.4

dept_name	first_name
Engineering	Amit
Engineering	Asha
Engineering	Karan
Engineering	Priya
Engineering	Rahul
Sales	Arjun
Sales	Neha
Sales	Vikram
Marketing	Kavya
Marketing	Rohan
HR	Sneha
Finance	NULL

12 rows in set

How this query works, step by step

departments is on the right, so all 5 departments are kept.
Departments with employees get one row per employee.
Finance has no employees: it appears once, with NULL in first_name.
Meera, who has no department, is not in the result: she is in the left table and has no match.

Departments with no employees

SQL

SELECT d.dept_id, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idWHERE e.emp_id IS NULL;

ResultMySQL 8.4

dept_id	dept_name
5	Finance

1 row in set

Rewrite as a LEFT JOIN

Swap the tables and change RIGHT to LEFT. The result is identical:

SQL

SELECT d.dept_name, e.first_nameFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idORDER BY d.dept_id, e.first_name;

ResultMySQL 8.4

dept_name	first_name
Engineering	Amit
Engineering	Asha
Engineering	Karan
Engineering	Priya
Engineering	Rahul
Sales	Arjun
Sales	Neha
Sales	Vikram
Marketing	Kavya
Marketing	Rohan
HR	Sneha
Finance	NULL

12 rows in set

How this query works, step by step

departments is now first (the left table), so all departments are kept.
The ON condition is the same equality, just written the other way round.
Many style guides use only LEFT JOIN, because “start from the table you want all rows of” is easier to follow.

When RIGHT JOIN reads naturally

In a long chain of inner joins, you may want to keep all rows of a table you join last. A right join at the end does that without restructuring the query:

SQL

SELECT p.product_name, o.order_id, o.statusFROM orders oJOIN order_items oi ON oi.order_id = o.order_idRIGHT JOIN products p ON p.product_id = oi.product_idWHERE p.category = 'Furniture'ORDER BY p.product_name, o.order_id;

ResultMySQL 8.4

product_name	order_id	status
Desk Lamp	1006	Shipped
Desk Lamp	1008	Pending
Monitor Arm	NULL	NULL
Office Chair	1002	Delivered
Office Chair	1008	Pending
Standing Desk	1004	Cancelled

6 rows in set

How this query works, step by step

orders JOIN order_items builds the order lines.
RIGHT JOIN products keeps every product, even those with no order line.
The Monitor Arm has never been ordered, so it appears with NULL order columns.

Exercise: Every product and its order lines

Using a RIGHT JOIN, list every product name with the order_ids it appears in, including products never ordered.

Expected resultMySQL 8.4

product_name	order_id
Wireless Mouse	1003
Wireless Mouse	1007
Mechanical Keyboard	1001
Mechanical Keyboard	1003
Mechanical Keyboard	1009
USB-C Hub	1006
Office Chair	1002
Office Chair	1008
Standing Desk	1004
Notebook Pack	1001
Notebook Pack	1005
Notebook Pack	1010
Gel Pens	1005
Gel Pens	1010
Desk Lamp	1006
Desk Lamp	1008
Monitor Arm	NULL

17 rows in set

Hint 1

Put order_items first and products on the right.

SQL

SELECT p.product_name, oi.order_idFROM order_items oiRIGHT JOIN products p ON p.product_id = oi.product_idORDER BY p.product_id, oi.order_id;

ResultMySQL 8.4

product_name	order_id
Wireless Mouse	1003
Wireless Mouse	1007
Mechanical Keyboard	1001
Mechanical Keyboard	1003
Mechanical Keyboard	1009
USB-C Hub	1006
Office Chair	1002
Office Chair	1008
Standing Desk	1004
Notebook Pack	1001
Notebook Pack	1005
Notebook Pack	1010
Gel Pens	1005
Gel Pens	1010
Desk Lamp	1006
Desk Lamp	1008
Monitor Arm	NULL

17 rows in set

Quick quiz

1. A RIGHT JOIN B keeps all rows of…

2. Which query is equivalent to A RIGHT JOIN B ON ...?

RIGHT JOIN FAQ

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN keeps every row of the first (left) table; RIGHT JOIN keeps every row of the second (right) table. Swapping the table order converts one into the other.

Is RIGHT JOIN used in practice?

Less often than LEFT JOIN, because queries are usually easier to read when the table whose rows you keep comes first. It is fully supported in MySQL.

Is RIGHT OUTER JOIN the same as RIGHT JOIN?

Yes. The keyword OUTER is optional.

SQL LEFT JOIN

SQL Tutorial › Joins & Set Operators › LEFT JOIN

SQL LEFT JOIN

IntermediateLesson 33 of 6912 min read8 queries run on MySQL 8.4

LEFT JOIN (also LEFT OUTER JOIN) keeps every row of the left table, whether or not it has a match on the right. Where there is no match, the right table’s columns are NULL. That makes it the join for questions like “all customers, with their orders if they have any” and “which products have never been sold”.

In this lesson

Keep unmatched rows with LEFT JOIN
Find rows with no match using IS NULL
Count with zeros instead of losing rows
Avoid the WHERE clause that turns a LEFT JOIN into an INNER JOIN

LEFT JOIN syntax

MySQL

SELECT columnsFROM left_table lLEFT JOIN right_table r ON r.key = l.key;

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

All customers, with their orders

SQL

SELECT c.customer_id, c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idORDER BY c.customer_id, o.order_id;

ResultMySQL 8.4

customer_id	customer_name	order_id	status
1	Ananya Sharma	1001	Delivered
1	Ananya Sharma	1003	Delivered
1	Ananya Sharma	1008	Pending
2	Rohit Kumar	1002	Delivered
2	Rohit Kumar	1007	Delivered
3	Fatima Khan	1004	Cancelled
3	Fatima Khan	1009	Delivered
4	David Fernandes	1005	Delivered
5	Ishita Bose	1006	Shipped
5	Ishita Bose	1010	Delivered
6	Manoj Pillai	NULL	NULL
7	Sara Thomas	NULL	NULL

12 rows in set

How this query works, step by step

customers is the left table, so all 7 customers will appear.
Customers with orders get one row per order, as with an inner join.
Manoj (6) and Sara (7) have no orders: they still appear once, with NULL in order_id and status.
Result: 10 matched rows + 2 unmatched = 12 rows.

Find rows with no match (anti-join)

Keep only the rows where the right side is NULL. Customers who never ordered:

SQL

SELECT c.customer_id, c.customer_nameFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.order_id IS NULL;

ResultMySQL 8.4

customer_id	customer_name
6	Manoj Pillai
7	Sara Thomas

2 rows in set

How this query works, step by step

The left join adds NULL order columns to customers without orders.
WHERE o.order_id IS NULL keeps exactly those rows.
Test a column that can never be NULL in a real match, such as the primary key order_id.

Products that have never been ordered:

SQL

SELECT p.product_id, p.product_nameFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idWHERE oi.product_id IS NULL;

ResultMySQL 8.4

product_id	product_name
9	Monitor Arm

1 row in set

Departments with no employees:

SQL

SELECT d.dept_id, d.dept_nameFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idWHERE e.emp_id IS NULL;

ResultMySQL 8.4

dept_id	dept_name
5	Finance

1 row in set

Counts that include zero

Orders per customer, including customers with none:

SQL

SELECT c.customer_name, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.customer_nameORDER 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

The left join keeps all 7 customers.
COUNT(o.order_id) counts non-NULL order ids, so Manoj and Sara get 0.
COUNT(*) would be wrong here: it counts their single NULL row as 1.

The WHERE trap

Goal: all customers, plus their delivered orders. Putting the status filter in WHERE silently removes the customers without orders:

SQL

SELECT c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.status = 'Delivered'ORDER BY c.customer_name;

ResultMySQL 8.4

customer_name	order_id	status
Ananya Sharma	1001	Delivered
Ananya Sharma	1003	Delivered
David Fernandes	1005	Delivered
Fatima Khan	1009	Delivered
Ishita Bose	1010	Delivered
Rohit Kumar	1002	Delivered
Rohit Kumar	1007	Delivered

7 rows in set

How this query works, step by step

The left join produces NULL status for Manoj and Sara.
WHERE o.status = 'Delivered' is UNKNOWN for NULL, so those rows are thrown away.
The query now behaves like an inner join: the customers without delivered orders are gone.

Move the condition into ON, so it only decides which orders match:

MySQL

SELECT c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'Delivered'ORDER BY c.customer_name;

ResultMySQL 8.4

customer_name	order_id	status
Ananya Sharma	1003	Delivered
Ananya Sharma	1001	Delivered
David Fernandes	1005	Delivered
Fatima Khan	1009	Delivered
Ishita Bose	1010	Delivered
Manoj Pillai	NULL	NULL
Rohit Kumar	1007	Delivered
Rohit Kumar	1002	Delivered
Sara Thomas	NULL	NULL

9 rows in set

How this query works, step by step

Only delivered orders can match now.
Manoj and Sara have no delivered order, so they still appear once, with NULLs.
Every customer is in the result.

Tip

Rule of thumb for outer joins: conditions on the right table go in ON; conditions on the left table go in WHERE.

LEFT JOIN with several tables

Every product with its total units sold, including unsold products:

SQL

SELECT p.product_name, COALESCE(SUM(oi.quantity), 0) AS units_soldFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idGROUP BY p.product_id, p.product_nameORDER BY units_sold DESC, p.product_name;

ResultMySQL 8.4

product_name	units_sold
Notebook Pack	18
Gel Pens	6
Desk Lamp	3
Mechanical Keyboard	3
Office Chair	3
USB-C Hub	2
Wireless Mouse	2
Standing Desk	1
Monitor Arm	0

9 rows in set

COALESCE turns the NULL sum of the Monitor Arm into 0.

Exercise: Employees and departments

List all employees with their department name, including the CEO who has no department.

Expected resultMySQL 8.4

first_name	dept_name
Meera	NULL
Asha	Engineering
Rahul	Engineering
Priya	Engineering
Karan	Engineering
Vikram	Sales
Neha	Sales
Arjun	Sales
Kavya	Marketing
Rohan	Marketing
Sneha	HR
Amit	Engineering

12 rows in set

Hint 1

employees must be the left table.

SQL

SELECT e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idORDER BY e.emp_id;

ResultMySQL 8.4

first_name	dept_name
Meera	NULL
Asha	Engineering
Rahul	Engineering
Priya	Engineering
Karan	Engineering
Vikram	Sales
Neha	Sales
Arjun	Sales
Kavya	Marketing
Rohan	Marketing
Sneha	HR
Amit	Engineering

12 rows in set

Exercise: Headcount including empty departments

For each department, show its name and the number of employees, including departments with no employees.

Expected resultMySQL 8.4

dept_name	staff
Engineering	5
Sales	3
Marketing	2
HR	1
Finance	0

5 rows in set

Hint 1

Departments on the left, COUNT(e.emp_id).

SQL

SELECT d.dept_name, COUNT(e.emp_id) AS staffFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_id, d.dept_nameORDER BY d.dept_id;

ResultMySQL 8.4

dept_name	staff
Engineering	5
Sales	3
Marketing	2
HR	1
Finance	0

5 rows in set

Quick quiz

1. In A LEFT JOIN B, which rows are always kept?

2. How do you find rows in A with no match in B?

3. Why use COUNT(o.order_id) instead of COUNT(*) after a LEFT JOIN?

4. A WHERE condition on the right table of a LEFT JOIN…

LEFT JOIN FAQ

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

None. OUTER is optional; both keep every row of the left table.

How do I find records that do not exist in another table?

Use LEFT JOIN other ON ... WHERE other.id IS NULL, or WHERE NOT EXISTS (SELECT 1 FROM other WHERE ...).

Why does my LEFT JOIN return the same result as an INNER JOIN?

Usually a WHERE condition on the right table removes the NULL rows. Move that condition into the ON clause.

Can a LEFT JOIN return more rows than the left table has?

Yes. If a left row matches several right rows, it appears once per match.

SQL INNER JOIN

SQL Tutorial › Joins & Set Operators › INNER JOIN

SQL INNER JOIN

IntermediateLesson 32 of 6912 min read6 queries run on MySQL 8.4

INNER JOIN returns the rows that have a match in both tables. It is by far the most used join: orders with their customers, order lines with their products, employees with their departments. This lesson builds inner joins step by step on the sample shop data, from two tables to four.

In this lesson

Write an INNER JOIN with ON
Use table aliases and qualified column names
Join three and four tables
Combine joins with WHERE, GROUP BY and USING

INNER JOIN syntax

MySQL

SELECT columnsFROM table1 t1INNER JOIN table2 t2 ON t2.key = t1.key;

INNER is optional: JOIN alone means INNER JOIN.

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

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

Join two tables

Each order with its customer’s name:

SQL

SELECT o.order_id, o.order_date, c.customer_name, c.cityFROM orders oINNER JOIN customers c ON c.customer_id = o.customer_idORDER BY o.order_id;

ResultMySQL 8.4

order_id	order_date	customer_name	city
1001	2025-01-05	Ananya Sharma	Mumbai
1002	2025-01-17	Rohit Kumar	Delhi
1003	2025-02-02	Ananya Sharma	Mumbai
1004	2025-02-14	Fatima Khan	Hyderabad
1005	2025-03-08	David Fernandes	Goa
1006	2025-03-22	Ishita Bose	Kolkata
1007	2025-04-10	Rohit Kumar	Delhi
1008	2025-04-28	Ananya Sharma	Mumbai
1009	2025-05-15	Fatima Khan	Hyderabad
1010	2025-06-01	Ishita Bose	Kolkata

10 rows in set

How this query works, step by step

FROM orders o starts with the 10 orders; o is the table alias.
For each order, INNER JOIN customers c ON c.customer_id = o.customer_id looks up the customer with the same id.
Every order has a valid customer (the foreign key guarantees it), so all 10 orders come back.
Customers 6 and 7 have no orders, so they never appear: an inner join keeps only matched rows.

How the matching works

Think of it as a nested loop: for every row on the left, find every row on the right where the ON condition is true, and output one combined row per match.

orders row	ON c.customer_id = o.customer_id	output
1001, customer 1	matches Ananya Sharma	1 row
1004, customer 3	matches Fatima Khan	1 row
(no order)	Manoj Pillai, Sara Thomas never match	0 rows

The database actually uses indexes and smarter algorithms, but the result is the same.

One-to-many joins multiply rows

An order can have several lines. Joining orders to order_items gives one row per line, not per order:

SQL

SELECT o.order_id, o.status, oi.product_id, oi.quantityFROM orders oJOIN order_items oi ON oi.order_id = o.order_idWHERE o.order_id IN (1001, 1002, 1005)ORDER BY o.order_id, oi.product_id;

ResultMySQL 8.4

order_id	status	product_id	quantity
1001	Delivered	2	1
1001	Delivered	6	5
1002	Delivered	4	1
1005	Delivered	6	10
1005	Delivered	7	4

5 rows in set

How this query works, step by step

Order 1001 has two lines, so it appears twice.
Order 1002 has one line and appears once.
This is expected, but remember it before you SUM order-level columns after such a join: they would be counted once per line.

Join three and four tables

Customer name, product name and line amount for every order line:

SQL

SELECT c.customer_name, o.order_id, p.product_name, oi.quantity,       oi.quantity * p.price AS amountFROM orders oJOIN customers   c  ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products    p  ON p.product_id  = oi.product_idWHERE c.customer_id = 1ORDER BY o.order_id, p.product_name;

ResultMySQL 8.4

customer_name	order_id	product_name	quantity	amount
Ananya Sharma	1001	Mechanical Keyboard	1	3499
Ananya Sharma	1001	Notebook Pack	5	1245
Ananya Sharma	1003	Mechanical Keyboard	1	3499
Ananya Sharma	1003	Wireless Mouse	1	799
Ananya Sharma	1008	Desk Lamp	2	2998
Ananya Sharma	1008	Office Chair	2	17998

6 rows in set

How this query works, step by step

orders joined to customers gives the customer’s name.
Joining order_items expands each order into its lines.
Joining products adds the name and price of each line.
WHERE c.customer_id = 1 keeps Ananya’s lines: 5 lines across 3 orders.

INNER JOIN with GROUP BY

Revenue per customer, highest first:

SQL

SELECT c.customer_name,       COUNT(DISTINCT o.order_id) AS orders,       SUM(oi.quantity * p.price) AS revenueFROM customers cJOIN orders o       ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products p     ON p.product_id  = oi.product_idWHERE o.status <> 'Cancelled'GROUP BY c.customer_id, c.customer_nameORDER BY revenue DESC;

ResultMySQL 8.4

customer_name	orders	revenue
Ananya Sharma	3	30038
Rohit Kumar	2	9798
Ishita Bose	2	6642
Fatima Khan	1	3499
David Fernandes	1	3286

5 rows in set

How this query works, step by step

The joins build one row per order line with its customer and price.
WHERE drops the cancelled order.
GROUP BY the customer adds up the line amounts and counts distinct orders.
Customers without orders are not in the result: use a LEFT JOIN to include them with zero.

Conditions in ON vs WHERE

For an inner join, a filter gives the same result in ON or in WHERE:

SQL

SELECT o.order_id, c.customer_nameFROM orders oJOIN customers c ON c.customer_id = o.customer_id AND c.city = 'Delhi';

ResultMySQL 8.4

order_id	customer_name
1002	Rohit Kumar
1007	Rohit Kumar

2 rows in set

Keep join conditions in ON and filters in WHERE for readability. For outer joins the placement changes the result; see LEFT JOIN.

USING

When both columns have the same name, USING (column) is a shortcut for ON a.column = b.column, and the shared column appears only once in SELECT *:

MySQL

SELECT order_id, customer_id, customer_nameFROM ordersJOIN customers USING (customer_id)WHERE order_id < 1004;

ResultMySQL 8.4

order_id	customer_id	customer_name
1001	1	Ananya Sharma
1003	1	Ananya Sharma
1002	2	Rohit Kumar

3 rows in set

Common mistakes

Forgetting the ON condition (or writing FROM a, b with no WHERE) produces a cross join: every row times every row.
Joining on the wrong column, for example ON c.customer_id = o.order_id, runs without an error and returns nonsense. Always check the row count.
Ambiguous column names: if both tables have dept_id, write e.dept_id or d.dept_id.

Exercise: Products in order 1008

List the product names and quantities in order 1008.

Expected resultMySQL 8.4

product_name	quantity
Office Chair	2
Desk Lamp	2

2 rows in set

Hint 1

Join order_items to products and filter on order_id.

SQL

SELECT p.product_name, oi.quantityFROM order_items oiJOIN products p ON p.product_id = oi.product_idWHERE oi.order_id = 1008;

ResultMySQL 8.4

product_name	quantity
Office Chair	2
Desk Lamp	2

2 rows in set

Exercise: Employees and their department location

Show each employee’s first_name, dept_name and location, for employees whose department is in Mumbai.

Expected resultMySQL 8.4

first_name	dept_name	location
Vikram	Sales	Mumbai
Neha	Sales	Mumbai
Arjun	Sales	Mumbai

3 rows in set

Hint 1

Join employees to departments and filter on d.location.

SQL

SELECT e.first_name, d.dept_name, d.locationFROM employees eJOIN departments d ON d.dept_id = e.dept_idWHERE d.location = 'Mumbai';

ResultMySQL 8.4

first_name	dept_name	location
Vikram	Sales	Mumbai
Neha	Sales	Mumbai
Arjun	Sales	Mumbai

3 rows in set

Quick quiz

1. A customer has no orders. Does customers INNER JOIN orders return them?

2. Order 1001 has two order lines. How many rows does it produce in orders JOIN order_items?

3. What is JOIN without a keyword in MySQL?

JOIN defaults to an inner join.

INNER JOIN FAQ

What does INNER JOIN do in SQL?

It returns rows that have matching values in both tables, according to the condition in ON.

How do I join three tables in SQL?

Add one JOIN ... ON ... per extra table: FROM a JOIN b ON b.x = a.x JOIN c ON c.y = b.y.

Why does my join return duplicate rows?

In a one-to-many relationship each parent row appears once for every matching child row. Aggregate, use DISTINCT deliberately, or join to a pre-aggregated subquery.

Is INNER JOIN faster than LEFT JOIN?

Often slightly, because the optimizer has more freedom to reorder tables. Choose the join that gives the correct result first; indexes on the join columns matter far more.

SQL AUTO_INCREMENT

SQL Tutorial › Database Design › AUTO_INCREMENT

SQL AUTO_INCREMENT

BeginnerLesson 56 of 698 min read4 queries run on MySQL 8.4

AUTO_INCREMENT makes MySQL generate the next number for a column every time you insert a row, so you never have to work out a new id yourself. It is the standard way to create surrogate primary keys. This lesson shows how it counts, how to get the id you just created, and why the numbers sometimes skip.

In this lesson

Create an AUTO_INCREMENT primary key
Get the new id with LAST_INSERT_ID()
Set or reset the starting value
Understand gaps in the sequence

Define an AUTO_INCREMENT column

MySQL

CREATE TABLE tickets (  ticket_id INT AUTO_INCREMENT PRIMARY KEY,  title     VARCHAR(60) NOT NULL);INSERT INTO tickets (title) VALUES ('Login fails on Safari');INSERT INTO tickets (title) VALUES ('Report export is slow'), ('Typo on pricing page');SELECT * FROM tickets;

ResultMySQL 8.4

mysql> CREATE TABLE tickets (

Query OK, 0 rows affected

mysql> INSERT INTO tickets (title) VALUES ('Login fails on Safari')

Query OK, 1 row affected

mysql> INSERT INTO tickets (title) VALUES ('Report export is slow'), ('Typo o…

Query OK, 2 rows affected

mysql> SELECT * FROM tickets

ticket_id	title
1	Login fails on Safari
2	Report export is slow
3	Typo on pricing page

3 rows in set

How this query works, step by step

ticket_id is not given in the inserts; MySQL fills in 1, 2 and 3.
The column must be a key (usually the primary key), and a table can have only one AUTO_INCREMENT column.
Inserting NULL or 0 into the column also generates the next value.

LAST_INSERT_ID()

After an insert, LAST_INSERT_ID() returns the id that was generated, which you need to insert child rows:

MySQL

CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL);CREATE TABLE ticket_comments (comment_id INT AUTO_INCREMENT PRIMARY KEY, ticket_id INT NOT NULL, body VARCHAR(100));INSERT INTO tickets (title) VALUES ('Checkout button missing');INSERT INTO ticket_comments (ticket_id, body) VALUES (LAST_INSERT_ID(), 'Reproduced on Chrome');SELECT t.ticket_id, t.title, c.body FROM tickets t JOIN ticket_comments c ON c.ticket_id = t.ticket_id;

ResultMySQL 8.4

mysql> CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title …

Query OK, 0 rows affected

mysql> CREATE TABLE ticket_comments (comment_id INT AUTO_INCREMENT PRIMARY KE…

Query OK, 0 rows affected

mysql> INSERT INTO tickets (title) VALUES ('Checkout button missing')

Query OK, 1 row affected

mysql> INSERT INTO ticket_comments (ticket_id, body) VALUES (LAST_INSERT_ID()…

Query OK, 1 row affected

mysql> SELECT t.ticket_id, t.title, c.body FROM tickets t JOIN ticket_comment…

ticket_id	title	body
1	Checkout button missing	Reproduced on Chrome

1 row in set

How this query works, step by step

The ticket insert generates ticket_id = 1.
LAST_INSERT_ID() returns 1, so the comment is attached to the right ticket.
The value is per connection: other users inserting at the same time cannot change what your session sees.
For a multi-row insert it returns the id of the first inserted row.

Choose the starting value

MySQL

CREATE TABLE invoices (  invoice_no INT AUTO_INCREMENT PRIMARY KEY,  amount     INT NOT NULL) AUTO_INCREMENT = 1001;INSERT INTO invoices (amount) VALUES (4999), (1250);SELECT * FROM invoices;

ResultMySQL 8.4

mysql> CREATE TABLE invoices (

Query OK, 0 rows affected

mysql> INSERT INTO invoices (amount) VALUES (4999), (1250)

Query OK, 2 rows affected

mysql> SELECT * FROM invoices

invoice_no	amount
1001	4999
1002	1250

2 rows in set

Change it later with ALTER TABLE invoices AUTO_INCREMENT = 5000; (it cannot be set below the current maximum).

Why there are gaps

Auto-increment values are never reused. Deleted rows, rolled-back inserts and failed inserts all leave gaps:

MySQL

CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL UNIQUE);INSERT INTO tickets (title) VALUES ('A'), ('B'), ('C');DELETE FROM tickets WHERE ticket_id = 3;INSERT INTO tickets (title) VALUES ('D');INSERT IGNORE INTO tickets (title) VALUES ('A');INSERT INTO tickets (title) VALUES ('E');SELECT * FROM tickets;

ResultMySQL 8.4

mysql> CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title …

Query OK, 0 rows affected

mysql> INSERT INTO tickets (title) VALUES ('A'), ('B'), ('C')

Query OK, 3 rows affected

mysql> DELETE FROM tickets WHERE ticket_id = 3

Query OK, 1 row affected

mysql> INSERT INTO tickets (title) VALUES ('D')

Query OK, 1 row affected

mysql> INSERT IGNORE INTO tickets (title) VALUES ('A')

Query OK, 0 rows affected, 1 warning

mysql> INSERT INTO tickets (title) VALUES ('E')

Query OK, 1 row affected

mysql> SELECT * FROM tickets

ticket_id	title
1	A
2	B
4	D
6	E

4 rows in set

How this query works, step by step

A, B, C get 1, 2, 3. Deleting 3 does not free the number: D gets 4.
INSERT IGNORE of the duplicate 'A' fails the unique check but has already used up number 5.
E therefore gets 6.

Tip

Treat auto-increment ids as unique labels, not as a count or an order of events. If you need gap-free invoice numbers for legal reasons, generate them separately inside a transaction.

TRUNCATE resets the counter

DELETE FROM t keeps the counter; TRUNCATE TABLE t resets it to 1 (see DELETE & TRUNCATE).

In other databases

Database	Syntax
MySQL	`id INT AUTO_INCREMENT PRIMARY KEY`
PostgreSQL	`id INT GENERATED ALWAYS AS IDENTITY` (or `SERIAL`)
SQL Server	`id INT IDENTITY(1,1) PRIMARY KEY`
SQLite	`id INTEGER PRIMARY KEY` (auto-assigned)
Oracle 12c+	`id NUMBER GENERATED ALWAYS AS IDENTITY`

Quick quiz

1. How do you get the id generated by your last insert in MySQL?

2. A row with id 7 is deleted. Is 7 reused?

3. Why is SELECT MAX(id) a bad way to find your new id?

AUTO_INCREMENT FAQ

What is AUTO_INCREMENT in MySQL?

A column attribute that makes MySQL assign the next integer automatically when a row is inserted without a value for that column.

How do I reset AUTO_INCREMENT in MySQL?

TRUNCATE TABLE t empties the table and resets it, or ALTER TABLE t AUTO_INCREMENT = n sets the next value (at least the current maximum + 1).

Why does my AUTO_INCREMENT skip numbers?

Deletes, rolled-back transactions, failed inserts and INSERT IGNORE / ON DUPLICATE KEY all consume values without leaving rows.

SQL CHECK Constraint

SQL Tutorial › Database Design › CHECK

SQL CHECK Constraint

IntermediateLesson 55 of 698 min read7 queries run on MySQL 8.4

A CHECK constraint validates each row against a condition: prices must be positive, ratings between 1 and 5, an end date after its start date. MySQL accepted the syntax for years but ignored it; since MySQL 8.0.16 checks are enforced, so they are now a real tool for keeping data valid.

In this lesson

Validate values with CHECK conditions
Write rules across several columns
Remember how CHECK treats NULL
Name, add, drop and disable checks

A column check

SQL

CREATE TABLE ratings (  rating_id INT PRIMARY KEY,  stars     INT NOT NULL CHECK (stars BETWEEN 1 AND 5),  comment   VARCHAR(200));INSERT INTO ratings VALUES (1, 5, 'Great course'), (2, 3, NULL);SELECT * FROM ratings;

ResultMySQL 8.4

mysql> CREATE TABLE ratings (

Query OK, 0 rows affected

mysql> INSERT INTO ratings VALUES (1, 5, 'Great course'), (2, 3, NULL)

Query OK, 2 rows affected

mysql> SELECT * FROM ratings

rating_id	stars	comment
1	5	Great course
2	3	NULL

2 rows in set

MySQL

CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT NOT NULL CHECK (stars BETWEEN 1 AND 5));INSERT INTO ratings VALUES (3, 7);

ResultMySQL 8.4

mysql> CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT NOT NULL CH…

Query OK, 0 rows affected

mysql> INSERT INTO ratings VALUES (3, 7)

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

How this query works, step by step

Every insert or update is tested with stars BETWEEN 1 AND 5.
7 fails, so MySQL rejects the row with error 3819 and names the check (ratings_chk_1).

A rule across columns

A table-level check can compare columns, and a name makes the error clearer:

MySQL

CREATE TABLE promotions (  promo_id   INT PRIMARY KEY,  discount   INT NOT NULL,  start_date DATE NOT NULL,  end_date   DATE NOT NULL,  CONSTRAINT chk_discount CHECK (discount > 0 AND discount <= 70),  CONSTRAINT chk_dates CHECK (end_date >= start_date));INSERT INTO promotions VALUES (1, 20, '2026-01-01', '2026-01-31');INSERT INTO promotions VALUES (2, 10, '2026-03-10', '2026-03-01');

ResultMySQL 8.4

mysql> CREATE TABLE promotions (

Query OK, 0 rows affected

mysql> INSERT INTO promotions VALUES (1, 20, '2026-01-01', '2026-01-31')

Query OK, 1 row affected

mysql> INSERT INTO promotions VALUES (2, 10, '2026-03-10', '2026-03-01')

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

How this query works, step by step

Promotion 1 passes both checks.
Promotion 2 ends before it starts, so chk_dates rejects it, and the error message names it.

A list of allowed values:

SQL

CREATE TABLE shipments (  shipment_id INT PRIMARY KEY,  status VARCHAR(12) NOT NULL CHECK (status IN ('packed', 'shipped', 'delivered')));INSERT INTO shipments VALUES (1, 'packed'), (2, 'shipped');SELECT * FROM shipments;

ResultMySQL 8.4

mysql> CREATE TABLE shipments (

Query OK, 0 rows affected

mysql> INSERT INTO shipments VALUES (1, 'packed'), (2, 'shipped')

Query OK, 2 rows affected

mysql> SELECT * FROM shipments

shipment_id	status
1	packed
2	shipped

2 rows in set

CHECK and NULL

A check passes when its condition is UNKNOWN, which happens when a value is NULL:

SQL

CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT CHECK (stars BETWEEN 1 AND 5));INSERT INTO ratings VALUES (1, NULL);SELECT * FROM ratings;

ResultMySQL 8.4

mysql> CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT CHECK (star…

Query OK, 0 rows affected

mysql> INSERT INTO ratings VALUES (1, NULL)

Query OK, 1 row affected

mysql> SELECT * FROM ratings

rating_id	stars
1	NULL

1 row in set

The row is accepted because NULL BETWEEN 1 AND 5 is UNKNOWN, not FALSE. Add NOT NULL when the value is required.

Add, drop and disable a check

MySQL

ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);SELECT constraint_name, check_clause FROM information_schema.check_constraintsWHERE constraint_schema = 'company';

ResultMySQL 8.4

mysql> ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0)

Query OK, 9 rows affected

mysql> SELECT constraint_name, check_clause FROM information_schema.check_con…

CONSTRAINT_NAME	CHECK_CLAUSE
chk_stock	(`stock` >= 0)

1 row in set

MySQL

ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);UPDATE products SET stock = -5 WHERE product_id = 1;

ResultMySQL 8.4

mysql> ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0)

Query OK, 9 rows affected

mysql> UPDATE products SET stock = -5 WHERE product_id = 1

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

ALTER TABLE products DROP CHECK chk_stock; removes it.
ALTER TABLE products ALTER CHECK chk_stock NOT ENFORCED; keeps it but stops checking (useful for bulk loads).

Note

A check may use only the row’s own columns and deterministic functions. It cannot use subqueries, other tables, NOW() or user variables. For rules that involve other tables, use a foreign key or a trigger.

Quick quiz

1. From which MySQL version are CHECK constraints enforced?

2. A column has CHECK (age >= 18). Is a NULL age accepted?

3. Can a CHECK constraint compare two columns of the same row?

CHECK FAQ

Does MySQL support CHECK constraints?

Yes. MySQL 8.0.16 and later enforce CHECK constraints; older versions ignored them.

Can a CHECK constraint reference another table?

No. Checks see only the current row. Use a foreign key or a trigger for cross-table rules.

What is the difference between CHECK and ENUM?

ENUM is a data type that restricts a column to a list. A CHECK can express any row-level rule, including ranges and comparisons between columns, and keeps the column a normal type.

SQL NOT NULL Constraint

SQL Tutorial › Database Design › NOT NULL

SQL NOT NULL Constraint

BeginnerLesson 51 of 697 min read6 queries run on MySQL 8.4

By default any column may hold NULL. A NOT NULL constraint says “this column must always have a value”. It is the simplest and most useful constraint: names, dates and amounts that are required by the business should never be missing, and the database can guarantee that.

In this lesson

Declare required columns with NOT NULL
Recognise NOT NULL errors on INSERT and UPDATE
Add NOT NULL to an existing column safely
Combine NOT NULL with DEFAULT

Define NOT NULL columns

MySQL

CREATE TABLE patients (  patient_id INT PRIMARY KEY,  full_name  VARCHAR(60) NOT NULL,  phone      VARCHAR(15),  admitted   DATE NOT NULL);DESCRIBE patients;

ResultMySQL 8.4

mysql> CREATE TABLE patients (

Query OK, 0 rows affected

mysql> DESCRIBE patients

Field	Type	Null	Key	Default
patient_id	int	NO	PRI	NULL
full_name	varchar(60)	NO		NULL
phone	varchar(15)	YES		NULL
admitted	date	NO		NULL

4 rows in set

How this query works, step by step

full_name and admitted are NOT NULL; the Null column of DESCRIBE shows NO.
phone is optional (YES).
A primary key is automatically NOT NULL.

Inserting without a required value

Leaving a NOT NULL column out:

MySQL

CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);INSERT INTO patients (patient_id, full_name) VALUES (1, 'Ravi Shah');

ResultMySQL 8.4

mysql> CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(6…

Query OK, 0 rows affected

mysql> INSERT INTO patients (patient_id, full_name) VALUES (1, 'Ravi Shah')

ERROR 1364 (HY000): Field 'admitted' doesn't have a default value

Giving NULL explicitly:

MySQL

CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);INSERT INTO patients VALUES (1, NULL, '98450 11111', '2026-01-10');

ResultMySQL 8.4

mysql> CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(6…

Query OK, 0 rows affected

mysql> INSERT INTO patients VALUES (1, NULL, '98450 11111', '2026-01-10')

ERROR 1048 (23000): Column 'full_name' cannot be null

How this query works, step by step

Error 1364: the column was not given and has no default.
Error 1048: an explicit NULL was given for a NOT NULL column.
An UPDATE ... SET full_name = NULL fails with error 1048 in the same way.

Note

An empty string '' is not NULL, so NOT NULL does not stop it. Add a CHECK (full_name <> '') if empty text must be rejected too.

NOT NULL with DEFAULT

Combine them when a value is required but has an obvious standard:

SQL

CREATE TABLE tickets (  ticket_id INT PRIMARY KEY,  status    VARCHAR(10) NOT NULL DEFAULT 'open');INSERT INTO tickets (ticket_id) VALUES (1);SELECT * FROM tickets;

ResultMySQL 8.4

mysql> CREATE TABLE tickets (

Query OK, 0 rows affected

mysql> INSERT INTO tickets (ticket_id) VALUES (1)

Query OK, 1 row affected

mysql> SELECT * FROM tickets

ticket_id	status
1	open

1 row in set

Add NOT NULL to an existing column

MODIFY redefines the column. It fails if existing rows contain NULL:

MySQL

ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL;

ResultMySQL 8.4

ERROR 1138 (22004): Invalid use of NULL value

Fix the data first, then add the constraint:

MySQL

UPDATE customers SET email = 'unknown@example.com' WHERE email IS NULL;ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL;SELECT customer_id, email FROM customers WHERE customer_id = 4;

ResultMySQL 8.4

mysql> UPDATE customers SET email = 'unknown@example.com' WHERE email IS NULL

Query OK, 1 row affected

mysql> ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL

Query OK, 0 rows affected

mysql> SELECT customer_id, email FROM customers WHERE customer_id = 4

customer_id	email
4	unknown@example.com

1 row in set

How this query works, step by step

Customer 4 has no email; the UPDATE fills in a placeholder.
Now no row is NULL, so MODIFY ... NOT NULL succeeds.
MODIFY must repeat the full column type (VARCHAR(60)), or the type would change too.

Remove the constraint again by modifying the column without NOT NULL:

MySQL

ALTER TABLE customers MODIFY email VARCHAR(60) NULL;

Quick quiz

1. Does NOT NULL reject an empty string ”?

2. Why does ALTER TABLE ... MODIFY email ... NOT NULL fail?

NOT NULL FAQ

What does NOT NULL mean in SQL?

The column must always contain a value; inserts or updates that would leave it NULL are rejected.

How do I add a NOT NULL constraint to an existing column in MySQL?

Update existing NULLs to a value, then run ALTER TABLE t MODIFY col TYPE NOT NULL; repeating the column’s full type.

Is a primary key NOT NULL automatically?

Yes. Every primary key column is implicitly NOT NULL.