SQL SELECT Statement

SQL Tutorial › Querying Data › SELECT

SQL SELECT Statement

BeginnerLesson 7 of 6912 min read7 queries run on MySQL 8.4

SELECT is the SQL statement you will write more than any other. It reads rows from a table and returns only the columns you ask for. In this lesson every query runs on a real MySQL 8.4 server, and you see the table it reads, the result it returns and what happens at each step.

In this lesson
  • Read specific columns, or every column with SELECT *
  • Calculate new columns such as yearly salary
  • Understand the order in which MySQL runs a query
  • Avoid the most common SELECT mistakes

What does SELECT do?

A database stores data in tables: rows and columns, like a spreadsheet. SELECT asks the database a question and gets a result set back, which is also a table. It never changes the stored data, so it is always safe to run.

Every query in this lesson reads the employees table of our sample company database. Here are the columns we use:

employees · 12 rows (some columns)
emp_idfirst_namelast_namejob_titlesalarycity
100MeeraKapoorCEO250000Mumbai
101AshaRaoEngineering Manager125000Bengaluru
102RahulMehtaSenior Developer98000Bengaluru
103PriyaNairQA Engineer72000Pune
104KaranShahDeveloper72000Bengaluru
105VikramSinghSales Manager105000Mumbai
106NehaGuptaSales Executive54000Mumbai
107ArjunDasSales Executive48000Kolkata
108KavyaIyerMarketing Lead88000Pune
109RohanJoshiContent Writer45000Pune
110SnehaKulkarniHR Manager78000Delhi
111AmitVermaIntern25000Bengaluru

SELECT syntax

MySQL
SELECT column1, column2, ...FROM table_name;
  • SELECT lists the columns you want, separated by commas.
  • FROM names the table to read.
  • The semicolon ; ends the statement. MySQL needs it when you send several statements together.

Select specific columns

Show each employee’s first name and job title:

SQL
SELECT first_name, job_titleFROM employees;
ResultMySQL 8.4
first_namejob_title
MeeraCEO
AshaEngineering Manager
RahulSenior Developer
PriyaQA Engineer
KaranDeveloper
VikramSales Manager
NehaSales Executive
ArjunSales Executive
KavyaMarketing Lead
RohanContent Writer
SnehaHR Manager
AmitIntern
12 rows in set
How this query works, step by step
  1. FROM employees runs first: MySQL opens the employees table and takes all 12 rows.
  2. SELECT first_name, job_title keeps only those two columns and drops the rest.
  3. The columns appear in the order you listed them, not the order they have in the table.
  4. There is no WHERE, so every row comes back: 12 rows in set.
Tip

Click Try it, change the column list (for example add salary), and press Run. The query runs in your browser on a copy of the same table.

Select all columns with SELECT *

The asterisk * means “every column, in table order”:

SQL
SELECT * FROM employees;
ResultMySQL 8.4
emp_idfirst_namelast_namedept_idmanager_idjob_titlesalarycommissionhire_datecity
100MeeraKapoorNULLNULLCEO250000NULL2015-01-05Mumbai
101AshaRao1100Engineering Manager125000NULL2018-04-12Bengaluru
102RahulMehta1101Senior Developer98000NULL2019-07-01Bengaluru
103PriyaNair1101QA Engineer72000NULL2021-01-18Pune
104KaranShah1102Developer72000NULL2022-08-29Bengaluru
105VikramSingh2100Sales Manager105000120002017-11-20Mumbai
106NehaGupta2105Sales Executive5400060002022-03-14Mumbai
107ArjunDas2105Sales Executive4800045002023-06-05Kolkata
108KavyaIyer3100Marketing Lead88000NULL2020-09-09Pune
109RohanJoshi3108Content Writer45000NULL2024-01-22Pune
110SnehaKulkarni4100HR Manager78000NULL2019-02-25Delhi
111AmitVerma1101Intern25000NULL2025-06-02Bengaluru
12 rows in set
How this query works, step by step
  1. FROM employees reads all 12 rows.
  2. * expands to all 10 columns: emp_id, first_name, last_name, dept_id, manager_id, job_title, salary, commission, hire_date, city.
  3. Empty values show as NULL. Only the three sales staff have a commission, and the CEO has no dept_id or manager_id.
Watch out

SELECT * is fine for exploring a table, but avoid it in application code and reports. It sends columns you do not need, makes queries slower on wide tables, and breaks code silently when someone adds or reorders a column.

Calculated columns and aliases

A column in the SELECT list can be an expression. MySQL calculates it for every row. Use AS to give it a readable name (an alias):

SQL
SELECT first_name,       salary,       salary * 12 AS yearly_salaryFROM employees;
ResultMySQL 8.4
first_namesalaryyearly_salary
Meera2500003000000
Asha1250001500000
Rahul980001176000
Priya72000864000
Karan72000864000
Vikram1050001260000
Neha54000648000
Arjun48000576000
Kavya880001056000
Rohan45000540000
Sneha78000936000
Amit25000300000
12 rows in set
How this query works, step by step
  1. FROM employees reads all 12 rows.
  2. For each row, MySQL multiplies that row’s salary by 12.
  3. AS yearly_salary names the new column. Without it, the header would be the expression text salary * 12.
  4. The stored salary values do not change: the calculation exists only in the result.

You can also join text with CONCAT():

SQL
SELECT emp_id,       CONCAT(first_name, ' ', last_name) AS full_name,       cityFROM employees;
ResultMySQL 8.4
emp_idfull_namecity
100Meera KapoorMumbai
101Asha RaoBengaluru
102Rahul MehtaBengaluru
103Priya NairPune
104Karan ShahBengaluru
105Vikram SinghMumbai
106Neha GuptaMumbai
107Arjun DasKolkata
108Kavya IyerPune
109Rohan JoshiPune
110Sneha KulkarniDelhi
111Amit VermaBengaluru
12 rows in set
How this query works, step by step
  1. For every row, CONCAT() glues first_name, a space and last_name into one string.
  2. The result column is called full_name because of the alias.
  3. emp_id and city are copied as they are.

SELECT without a table

SELECT can also evaluate expressions without reading any table. That is handy for testing a function before you use it in a real query:

MySQL
SELECT 7 * 6 AS answer, UPPER('sqatools') AS site, CURDATE() IS NOT NULL AS has_date;
ResultMySQL 8.4
answersitehas_date
42SQATOOLS1
1 row in set
How this query works, step by step
  1. There is no FROM, so MySQL evaluates each expression once and returns one row.
  2. 7 * 6 is 42, UPPER() turns the text to capitals, and a comparison returns 1 for true.

The order MySQL runs a query

You write a query in one order but MySQL runs it in another. Knowing this explains most beginner errors, such as “unknown column” when you use an alias in WHERE.

StepClauseWhat happens
1FROM / JOINPick the tables and combine them
2WHEREKeep only the rows that match the condition
3GROUP BYPut rows into groups
4HAVINGKeep only the groups that match
5SELECTCalculate the output columns and aliases
6DISTINCTRemove duplicate rows
7ORDER BYSort the result
8LIMITReturn only the first N rows

Because SELECT (step 5) runs after WHERE (step 2), an alias created in SELECT does not exist yet inside WHERE:

MySQL
SELECT first_name, salary * 12 AS yearly_salaryFROM employeesWHERE yearly_salary > 1000000;
ResultMySQL 8.4
ERROR 1054 (42S22): Unknown column 'yearly_salary' in 'where clause'
Note

Repeat the expression in WHERE (WHERE salary * 12 > 1000000) or use a subquery or CTE. ORDER BY runs after SELECT, so ORDER BY yearly_salary does work.

Common SELECT mistakes

  • Missing comma between columns. SELECT first_name job_title FROM employees does not fail: MySQL reads job_title as an alias for first_name, so you get one column with the wrong header.
  • Comma after the last column. SELECT first_name, FROM employees is a syntax error.
  • Quoting column names with single quotes. SELECT 'salary' FROM employees returns the text salary on every row. Write column names without quotes; wrap a name in backticks only when it has spaces or is a reserved word (see SQL Syntax).
  • Expecting a fixed row order. Without ORDER BY the database may return rows in any order. Never rely on the order you happen to see.

Here is the missing-comma trap in action:

SQL
SELECT first_name job_titleFROM employeesLIMIT 3;
ResultMySQL 8.4
job_title
Meera
Asha
Rahul
3 rows in set

Predict the result

What does this query return? Think about how many columns, and what the header is.

SELECT last_name, salary / 1000 AS salary_kFROM employeesLIMIT 2;

Exercise: List names and cities

Write a query that shows every employee’s emp_id, first_name and city, with city renamed to office.

Expected resultMySQL 8.4
emp_idfirst_nameoffice
100MeeraMumbai
101AshaBengaluru
102RahulBengaluru
103PriyaPune
104KaranBengaluru
105VikramMumbai
106NehaMumbai
107ArjunKolkata
108KavyaPune
109RohanPune
110SnehaDelhi
111AmitBengaluru
12 rows in set
Hint 1

Put the three columns after SELECT, separated by commas.

Hint 2

Rename a column with AS office.

Exercise: Monthly bonus

Show each employee’s first_name, salary and a column called bonus that is 10% of the salary.

Expected resultMySQL 8.4
first_namesalarybonus
Meera25000025000.00
Asha12500012500.00
Rahul980009800.00
Priya720007200.00
Karan720007200.00
Vikram10500010500.00
Neha540005400.00
Arjun480004800.00
Kavya880008800.00
Rohan450004500.00
Sneha780007800.00
Amit250002500.00
12 rows in set
Hint 1

10% of a number is the number multiplied by 0.10.

Quick quiz

1. Which clause does MySQL run first?

2. What does SELECT * return?

3. You run SELECT first_name job_title FROM employees. What happens?

4. Does SELECT change the data stored in the table?

SELECT FAQ

What is the SELECT statement in SQL?

SELECT is the SQL command that reads data from one or more tables. You list the columns you want after SELECT and the table after FROM, and the database returns a result set.

Is SELECT * bad practice?

It is fine for quick exploration, but in applications and reports list the columns you need. SELECT * transfers extra data and can break code when the table changes.

Can I use an alias from SELECT in the WHERE clause?

No. WHERE runs before SELECT, so the alias does not exist yet. Repeat the expression, or wrap the query in a subquery or CTE. You can use the alias in ORDER BY.

Is SQL case sensitive?

Keywords such as SELECT and FROM are not case sensitive, so select works too. Writing keywords in capitals is a convention that makes queries easier to read. Whether table names are case sensitive depends on the operating system and MySQL settings.

What is the order of execution of a SQL query?

FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT.

SQL INSERT INTO Statement

SQL Tutorial › Modifying Data › INSERT

SQL INSERT INTO Statement

BeginnerLesson 18 of 6911 min read10 queries run on MySQL 8.4

INSERT INTO adds new rows to a table. You can insert one row, many rows in one statement, or the result of a query. This lesson inserts into the sample customers and products tables and shows exactly what MySQL reports, including the errors you get when a row breaks a rule.

In this lesson
  • Insert one row and many rows
  • Name the columns so inserts survive table changes
  • Rely on defaults and NULLs for missing columns
  • Copy rows with INSERT INTO ... SELECT

INSERT syntax

MySQL
INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...);

The values are matched to the columns by position: the first value goes into the first listed column, and so on.

Sample data

customers · 7 rows
customer_idcustomer_nameemailcityjoined_on
1Ananya Sharmaananya@example.comMumbai2024-01-15
2Rohit Kumarrohit.k@example.comDelhi2024-02-03
3Fatima Khanfatima@example.comHyderabad2024-03-21
4David FernandesNULLGoa2024-05-09
5Ishita Boseishita@example.comKolkata2024-07-30
6Manoj Pillaimanoj.p@example.comChennai2025-01-11
7Sara Thomassara.t@example.comBengaluru2025-03-02

Insert one row

SQL
INSERT INTO customers (customer_id, customer_name, email, city, joined_on)VALUES (8, 'Kiran Rao', 'kiran.rao@example.com', 'Bengaluru', '2025-06-15'); SELECT * FROM customers WHERE customer_id = 8;
ResultMySQL 8.4
mysql> INSERT INTO customers (customer_id, customer_name, email, city, joined…
Query OK, 1 row affected
mysql> SELECT * FROM customers WHERE customer_id = 8
customer_idcustomer_nameemailcityjoined_on
8Kiran Raokiran.rao@example.comBengaluru2025-06-15
1 row in set
How this query works, step by step
  1. The column list names the five columns that receive values.
  2. VALUES (...) supplies one value per column, in the same order. Text and dates are quoted; numbers are not.
  3. MySQL reports Query OK, 1 row affected.
  4. The SELECT reads the new row back.

Insert several rows at once

Separate the row groups with commas. One multi-row INSERT is much faster than many single-row statements:

SQL
INSERT INTO products (product_id, product_name, category, price, stock)VALUES (10, 'Webcam HD', 'Electronics', 2499, 40),       (11, 'Sticky Notes', 'Stationery', 99, 500),       (12, 'Footrest', 'Furniture', 1299, 25); SELECT product_id, product_name, price FROM products WHERE product_id >= 10;
ResultMySQL 8.4
mysql> INSERT INTO products (product_id, product_name, category, price, stock)
Query OK, 3 rows affected
mysql> SELECT product_id, product_name, price FROM products WHERE product_id …
product_idproduct_nameprice
10Webcam HD2499
11Sticky Notes99
12Footrest1299
3 rows in set
How this query works, step by step
  1. Each parenthesised group is one row: three rows here.
  2. MySQL inserts them in one statement and reports 3 rows affected.

Insert only some columns

Columns you leave out get their default value, or NULL if they have no default:

SQL
INSERT INTO customers (customer_id, customer_name, city)VALUES (9, 'Leela Nair', 'Kochi'); SELECT * FROM customers WHERE customer_id = 9;
ResultMySQL 8.4
mysql> INSERT INTO customers (customer_id, customer_name, city)
Query OK, 1 row affected
mysql> SELECT * FROM customers WHERE customer_id = 9
customer_idcustomer_nameemailcityjoined_on
9Leela NairNULLKochiNULL
1 row in set
How this query works, step by step
  1. Only three columns are named.
  2. email and joined_on are not given and have no default, so they are stored as NULL.

Insert without a column list

You may leave out the column list, but then you must give a value for every column in table order:

SQL
INSERT INTO departments VALUES (6, 'Legal', 'Delhi');SELECT * FROM departments;
ResultMySQL 8.4
mysql> INSERT INTO departments VALUES (6, 'Legal', 'Delhi')
Query OK, 1 row affected
mysql> SELECT * FROM departments
dept_iddept_namelocation
1EngineeringBengaluru
2SalesMumbai
3MarketingPune
4HRDelhi
5FinanceMumbai
6LegalDelhi
6 rows in set
Watch out

Avoid this form in application code. If someone adds a column to the table later, every insert without a column list breaks, or worse, puts values into the wrong columns.

Errors you will meet

A duplicate primary key:

MySQL
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Duplicate Person');
ResultMySQL 8.4
ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'

A required column left empty (customer_name is NOT NULL):

MySQL
INSERT INTO customers (customer_id, city) VALUES (10, 'Pune');
ResultMySQL 8.4
ERROR 1364 (HY000): Field 'customer_name' doesn't have a default value

A value count that does not match the column count:

MySQL
INSERT INTO departments (dept_id, dept_name) VALUES (7, 'Support', 'Pune');
ResultMySQL 8.4
ERROR 1136 (21S01): Column count doesn't match value count at row 1

A foreign key that points nowhere (there is no customer 99):

MySQL
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (1011, 99, '2025-06-10', 'Pending');
ResultMySQL 8.4
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
How this query works, step by step
  1. Error 1062: the primary key value already exists.
  2. Error 1364: a NOT NULL column without a default was not given a value.
  3. Error 1136: three values for two columns.
  4. Error 1452: the foreign key on customer_id refuses an order for a customer that does not exist.
Note

An INSERT that fails inserts nothing. In a multi-row insert, one bad row makes the whole statement fail (for InnoDB tables).

INSERT INTO … SELECT

Insert the result of a query instead of literal values. Archive all cancelled orders into a new table:

SQL
CREATE TABLE cancelled_orders (  order_id    INT PRIMARY KEY,  customer_id INT,  order_date  DATE); INSERT INTO cancelled_orders (order_id, customer_id, order_date)SELECT order_id, customer_id, order_dateFROM ordersWHERE status = 'Cancelled'; SELECT * FROM cancelled_orders;
ResultMySQL 8.4
mysql> CREATE TABLE cancelled_orders (
Query OK, 0 rows affected
mysql> INSERT INTO cancelled_orders (order_id, customer_id, order_date)
Query OK, 1 row affected
mysql> SELECT * FROM cancelled_orders
order_idcustomer_idorder_date
100432025-02-14
1 row in set
How this query works, step by step
  1. CREATE TABLE makes an empty archive table.
  2. The SELECT finds the cancelled orders (one row).
  3. INSERT INTO ... SELECT writes every row the query returns into the archive. There is no VALUES keyword.

Insert calculated values

Values can be expressions and functions:

SQL
INSERT INTO products (product_id, product_name, category, price, stock)VALUES (13, UPPER('laptop stand'), 'Furniture', 1500 + 499, 10 * 2); SELECT * FROM products WHERE product_id = 13;
ResultMySQL 8.4
mysql> INSERT INTO products (product_id, product_name, category, price, stock)
Query OK, 1 row affected
mysql> SELECT * FROM products WHERE product_id = 13
product_idproduct_namecategorypricestock
13LAPTOP STANDFurniture199920
1 row in set

Exercise: Add a department

Insert a new department with dept_id 6, name 'Support' and location 'Hyderabad', then show all departments.

Expected resultMySQL 8.4
mysql> INSERT INTO departments (dept_id, dept_name, location)
Query OK, 1 row affected
mysql> SELECT * FROM departments
dept_iddept_namelocation
1EngineeringBengaluru
2SalesMumbai
3MarketingPune
4HRDelhi
5FinanceMumbai
6SupportHyderabad
6 rows in set
Hint 1

Name the three columns, then give three values.

Exercise: Two new customers

Insert customers 8 and 9 in one statement: 'Om Prakash' from 'Jaipur' and 'Rhea Das' from 'Kolkata'. Leave the email and date empty.

Expected resultMySQL 8.4
mysql> INSERT INTO customers (customer_id, customer_name, city)
Query OK, 2 rows affected
mysql> SELECT customer_id, customer_name, city, email FROM customers WHERE cu…
customer_idcustomer_namecityemail
8Om PrakashJaipurNULL
9Rhea DasKolkataNULL
2 rows in set
Hint 1

One INSERT with two row groups separated by a comma.

Quick quiz

1. How are values matched to columns in INSERT INTO t (a, b) VALUES (1, 2)?

2. A column is left out of the INSERT and has no DEFAULT. What is stored?

3. Which statement copies rows from one table into another existing table?

4. Why is a multi-row INSERT preferred to many single-row INSERTs?

INSERT FAQ

How do I insert multiple rows in SQL?

List several value groups after VALUES, separated by commas: INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');

How do I insert data from one table into another?

Use INSERT INTO target (columns) SELECT columns FROM source WHERE ...; The selected columns must match the target columns in number and order.

How do I get the ID of the row I just inserted?

For an AUTO_INCREMENT column, call SELECT LAST_INSERT_ID(); right after the insert, in the same connection. See AUTO_INCREMENT.

How do I insert a row only if it does not exist?

Use INSERT IGNORE, or INSERT ... ON DUPLICATE KEY UPDATE to update the existing row instead. See Upsert.

SQL Introduction

SQL Tutorial › Getting Started › SQL Introduction

SQL Introduction

BeginnerLesson 1 of 6910 min read2 queries run on MySQL 8.4

SQL (Structured Query Language) is the standard language for storing, reading and changing data in relational databases. Banks, shops, hospitals, apps and test frameworks all keep their data in databases, and SQL is how you talk to them. This lesson explains what SQL is, how a database is organised and what kinds of commands SQL has, then you run your first queries.

In this lesson
  • What SQL, a database and an RDBMS are
  • How tables, rows, columns and keys fit together
  • The difference between SQL and MySQL
  • The five groups of SQL commands: DQL, DML, DDL, DCL and TCL

What is SQL?

SQL stands for Structured Query Language. It is pronounced “S-Q-L” or “sequel”. You use it to:

  • read data: which customers ordered this month?
  • add, change and delete data: record a new order, update a price, remove a test account.
  • define the structure: create tables and the rules the data must follow.
  • control access: decide who may read or change what.

SQL is declarative: you describe what result you want, and the database works out how to get it. You never write loops to search a table yourself.

Databases, tables, rows and columns

A relational database stores data in tables. A table looks like a spreadsheet:

  • each column has a name and a data type, for example salary INT;
  • each row is one record, for example one department;
  • a primary key column holds a value that identifies each row uniquely, such as dept_id.

Here is the departments table from the sample database used throughout this tutorial:

departments · 5 rows
dept_iddept_namelocation
1EngineeringBengaluru
2SalesMumbai
3MarketingPune
4HRDelhi
5FinanceMumbai

Tables are linked by keys. Every employee row stores a dept_id, which points at a row in departments. That link is called a foreign key, and it is what makes the database relational.

What is an RDBMS?

An RDBMS (Relational Database Management System) is the software that stores the tables and runs your SQL. Popular ones:

RDBMSMade byTypical use
MySQLOracle (open source)Websites, SaaS apps, WordPress
PostgreSQLOpen source communityApps, analytics, GIS
SQL ServerMicrosoftEnterprise and .NET apps
Oracle DatabaseOracleLarge enterprise systems
SQLiteOpen sourceMobile apps, browsers, small tools

SQL vs MySQL

SQL is the language; MySQL is a database system that understands it. The core of SQL (SELECT, INSERT, JOIN, GROUP BY …) is an ISO standard and works almost the same everywhere. Each product adds its own extras. This tutorial uses MySQL 8.4 LTS, and every result you see was produced by a real MySQL 8.4 server. Where MySQL differs from other databases, the lesson says so.

Your first SQL queries

Ask the database which departments exist and where they are:

SQL
SELECT dept_name, locationFROM departments;
ResultMySQL 8.4
dept_namelocation
EngineeringBengaluru
SalesMumbai
MarketingPune
HRDelhi
FinanceMumbai
5 rows in set
How this query works, step by step
  1. FROM departments tells MySQL which table to read.
  2. SELECT dept_name, location picks two of its three columns.
  3. MySQL returns a result set with one row per department: 5 rows.

Now a question with a condition and a sort: who earns more than 90,000, highest first?

SQL
SELECT first_name, job_title, salaryFROM employeesWHERE salary > 90000ORDER BY salary DESC;
ResultMySQL 8.4
first_namejob_titlesalary
MeeraCEO250000
AshaEngineering Manager125000
VikramSales Manager105000
RahulSenior Developer98000
4 rows in set
How this query works, step by step
  1. FROM employees reads all 12 employees.
  2. WHERE salary > 90000 keeps only the rows whose salary is above 90,000.
  3. SELECT picks the three columns to show.
  4. ORDER BY salary DESC sorts from the highest salary to the lowest.
Tip

Every example with a Try it button runs in your browser. Edit the query, press Run (or Ctrl+Enter), and Reset to get the original back.

The five types of SQL commands

GroupStands forCommandsUsed to
DQLData Query LanguageSELECTread data
DMLData Manipulation LanguageINSERT, UPDATE, DELETEchange rows
DDLData Definition LanguageCREATE, ALTER, DROP, TRUNCATEdefine tables and other objects
DCLData Control LanguageGRANT, REVOKEcontrol permissions
TCLTransaction Control LanguageSTART TRANSACTION, COMMIT, ROLLBACKgroup changes into all-or-nothing units

You will learn each group in this tutorial: queries first, because reading data is what you will do most.

Why learn SQL?

  • It is one of the most requested skills for developers, testers, data analysts and data engineers.
  • It has barely changed in decades, so what you learn now stays useful.
  • Testers use SQL to set up test data and check what an application really saved, not just what the screen shows.
  • Python, Java and every other language talk to databases by sending SQL.

Quick quiz

1. What does SQL stand for?

2. Which statement best describes MySQL?

3. Which command group does INSERT belong to?

4. What links an employee row to its department?

SQL introduction FAQ

What is SQL used for?

SQL is used to read, insert, update and delete data in relational databases, to create tables and other database objects, and to control who can access the data.

Is SQL a programming language?

SQL is a declarative query language. Standard SQL describes what data you want rather than step-by-step instructions, although database systems add procedural features such as stored procedures.

What is the difference between SQL and MySQL?

SQL is the language. MySQL is a relational database management system that stores data and executes SQL statements. PostgreSQL, SQL Server and Oracle also use SQL.

Is SQL hard to learn?

No. Basic queries read almost like English, and most people write useful queries within a day. Joins, grouping and window functions take more practice, which is why this tutorial explains each query step by step.

Which database should a beginner start with?

MySQL is a good first choice: it is free, widely used and well documented. The SQL you learn here transfers to PostgreSQL, SQL Server and others with small changes.

SQL Views

SQL Tutorial › Performance & Objects › Views

SQL Views

IntermediateLesson 62 of 6911 min read6 queries run on MySQL 8.4

A view is a saved SELECT with a name. You query it like a table, but it stores no data of its own: every time you read it, MySQL runs the underlying query on the current data. Views hide complex joins behind a simple name, give every report the same definition of “revenue”, and let you expose only some columns to some users.

In this lesson
  • Create and query a view
  • Change and drop views
  • Know when a view is updatable and what WITH CHECK OPTION does
  • Use views for simplicity and security

CREATE VIEW

An order summary that every report can reuse:

SQL
CREATE VIEW order_summary ASSELECT o.order_id, o.order_date, o.status, c.customer_name,       SUM(oi.quantity * p.price) AS order_totalFROM 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_idGROUP BY o.order_id, o.order_date, o.status, c.customer_name; SELECT * FROM order_summary WHERE status = 'Delivered' ORDER BY order_total DESC;
ResultMySQL 8.4
mysql> CREATE VIEW order_summary AS
Query OK, 0 rows affected
mysql> SELECT * FROM order_summary WHERE status = 'Delivered' ORDER BY order_…
order_idorder_datestatuscustomer_nameorder_total
10022025-01-17DeliveredRohit Kumar8999
10012025-01-05DeliveredAnanya Sharma4744
10032025-02-02DeliveredAnanya Sharma4298
10092025-05-15DeliveredFatima Khan3499
10052025-03-08DeliveredDavid Fernandes3286
10102025-06-01DeliveredIshita Bose1145
10072025-04-10DeliveredRohit Kumar799
7 rows in set
How this query works, step by step
  1. CREATE VIEW order_summary AS SELECT ... saves the query under a name. No rows are copied.
  2. SELECT * FROM order_summary WHERE ... reads it like a table.
  3. MySQL combines the view’s query with your WHERE and ORDER BY and runs them on the live tables.

A view always shows current data

SQL
CREATE VIEW low_stock ASSELECT product_id, product_name, stock FROM products WHERE stock < 20;SELECT * FROM low_stock;UPDATE products SET stock = 5 WHERE product_id = 2;SELECT * FROM low_stock;
ResultMySQL 8.4
mysql> CREATE VIEW low_stock AS
Query OK, 0 rows affected
mysql> SELECT * FROM low_stock
product_idproduct_namestock
3USB-C Hub0
4Office Chair12
5Standing Desk5
9Monitor Arm18
4 rows in set
mysql> UPDATE products SET stock = 5 WHERE product_id = 2
Query OK, 1 row affected
mysql> SELECT * FROM low_stock
product_idproduct_namestock
2Mechanical Keyboard5
3USB-C Hub0
4Office Chair12
5Standing Desk5
9Monitor Arm18
5 rows in set
How this query works, step by step
  1. The first read lists the products with fewer than 20 in stock.
  2. After the keyboard’s stock drops to 5, the same view includes it, without any change to the view.

Change and drop a view

MySQL
CREATE VIEW low_stock AS SELECT product_id, product_name, stock FROM products WHERE stock < 20;CREATE OR REPLACE VIEW low_stock ASSELECT product_id, product_name, category, stock FROM products WHERE stock < 10;SELECT * FROM low_stock;DROP VIEW low_stock;
ResultMySQL 8.4
mysql> CREATE VIEW low_stock AS SELECT product_id, product_name, stock FROM p…
Query OK, 0 rows affected
mysql> CREATE OR REPLACE VIEW low_stock AS
Query OK, 0 rows affected
mysql> SELECT * FROM low_stock
product_idproduct_namecategorystock
3USB-C HubElectronics0
5Standing DeskFurniture5
2 rows in set
mysql> DROP VIEW low_stock
Query OK, 0 rows affected

SHOW FULL TABLES WHERE Table_type = 'VIEW'; lists the views in a database, and SHOW CREATE VIEW name; shows a view’s query.

Updatable views

A simple view over one table, without GROUP BY, DISTINCT, aggregates or UNION, is updatable: changes go straight to the base table.

MySQL
CREATE VIEW furniture ASSELECT product_id, product_name, price, stock FROM products WHERE category = 'Furniture';UPDATE furniture SET price = price - 500 WHERE product_id = 4;SELECT product_id, product_name, price FROM products WHERE product_id = 4;
ResultMySQL 8.4
mysql> CREATE VIEW furniture AS
Query OK, 0 rows affected
mysql> UPDATE furniture SET price = price – 500 WHERE product_id = 4
Query OK, 1 row affected
mysql> SELECT product_id, product_name, price FROM products WHERE product_id …
product_idproduct_nameprice
4Office Chair8499
1 row in set

A view with an aggregate is not:

MySQL
CREATE VIEW category_stock AS SELECT category, SUM(stock) AS units FROM products GROUP BY category;UPDATE category_stock SET units = 0 WHERE category = 'Furniture';
ResultMySQL 8.4
mysql> CREATE VIEW category_stock AS SELECT category, SUM(stock) AS units FRO…
Query OK, 0 rows affected
mysql> UPDATE category_stock SET units = 0 WHERE category = 'Furniture'
ERROR 1288 (HY000): The target table category_stock of the UPDATE is not updatable

WITH CHECK OPTION

Without it, you can insert rows through a view that the view itself will not show. WITH CHECK OPTION refuses them:

MySQL
CREATE VIEW furniture ASSELECT product_id, product_name, category, price, stock FROM products WHERE category = 'Furniture'WITH CHECK OPTION;INSERT INTO furniture VALUES (20, 'Bookshelf', 'Furniture', 4999, 8);INSERT INTO furniture VALUES (21, 'Stapler', 'Stationery', 299, 40);
ResultMySQL 8.4
mysql> CREATE VIEW furniture AS
Query OK, 0 rows affected
mysql> INSERT INTO furniture VALUES (20, 'Bookshelf', 'Furniture', 4999, 8)
Query OK, 1 row affected
mysql> INSERT INTO furniture VALUES (21, 'Stapler', 'Stationery', 299, 40)
ERROR 1369 (HY000): CHECK OPTION failed 'company.furniture'
How this query works, step by step
  1. The bookshelf is Furniture, matches the view’s WHERE, and is inserted.
  2. The stapler is Stationery, which the view would not show, so WITH CHECK OPTION rejects it.

Views for security

Give an analyst access to staff names and departments but not salaries:

MySQL
CREATE VIEW staff_directory ASSELECT emp_id, first_name, last_name, dept_id, city FROM employees; GRANT SELECT ON company.staff_directory TO 'analyst'@'localhost';

The analyst can query staff_directory but has no privilege on employees itself. See GRANT.

View vs table

TableView
Stores datayesno (only the query)
Always currentyesyes, computed on every read
Can have its own indexesyesno, it uses the base tables’ indexes
Speeddirectsame as running the query

MySQL has no materialized views. To store a precomputed result, write it into a summary table (for example with CREATE TABLE ... AS SELECT) and refresh it on a schedule.

Quick quiz

1. Does a view store a copy of the data?

2. Which view can be updated?

3. What does WITH CHECK OPTION prevent?

Views FAQ

What is a view in SQL?

A virtual table defined by a SELECT query. It has a name and columns like a table but stores no data; its rows are computed when it is queried.

What is the difference between a view and a table?

A table stores data. A view stores a query and shows the current result of that query each time you read it.

Can we insert data into a view?

Yes, if the view is updatable: based on one table, with no aggregates, GROUP BY, DISTINCT or UNION. The rows go into the base table.

Does MySQL support materialized views?

No. Use a summary table refreshed by a scheduled event or batch job instead.

SQL Triggers

SQL Tutorial › Performance & Objects › Triggers

SQL Triggers

AdvancedLesson 65 of 6912 min read5 queries run on MySQL 8.4

A trigger is code the database runs automatically when a row is inserted, updated or deleted in a table. Triggers can validate or adjust data before it is saved and react after it is saved: writing an audit log, keeping a stock level in sync, blocking an invalid change. They run no matter which application or user made the change.

In this lesson
  • Create BEFORE and AFTER triggers for INSERT, UPDATE and DELETE
  • Read the row values with NEW and OLD
  • Build an audit log and validate data with SIGNAL
  • Know when not to use triggers

Trigger syntax

MySQL
CREATE TRIGGER trigger_name{BEFORE | AFTER} {INSERT | UPDATE | DELETE}ON table_nameFOR EACH ROWtrigger_body;
EventOLD (row before)NEW (row after)
INSERTnot availablethe row being inserted
UPDATEvalues before the changevalues after the change
DELETEthe row being deletednot available

In a BEFORE trigger you can change NEW values; in an AFTER trigger they are read-only.

BEFORE INSERT: clean data

Store every new customer email in lower case:

MySQL
CREATE TRIGGER trg_customers_biBEFORE INSERT ON customersFOR EACH ROWSET NEW.email = LOWER(TRIM(NEW.email)); INSERT INTO customers (customer_id, customer_name, email, city)VALUES (8, 'Kiran Rao', '  Kiran.Rao@Example.COM ', 'Pune');SELECT customer_id, email FROM customers WHERE customer_id = 8;
ResultMySQL 8.4
mysql> CREATE TRIGGER trg_customers_bi
Query OK, 0 rows affected
mysql> INSERT INTO customers (customer_id, customer_name, email, city)
Query OK, 1 row affected
mysql> SELECT customer_id, email FROM customers WHERE customer_id = 8
customer_idemail
8kiran.rao@example.com
1 row in set
How this query works, step by step
  1. Before the row is written, the trigger replaces NEW.email with the trimmed, lower-case version.
  2. The application sent messy input, but the table stores kiran.rao@example.com.
  3. A single-statement body needs no BEGIN ... END or DELIMITER.

AFTER UPDATE: audit log

Record every salary change, who it was for, and the old and new values:

MySQL
CREATE TABLE salary_audit (  audit_id   INT AUTO_INCREMENT PRIMARY KEY,  emp_id     INT,  old_salary INT,  new_salary INT,  changed_by VARCHAR(80),  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP); DELIMITER //CREATE TRIGGER trg_salary_auditAFTER UPDATE ON employeesFOR EACH ROWBEGIN  IF NEW.salary <> OLD.salary THEN    INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_by)    VALUES (NEW.emp_id, OLD.salary, NEW.salary, CURRENT_USER());  END IF;END //DELIMITER ; UPDATE employees SET salary = salary + 5000 WHERE dept_id = 3;UPDATE employees SET city = 'Pune' WHERE emp_id = 101;SELECT audit_id, emp_id, old_salary, new_salary, changed_by FROM salary_audit;
ResultMySQL 8.4
mysql> CREATE TABLE salary_audit (
Query OK, 0 rows affected
mysql> CREATE TRIGGER trg_salary_audit
Query OK, 0 rows affected
mysql> UPDATE employees SET salary = salary + 5000 WHERE dept_id = 3
Query OK, 2 rows affected
mysql> UPDATE employees SET city = 'Pune' WHERE emp_id = 101
Query OK, 1 row affected
mysql> SELECT audit_id, emp_id, old_salary, new_salary, changed_by FROM salar…
audit_idemp_idold_salarynew_salarychanged_by
11088800093000root@localhost
21094500050000root@localhost
2 rows in set
How this query works, step by step
  1. The trigger fires once for each row the UPDATE changes.
  2. Raising Marketing salaries changes two rows, so two audit rows are written.
  3. Changing Asha’s city fires the trigger too, but the IF sees the salary did not change and writes nothing.
  4. CURRENT_USER() records which account made the change.

BEFORE UPDATE: validate with SIGNAL

Reject salary cuts of more than 20%:

MySQL
DELIMITER //CREATE TRIGGER trg_salary_checkBEFORE UPDATE ON employeesFOR EACH ROWBEGIN  IF NEW.salary < OLD.salary * 0.8 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cut above 20% needs HR approval';  END IF;END //DELIMITER ; UPDATE employees SET salary = 90000 WHERE emp_id = 102;UPDATE employees SET salary = 50000 WHERE emp_id = 102;
ResultMySQL 8.4
mysql> CREATE TRIGGER trg_salary_check
Query OK, 0 rows affected
mysql> UPDATE employees SET salary = 90000 WHERE emp_id = 102
Query OK, 1 row affected
mysql> UPDATE employees SET salary = 50000 WHERE emp_id = 102
ERROR 1644 (45000): Salary cut above 20% needs HR approval
How this query works, step by step
  1. Cutting Rahul’s salary from 98,000 to 90,000 (8%) passes.
  2. Cutting it to 50,000 is more than 20%, so SIGNAL raises error 1644 with the custom message and the update is cancelled.

AFTER INSERT: keep stock in sync

Reduce a product’s stock whenever an order line is added:

MySQL
CREATE TRIGGER trg_order_items_aiAFTER INSERT ON order_itemsFOR EACH ROWUPDATE products SET stock = stock - NEW.quantity WHERE product_id = NEW.product_id; INSERT INTO order_items (order_id, product_id, quantity) VALUES (1009, 6, 10);SELECT product_id, product_name, stock FROM products WHERE product_id = 6;
ResultMySQL 8.4
mysql> CREATE TRIGGER trg_order_items_ai
Query OK, 0 rows affected
mysql> INSERT INTO order_items (order_id, product_id, quantity) VALUES (1009,…
Query OK, 1 row affected
mysql> SELECT product_id, product_name, stock FROM products WHERE product_id …
product_idproduct_namestock
6Notebook Pack290
1 row in set
How this query works, step by step
  1. The new line orders 10 notebook packs.
  2. The trigger subtracts 10 from product 6’s stock: 300 becomes 290.
  3. A matching AFTER DELETE trigger (adding OLD.quantity back) would complete the logic.

AFTER DELETE: keep a history

MySQL
CREATE TABLE deleted_customers AS SELECT * FROM customers WHERE 1 = 0;CREATE TRIGGER trg_customers_adAFTER DELETE ON customersFOR EACH ROWINSERT INTO deleted_customers VALUES (OLD.customer_id, OLD.customer_name, OLD.email, OLD.city, OLD.joined_on); DELETE FROM customers WHERE customer_id = 7;SELECT * FROM deleted_customers;
ResultMySQL 8.4
mysql> CREATE TABLE deleted_customers AS SELECT * FROM customers WHERE 1 = 0
Query OK, 0 rows affected
mysql> CREATE TRIGGER trg_customers_ad
Query OK, 0 rows affected
mysql> DELETE FROM customers WHERE customer_id = 7
Query OK, 1 row affected
mysql> SELECT * FROM deleted_customers
customer_idcustomer_nameemailcityjoined_on
7Sara Thomassara.t@example.comBengaluru2025-03-02
1 row in set

WHERE 1 = 0 copies the table’s columns without any rows.

Manage triggers

TaskStatement
ListSHOW TRIGGERS FROM company;
See the codeSHOW CREATE TRIGGER trg_salary_audit;
RemoveDROP TRIGGER IF EXISTS trg_salary_audit;

Pitfalls

  • Hidden logic: someone reading INSERT INTO order_items cannot see that stock changes. Document triggers well.
  • Performance: triggers run for every row; a bulk update of a million rows runs the trigger a million times.
  • Not fired by TRUNCATE, DROP TABLE or changes made by foreign-key CASCADE actions.
  • A trigger cannot modify the same table it is defined on (other than NEW values in BEFORE triggers).

Quick quiz

1. Which row values are available in a DELETE trigger?

2. Where can you change NEW values?

3. An UPDATE changes 50 rows. How often does a FOR EACH ROW trigger run?

4. How does a trigger reject a change?

Triggers FAQ

What is a trigger in SQL?

A stored program that the database executes automatically before or after an INSERT, UPDATE or DELETE on a table, once for each affected row.

What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row is written and can change or reject it. An AFTER trigger runs after the change and is typically used to update other tables or write logs.

What are NEW and OLD in MySQL triggers?

NEW holds the row’s values after an INSERT or UPDATE; OLD holds the values before an UPDATE or DELETE.

Can a trigger call a stored procedure?

Yes, with CALL, as long as the procedure does not return result sets or use transaction statements.

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_idholderbalance
1Asha8000
2Ravi5000
2 rows in set
How this query works, step by step
  1. START TRANSACTION (or BEGIN) opens a transaction. From now on, changes are pending.
  2. The two UPDATEs move the money. Other sessions still see the old balances.
  3. COMMIT makes both changes permanent and visible to everyone at the same moment.
  4. 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_idholderbalance
1Asha0
2Ravi3000
2 rows in set
mysql> ROLLBACK
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha10000
2Ravi3000
2 rows in set
How this query works, step by step
  1. Inside the transaction Asha’s balance is set to 0, and this session sees the change.
  2. ROLLBACK undoes it.
  3. 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_idholderbalance
1Asha15000
2Ravi3000
2 rows in set
mysql> ROLLBACK
Query OK, 0 rows affected
mysql> SELECT * FROM accounts
account_idholderbalance
1Asha10000
2Ravi3000
2 rows in set
How this query works, step by step
  1. The first SELECT shows Asha at 15,000: MySQL does not undo the whole transaction automatically when one statement fails.
  2. Your code must react to the error by calling ROLLBACK.
  3. 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_idholderbalance
1Asha9000
2Ravi3000
2 rows in set
How this query works, step by step
  1. A fee of 1,000 is taken from Asha.
  2. SAVEPOINT after_fee remembers this point.
  3. A wrong credit to Ravi is made, then undone with ROLLBACK TO SAVEPOINT after_fee.
  4. 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

PropertyMeansIn the transfer example
Atomicityall or nothingdebit and credit both happen, or neither
Consistencyrules always holdCHECK (balance >= 0) is never broken
Isolationconcurrent transactions do not see each other’s half-done worknobody sees the money “in flight”
Durabilitycommitted data survives a crashafter 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
LevelDirty readNon-repeatable readPhantom read
READ UNCOMMITTEDpossiblepossiblepossible
READ COMMITTEDnopossiblepossible
REPEATABLE READ (MySQL default)nonomostly prevented in InnoDB
SERIALIZABLEnonono
  • 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_idholderbalance
1Asha9500
2Ravi3000
2 rows in set
How this query works, step by step
  1. SELECT ... FOR UPDATE reads Asha’s balance and locks her row until the transaction ends.
  2. Another session trying to update or lock the same row waits.
  3. 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?

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

FunctionReturnsSkips NULL?
COUNT(*)number of rowscounts every row
COUNT(col)number of non-NULL valuesyes
SUM(col)totalyes
AVG(col)average (sum ÷ count of non-NULL)yes
MIN(col) / MAX(col)smallest / largest valueyes

Sample data

employees · 12 rows (some columns)
emp_idfirst_namedept_idsalarycommissionhire_date
100MeeraNULL250000NULL2015-01-05
101Asha1125000NULL2018-04-12
102Rahul198000NULL2019-07-01
103Priya172000NULL2021-01-18
104Karan172000NULL2022-08-29
105Vikram2105000120002017-11-20
106Neha25400060002022-03-14
107Arjun24800045002023-06-05
108Kavya388000NULL2020-09-09
109Rohan345000NULL2024-01-22
110Sneha478000NULL2019-02-25
111Amit125000NULL2025-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
employeeswith_departmentwith_commissiondepartments
121134
1 row in set
How this query works, step by step
  1. COUNT(*) counts all 12 rows, NULLs or not.
  2. COUNT(dept_id) skips the CEO, whose dept_id is NULL: 11.
  3. COUNT(commission) counts the 3 sales staff.
  4. 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
payrollavg_salaryavg_rounded
106000088333.333388333
1 row in set
How this query works, step by step
  1. SUM(salary) adds the 12 salaries: 1,060,000.
  2. AVG(salary) divides that by 12. MySQL returns an exact decimal with four places: 88333.3333.
  3. 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_salaryhighest_salaryfirst_hirelatest_hirefirst_alphabetically
250002500002015-01-052025-06-02Amit
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
engineersengineering_payrolltop_salary
5392000125000
1 row in set
How this query works, step by step
  1. WHERE dept_id = 1 keeps the 5 Engineering employees.
  2. 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
revenueunits_soldorders
722623810
1 row in set
How this query works, step by step
  1. The join adds each product’s price to its order lines.
  2. For every line MySQL calculates quantity * price, then SUM adds them all.
  3. 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_sellersavg_of_everyonetotal
7500.00001875.000022500
1 row in set
How this query works, step by step
  1. AVG(commission) ignores the 9 NULLs: 22,500 ÷ 3 = 7,500.
  2. COALESCE(commission, 0) turns NULL into 0, so the average is over all 12 rows: 22,500 ÷ 12 = 1,875.
  3. 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
ntotaltop
0NULLNULL
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
productscheapestdearestunits_in_stock
919918999780
1 row in set
Hint 1

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

Exercise: Delivered orders

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

Expected resultMySQL 8.4
deliveredfirst_datelast_date
72025-01-052025-06-01
1 row in set
Hint 1

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

Quick quiz

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

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?

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_idfirst_namejob_titlemanager_id
100MeeraCEONULL
101AshaEngineering Manager100
102RahulSenior Developer101
103PriyaQA Engineer101
104KaranDeveloper102
105VikramSales Manager100
106NehaSales Executive105
107ArjunSales Executive105
108KavyaMarketing Lead100
109RohanContent Writer108
110SnehaHR Manager100
111AmitIntern101

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_idemployeemanager
101AshaMeera
102RahulAsha
103PriyaAsha
104KaranRahul
105VikramMeera
106NehaVikram
107ArjunVikram
108KavyaMeera
109RohanKavya
110SnehaMeera
111AmitAsha
11 rows in set
How this query works, step by step
  1. employees e is the copy that plays the employee; employees m is the copy that plays the manager.
  2. ON m.emp_id = e.manager_id finds, for each employee, the row whose id equals their manager_id.
  3. 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
employeemanager
Meera(no manager)
AshaMeera
RahulAsha
PriyaAsha
KaranRahul
VikramMeera
NehaVikram
ArjunVikram
KavyaMeera
RohanKavya
SnehaMeera
AmitAsha
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_idmanagerdirect_reports
100Meera4
101Asha3
105Vikram2
102Rahul1
108Kavya1
5 rows in set
How this query works, step by step
  1. Each row of the join is one employee-manager pair.
  2. Grouping by the manager counts their pairs.
  3. 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_1person_2city
PriyaKavyaPune
PriyaRohanPune
KavyaRohanPune
3 rows in set
How this query works, step by step
  1. b.city = a.city pairs people from the same city.
  2. 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).
  3. 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
employeemanagermanagers_manager
RahulAshaMeera
PriyaAshaMeera
KaranRahulAsha
NehaVikramMeera
ArjunVikramMeera
RohanKavyaMeera
AmitAshaMeera
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
categorycheaperdearer
ElectronicsWireless MouseUSB-C Hub
ElectronicsWireless MouseMechanical Keyboard
ElectronicsUSB-C HubMechanical Keyboard
StationeryGel PensNotebook Pack
4 rows in set
Hint 1

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

Quick quiz

1. Why does a self join need two aliases?

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_idfirst_namedept_id
100MeeraNULL
101Asha1
102Rahul1
103Priya1
104Karan1
105Vikram2
106Neha2
107Arjun2
108Kavya3
109Rohan3
110Sneha4
111Amit1
departments · 5 rows
dept_iddept_namelocation
1EngineeringBengaluru
2SalesMumbai
3MarketingPune
4HRDelhi
5FinanceMumbai

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_idfirst_namedept_iddept_name
100MeeraNULLNULL
101Asha1Engineering
102Rahul1Engineering
103Priya1Engineering
104Karan1Engineering
111Amit1Engineering
105Vikram2Sales
106Neha2Sales
107Arjun2Sales
108Kavya3Marketing
109Rohan3Marketing
110Sneha4HR
NULLNULL5Finance
13 rows in set
How this query works, step by step
  1. The LEFT JOIN returns all 12 employees, Meera with NULL department columns.
  2. The RIGHT JOIN returns all departments: the 11 matched employee rows plus Finance with NULL employee columns.
  3. UNION stacks both results and removes exact duplicates, so each matched row appears once: 11 + 1 + 1 = 13 rows.
  4. 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_idfirst_namedept_iddept_name
100MeeraNULLNULL
101Asha1Engineering
102Rahul1Engineering
103Priya1Engineering
104Karan1Engineering
111Amit1Engineering
105Vikram2Sales
106Neha2Sales
107Arjun2Sales
108Kavya3Marketing
109Rohan3Marketing
110Sneha4HR
NULLNULL5Finance
13 rows in set
How this query works, step by step
  1. The first part returns every employee, matched or not.
  2. The second part returns only departments without employees (WHERE e.emp_id IS NULL).
  3. 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_idfirst_namedept_name
100MeeraNULL
NULLNULLFinance
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_idstockcountedissue
24544count differs
55NULLonly in system
6300NULLonly in system
7250NULLonly in system
830NULLonly in system
918NULLonly in system
10NULL7only in warehouse
7 rows in set
How this query works, step by step
  1. The first part lists in-stock products the warehouse did not count.
  2. The second part lists counted items unknown to the system (product 10).
  3. The third part lists products where both exist but the numbers differ (product 2: 45 vs 44).
  4. 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_idfirst_namedept_id
100MeeraNULL
101Asha1
102Rahul1
103Priya1
104Karan1
105Vikram2
106Neha2
107Arjun2
108Kavya3
109Rohan3
110Sneha4
111Amit1
departments · 5 rows
dept_iddept_namelocation
1EngineeringBengaluru
2SalesMumbai
3MarketingPune
4HRDelhi
5FinanceMumbai

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_namefirst_name
EngineeringAmit
EngineeringAsha
EngineeringKaran
EngineeringPriya
EngineeringRahul
SalesArjun
SalesNeha
SalesVikram
MarketingKavya
MarketingRohan
HRSneha
FinanceNULL
12 rows in set
How this query works, step by step
  1. departments is on the right, so all 5 departments are kept.
  2. Departments with employees get one row per employee.
  3. Finance has no employees: it appears once, with NULL in first_name.
  4. 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_iddept_name
5Finance
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_namefirst_name
EngineeringAmit
EngineeringAsha
EngineeringKaran
EngineeringPriya
EngineeringRahul
SalesArjun
SalesNeha
SalesVikram
MarketingKavya
MarketingRohan
HRSneha
FinanceNULL
12 rows in set
How this query works, step by step
  1. departments is now first (the left table), so all departments are kept.
  2. The ON condition is the same equality, just written the other way round.
  3. 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_nameorder_idstatus
Desk Lamp1006Shipped
Desk Lamp1008Pending
Monitor ArmNULLNULL
Office Chair1002Delivered
Office Chair1008Pending
Standing Desk1004Cancelled
6 rows in set
How this query works, step by step
  1. orders JOIN order_items builds the order lines.
  2. RIGHT JOIN products keeps every product, even those with no order line.
  3. 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_nameorder_id
Wireless Mouse1003
Wireless Mouse1007
Mechanical Keyboard1001
Mechanical Keyboard1003
Mechanical Keyboard1009
USB-C Hub1006
Office Chair1002
Office Chair1008
Standing Desk1004
Notebook Pack1001
Notebook Pack1005
Notebook Pack1010
Gel Pens1005
Gel Pens1010
Desk Lamp1006
Desk Lamp1008
Monitor ArmNULL
17 rows in set
Hint 1

Put order_items first and products on the right.

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.