Data Retrieval

Data retrieval fetches a set of rows, which can be regarded as a two-dimensional table.

SELECT

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]

into_option: INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ...

select_expr can be a single * to retrieve all columns. An alias can be given for a select_expr:

SELECT CONCAT(a,'-',b) AS c FROM myTable ORDER BY c;
-- or, without AS
SELECT CONCAT(a,'-',b) c FROM myTable ORDER BY c;

A table alias uses the syntax

tbl_name [[AS] new_name]
      [index_int]

. ORDER BY and GROUP BY can reference column names, column aliases, or column positions (integers starting at 1).

The HAVING clause can use aggregate functions and merges rows that share the specified column values; the WHERE clause cannot:

SELECT a, MAX(b) FROM users
GROUP BY a HAVING MAX(b) > 10;

LIMIT constrains the number of rows fetched:

SELECT * FROM tbl LIMIT 3,10; -- rows 4-13
SELECT * FROM tbl LIMIT 35,9999999; -- rows 36-last

If FOR UPDATE is used with a storage engine that supports page or row locks, examined rows cannot be written by other transactions before this transaction ends, cannot be read with SELECT...LOCK IN SHARE MODE, and cannot be read at certain isolation levels. LOCK IN SHARE MODE prevents the affected rows from being modified until the current transaction commits; other sessions can still read them.

ALL returns all rows including duplicates; DISTINCT removes duplicate rows; DISTINCTROW is a synonym for DISTINCT. For table-level-locking engines (MyISAM, MEMORY, MERGE), HIGH_PRIORITY runs the query even if the table is locked for reading. STRAIGHT_JOIN forces the join order to match the order tables appear in the FROM clause. SQL_BIG_RESULT / SQL_SMALL_RESULT hint the optimizer about expected result size. SQL_BUFFER_RESULT puts the result into a temporary table, freeing table locks early — useful when sending the result set to the client takes a long time. SQL_CALC_FOUND_ROWS calculates the row count ignoring any LIMIT clause; that total can then be retrieved with SELECT FOUND_ROWS(). SQL_NO_CACHE bypasses the query cache for both reading and writing.

This retrieves the last request id of each group:

SELECT test_id, MAX(request_id)
FROM testresults
GROUP BY test_id;

VALUES ROW()...

A table can be constructed on the fly with the VALUES statement:

VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);

VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);

VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);

column_0	column_1	column_2
1	-2	3
5	7	9
4	6	8

UNION, INTERSECT, EXCEPT

UNION combines the results from multiple query blocks. INTERSECT limits the result to rows common to all query blocks. EXCEPT limits the result of the first query block to rows not found in the second. ALL retains duplicates; if neither DISTINCT nor ALL is specified, DISTINCT is the default. INTERSECT has greater precedence than, and is evaluated before, UNION and EXCEPT.

TABLE T1 UNION TABLE T2;
TABLE T1 UNION ALL TABLE T2;
TABLE T1 INTERSECT TABLE T2;
TABLE T1 EXCEPT TABLE T2;

SELECT * FROM T1
EXCEPT
TABLE T2
INTERSECT
VALUES ROW(1,2), ROW(5,6);

CREATE TABLE T1 (a INT, b INT);
CREATE TABLE T2 LIKE T1;
INSERT INTO T1 VALUES (1,2), (3,4), (10,20);
INSERT INTO T2 VALUES (1,2), (3,4), (5,6);

TABLE T1 UNION TABLE T2;

TABLE T1 UNION ALL TABLE T2;

TABLE T1 INTERSECT TABLE T2;

TABLE T1 EXCEPT TABLE T2;

SELECT * FROM T1
  EXCEPT
TABLE T2
  INTERSECT
VALUES ROW(1,2), ROW(5,6);

CREATE TABLE T1 (a INT, b INT);
CREATE TABLE T2 LIKE T1;
INSERT INTO T1 VALUES (1,2), (3,4), (10,20);
INSERT INTO T2 VALUES (1,2), (3,4), (5,6);

TABLE T1 UNION TABLE T2;

TABLE T1 UNION ALL TABLE T2;

TABLE T1 INTERSECT TABLE T2;

TABLE T1 EXCEPT TABLE T2;

SELECT * FROM T1
  EXCEPT
TABLE T2
  INTERSECT
VALUES ROW(1,2), ROW(5,6);

TABLE T1 UNION TABLE T2:

a	b
1	2
3	4
10	20
5	6

TABLE T1 UNION ALL TABLE T2:

a	b
1	2
3	4
10	20
1	2
3	4
5	6

TABLE T1 INTERSECT TABLE T2:

a	b
1	2
3	4

TABLE T1 EXCEPT TABLE T2:

a	b
10	20

SELECT * FROM T1 EXCEPT TABLE T2 INTERSECT VALUES ROW(1,2), ROW(5,6):

a	b
3	4
10	20

To apply an ORDER BY or LIMIT clause to the entire UNION result, parenthesize the individual SELECT statements and place the clause at the end:

(SELECT a FROM t1 WHERE a=100 AND b=5)
UNION
(SELECT a FROM t2 WHERE a=101 AND b=3)
ORDER BY a LIMIT 15;

Unions can be nested:

(SELECT 1 UNION SELECT 1) UNION SELECT
      1;

(SELECT 1 UNION SELECT 1) UNION SELECT 1;

(SELECT 1 UNION SELECT 1) UNION SELECT 1;

1
1

JOIN

table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
| table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor

join_specification: ON search_condition | USING (join_column_list)

JOIN, CROSS JOIN, and INNER JOIN are equivalent in MySQL; they produce a Cartesian product between the joined tables. Each row of the first table is joined to each row of the second. The keyword JOIN itself is optional — a comma-separated FROM list has the same effect:

SELECT * FROM t1 JOIN (t2, t3) ON(t2.a=t1.a AND t3.a=t2.a);
-- equivalent to:
SELECT * FROM t1, t2, t3 ON(t2.a=t1.a AND t3.a=t2.a);

For a LEFT JOIN, rows in the right table with no match are returned with all right-table columns set to NULL. RIGHT JOIN is the mirror image. USING compares like-named columns in both tables and joins rows where the values match. A NATURAL [LEFT] JOIN is equivalent to an INNER JOIN or LEFT JOIN with a USING clause naming every column that exists in both tables. The {OJ ...} syntax exists for ODBC compatibility, and the curly braces must be written literally. STRAIGHT_JOIN behaves like JOIN except that the left table is always read before the right table.

MySQL has no FULL OUTER JOIN keyword; use UNION of a LEFT JOIN and a RIGHT JOIN instead:

SELECT country, Purchases.product, Purchases.price, Shipping.fee
FROM Purchases LEFT JOIN Shipping USING (country)
UNION
SELECT country, Purchases.product, Purchases.price, Shipping.fee
FROM Purchases RIGHT JOIN Shipping USING (country);

CREATE TABLE Purchases (
   product VARCHAR(64),
   price FLOAT,
   country VARCHAR(64)
);
INSERT INTO Purchases VALUES
   ('Vacuum Cleaner', 120, 'UK'),
   ('Intel Processor Chip', 500, 'USA'),
   ('Intelligent Rice Cooker', 200, 'China');

CREATE TABLE Shipping (
   country VARCHAR(64),
   fee FLOAT
);
INSERT INTO Shipping VALUES
   ('China', 20),
   ('USA', 5),
   ('Brazil', 10);

SELECT country, Purchases.product, Purchases.price, Shipping.fee
   FROM Purchases LEFT JOIN Shipping USING (country)
UNION
SELECT country, Purchases.product, Purchases.price, Shipping.fee
   FROM Purchases RIGHT JOIN Shipping USING (country);

CREATE TABLE Purchases (
   product VARCHAR(64),
   price FLOAT,
   country VARCHAR(64)
);
INSERT INTO Purchases VALUES
   ('Vacuum Cleaner', 120, 'UK'),
   ('Intel Processor Chip', 500, 'USA'),
   ('Intelligent Rice Cooker', 200, 'China');

CREATE TABLE Shipping (
   country VARCHAR(64),
   fee FLOAT
);
INSERT INTO Shipping VALUES
   ('China', 20),
   ('USA', 5),
   ('Brazil', 10);

SELECT country, Purchases.product, Purchases.price, Shipping.fee
   FROM Purchases LEFT JOIN Shipping USING (country)
UNION
SELECT country, Purchases.product, Purchases.price, Shipping.fee
   FROM Purchases RIGHT JOIN Shipping USING (country);

country	product	price	fee
UK	Vacuum Cleaner	120	NULL
USA	Intel Processor Chip	500	5
China	Intelligent Rice Cooker	200	20
Brazil	NULL	NULL	10

To select rows in one table that have no match in another:

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL;

SELECT name FROM table2
WHERE name NOT IN (SELECT name FROM table1);

With A(a)=(1,2,3,4) and B(b)=(3,4,5,6), the four join forms compare as follows: a Cartesian product (

SELECT
      * FROM A, B

CREATE TABLE A (a INT);
INSERT INTO A VALUES (1),(2),(3),(4);
CREATE TABLE B (b INT);
INSERT INTO B VALUES (3),(4),(5),(6);

-- Cartesian product
SELECT * FROM A, B;

-- Inner join
SELECT A.*, B.* FROM A, B WHERE A.a = B.b;

-- Left outer join
SELECT * FROM A LEFT JOIN B ON A.a = B.b;

-- Right outer join
SELECT * FROM A RIGHT JOIN B ON A.a = B.b;

CREATE TABLE A (a INT);
INSERT INTO A VALUES (1),(2),(3),(4);
CREATE TABLE B (b INT);
INSERT INTO B VALUES (3),(4),(5),(6);

-- Cartesian product
SELECT * FROM A, B;

-- Inner join
SELECT A.*, B.* FROM A, B WHERE A.a = B.b;

-- Left outer join
SELECT * FROM A LEFT JOIN B ON A.a = B.b;

-- Right outer join
SELECT * FROM A RIGHT JOIN B ON A.a = B.b;

SELECT * FROM A, B (Cartesian product):

a	b
4	3
3	3
2	3
1	3
4	4
3	4
2	4
1	4
4	5
3	5
2	5
1	5
4	6
3	6
2	6
1	6

SELECT A.*, B.* FROM A, B WHERE A.a = B.b (inner join):

a	b
3	3
4	4

SELECT * FROM A LEFT JOIN B ON A.a = B.b (left outer join):

a	b
1	NULL
2	NULL
3	3
4	4

SELECT * FROM A RIGHT JOIN B ON A.a = B.b (right outer join):

a	b
3	3
4	4
NULL	5
NULL	6

This finds symmetric pairs (X1,Y1) and (X2,Y2) where X1=Y2 and X2=Y1, from a HackerRank challenge:

SELECT f1.X, f1.Y
FROM Functions f1 JOIN Functions f2 ON f1.Y=f2.X AND f1.X=f2.Y
WHERE f1.X <= f1.Y
GROUP BY f1.X, f1.Y
HAVING COUNT(*)>1 OR f1.X<>f1.Y
ORDER BY f1.X, f1.Y;

This produces a report joining students to a grade band, from another HackerRank challenge, printing "NULL" as the name for grades below 8:

SELECT
IF(Grade > 7, Name, NULL),
Grade,
Marks
FROM Students JOIN Grades
ON Marks >= Min_Mark AND marks <= Max_Mark
ORDER BY Grade DESC, Name ASC;

CASE... & IF()

CASE has two forms: one with a value immediately following the keyword, and one without:

SELECT CASE 2
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'three' END;

SELECT CASE
WHEN 1 = 2 THEN 'first'
WHEN 2 = 2 THEN 'SECOND'
ELSE 'THIRD' END;

SELECT IF(5<8, 'smaller', 'greater');

SELECT CASE 2
   WHEN 1 THEN 'one'
   WHEN 2 THEN 'two'
   ELSE 'three' END;

SELECT CASE
   WHEN 1 = 2 THEN 'first'
   WHEN 2 = 2 THEN 'SECOND'
   ELSE 'THIRD' END;

SELECT IF(5<8, 'smaller', 'greater');

SELECT CASE 2
   WHEN 1 THEN 'one'
   WHEN 2 THEN 'two'
   ELSE 'three' END;

SELECT CASE
   WHEN 1 = 2 THEN 'first'
   WHEN 2 = 2 THEN 'SECOND'
   ELSE 'THIRD' END;

SELECT IF(5<8, 'smaller', 'greater');

CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'three' END
two

CASE WHEN 1 = 2 THEN 'first' WHEN 2 = 2 THEN 'SECOND' ELSE 'THIRD' END
SECOND

IF(5 < 8, 'smaller', 'greater')
smaller

A pivot transforms data from a long, narrow format into a wide, short format — useful for summarizing data across categories. A pivoted quarterly-sales report can be built with CASE inside SUM():

SELECT
Product,
SUM(CASE Quarter WHEN 'Q1' THEN Sales ELSE 0 END) AS Q1_Sales,
SUM(CASE Quarter WHEN 'Q2' THEN Sales ELSE 0 END) AS Q2_Sales,
SUM(CASE Quarter WHEN 'Q3' THEN Sales ELSE 0 END) AS Q3_Sales
FROM sales_table
GROUP BY Product;

CREATE TABLE Sales_Table (
    Product VARCHAR(16),
    Quarter CHAR(2),
    Sales INT
);
INSERT INTO Sales_Table VALUES
   ('A', 'Q1', 100),
   ('A', 'Q2', 200),
   ('A', 'Q3', 150),
   ('B', 'Q1', 75),
   ('B', 'Q2', 50),
   ('B', 'Q3', 100);

SELECT
   Product,
   SUM(CASE Quarter WHEN 'Q1' THEN Sales ELSE 0 END) AS Q1_Sales,
   SUM(CASE Quarter WHEN 'Q2' THEN Sales ELSE 0 END) AS Q2_Sales,
   SUM(CASE Quarter WHEN 'Q3' THEN Sales ELSE 0 END) AS Q3_Sales
FROM Sales_Table
GROUP BY Product;

CREATE TABLE Sales_Table (
    Product VARCHAR(16),
    Quarter CHAR(2),
    Sales INT
);
INSERT INTO Sales_Table VALUES
   ('A', 'Q1', 100),
   ('A', 'Q2', 200),
   ('A', 'Q3', 150),
   ('B', 'Q1', 75),
   ('B', 'Q2', 50),
   ('B', 'Q3', 100);

SELECT
   Product,
   SUM(CASE Quarter WHEN 'Q1' THEN Sales ELSE 0 END) AS Q1_Sales,
   SUM(CASE Quarter WHEN 'Q2' THEN Sales ELSE 0 END) AS Q2_Sales,
   SUM(CASE Quarter WHEN 'Q3' THEN Sales ELSE 0 END) AS Q3_Sales
FROM Sales_Table
GROUP BY Product;

Product	Q1_Sales	Q2_Sales	Q3_Sales
A	100	200	150
B	75	50	100

HANDLER

A handler provides direct access to a table, as opposed to working through a result set, and can be faster than SELECT when reading large numbers of rows. MyISAM and InnoDB tables support handlers. A handler is usable only by the session that opened it; other sessions can still access the table, which is not locked by this statement, so the data may change (or even become incomplete) as the handler performs successive reads.

HANDLER tbl_name OPEN [ [AS] alias]

HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
[ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
[ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name READ { FIRST | NEXT }
[ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name CLOSE

HANDLER...OPEN makes a table accessible to subsequent HANDLER...READ statements until HANDLER...CLOSE is called or the session ends. HANDLER...READ fetches a row where the specified index satisfies the given values and the WHERE condition is met. If an index spans multiple columns, values can be supplied for only the leftmost columns:

HANDLER tb READ idx = (1,2,3)
HANDLER tb READ idx = (1,2)
HANDLER tb READ idx = (1)

Use the quoted `PRIMARY` to reference a table's primary key. LIMIT fetches a specific number of rows instead of one.

See also Subquery and CTE for alternative ways to structure complex SELECT statements, and Window Functions for row-numbering-based pagination.

Data Manipulation	<<<	Data Manipulation Language	>>>	Subquery
last update: 17 Aug 2026

MENU