Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn to select data in MariaDB Server using SQL. This section covers various SELECT statement clauses, including WHERE, GROUP BY, and ORDER BY, to retrieve and filter your data effectively.
Understand the DUAL table. This dummy table allows selecting values without referencing a real table, often used for calculations or retrieving system variables.
You can use DUAL instead of a table name in situations where no tables are referenced, such as the following SELECT statement:
SELECT 1 + 1 FROM DUAL;
+-------+
| 1 + 1 |
+-------+
| 2 |
+-------+DUAL is purely for the convenience of people who require that all SELECT statements should haveFROM and possibly other clauses. MariaDB ignores the clauses. MariaDB does not require FROM DUAL if no tables are referenced.
FROM DUAL could be used when you only SELECT computed values, but require a WHERE clause, perhaps to test that a script correctly handles empty resultsets:
This page is licensed: GPLv2, originally from
Return a single value. A scalar subquery produces a one-row, one-column result that can be used anywhere a constant or expression is valid.
A scalar subquery is a that returns a single value. This is the simplest form of a subquery, and can be used in most places a literal or single column value is valid.
The data type, length and are all taken from the result returned by the subquery. The result of a subquery can always be NULL, that is, no result returned. Even if the original value is defined as NOT NULL, this is disregarded.
A subquery cannot be used where only a literal is expected, for example expects a literal string containing the file name, and LIMIT requires a literal integer.
Inserting a second row means the subquery is no longer a scalar, and this particular query is not valid:
Learn about Common Table Expressions (CTEs) in MariaDB Server. This section explains how to use CTEs for complex, readable, and reusable subqueries, simplifying data selection and manipulation.
SQL set operations combine the results of multiple query blocks in a single result, using the standard SQL operators EXCEPT, INTERSECT, and UNION, and the Oracle operator MINUS.
trueNOT EXISTStrueEXISTS subqueries ignore the columns specified by the SELECT of the subquery, since they're not relevant. For example,
and
produce identical results.
This page is licensed: CC BY-SA / Gnu FDL
SELECT ... WHERE EXISTS <Table subquery>SELECT col1 FROM t1 WHERE EXISTS (SELECT * FROM t2);No rows in the subquery, so the scalar is NULL:
A more traditional scalar subquery, as part of a WHERE clause:
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE sq1 (num TINYINT);
CREATE TABLE sq2 (num TINYINT);
INSERT INTO sq1 VALUES (1);
INSERT INTO sq2 VALUES (10* (SELECT num FROM sq1));
SELECT * FROM sq2;
+------+
| num |
+------+
| 10 |
+------+INSERT INTO sq1 VALUES (2);
INSERT INTO sq2 VALUES (10* (SELECT num FROM sq1));
ERROR 1242 (21000): Subquery returns more than 1 rowfile_path can be an absolute path, or a relative path starting from the data directory. It can only be specified as a string literal, not as a variable. However, the statement can be dynamically composed and executed as a prepared statement to work around this limitation.
This statement is binary-safe and so is particularly useful for writing BLOB values to file. It can be used, for example, to copy an image or an audio document from the database to a file.
The file must not exist. It cannot be overwritten. A user needs the FILE privilege to run this statement. Also, MariaDB needs permission to write files in the specified location. If the secure_file_priv system variable is set to a non-empty directory name, the file can only be written to that directory.
The character_set_filesystem system variable has controlled interpretation of file names that are given as literal strings.
The character_set_filesystem system variable does not have controlled interpretation of file names that are given as literal strings.
This page is licensed: CC BY-SA / Gnu FDL
SELECT 1 FROM DUAL WHERE FALSE;
Empty set (0.00 sec)Use a subquery as a temporary table. Derived tables allow you to select from the result set of another query within the FROM clause.
Although subqueries are more commonly placed in a WHERE clause, they can also form part of the FROM clause. Such subqueries are commonly called derived tables.
If a subquery is used in this way, you must also use an AS clause to name the result of the subquery.
MariaDB starting with
(no AS clause) are permitted in .
(no AS clause) are not permitted in .
MariaDB starting with
It is possible to assign column names in the derived table name syntax element.
It is not possible to assign column names in the derived table name syntax element.
Assume that, given the data above, you want to return the average total for all students. In other words, the average of Chun's 148 (75+73), Esben's 74 (43+31), etc.
You cannot do the following:
A subquery in the FROM clause is however permitted:
The following is permitted:
In this example, the second column of the derived table dt is used both within (WHERE c2 > 0), and outside, (WHERE a2 > 10), the specification. Both conditions apply to t1.c2.
This page is licensed: CC BY-SA / Gnu FDL
Lock rows for write operations. This clause prevents other transactions from modifying or reading the selected rows until the current transaction ends.
InnoDB supports row-level locking. Selected rows can be locked using LOCK IN SHARE MODE or FOR UPDATE. In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.
The FOR UPDATE clause of SELECT applies only when autocommit is set to 0 or the SELECT is enclosed in a transaction. A lock is acquired on the rows, and other transactions are prevented from writing the rows, acquire locks, and from reading them (unless their isolation level is READ UNCOMMITTED).
If autocommit is set to 1, the LOCK IN SHARE MODE and FOR UPDATE clauses have no effect in InnoDB. For non-transactional storage engines like MyISAM and ARIA, a table level lock will be taken even if autocommit is set to 1.
If the isolation level is set to SERIALIZABLE, all plain SELECT statements are converted to SELECT ... LOCK IN SHARE MODE.
This page is licensed: CC BY-SA / Gnu FDL
Export query results to a text file. This statement writes rows to a file on the server, allowing customization of field and line terminators.
SELECT INTO OUTFILE writes the resulting rows to a file, and allows the use of column and row terminators to specify a particular output format. The default is to terminate fields with tabs () and lines with newlines ().
The file must not exist. It cannot be overwritten. A user needs the privilege to run this statement. Also, MariaDB needs permission to write files in the specified location. If the system variable is set to a non-empty directory name, the file can only be written to that directory.
The statement complements SELECT INTO OUTFILE.
The CHARACTER SET clause specifies the in which the results are to be written. Without the clause, no conversion takes place (the binary character set). In this case, if there are multiple character sets, the output will contain these too, and may not easily be able to be reloaded.
In cases where you have two servers using different character-sets, using SELECT INTO OUTFILE to transfer data from one to the other can have unexpected results. To ensure that MariaDB correctly interprets the escape sequences, use the CHARACTER SET clause on both the SELECT INTO OUTFILE statement and the subsequent statement.
The following example produces a file in the CSV format:
The following ANSI syntax is also supported for simple SELECT without UNION :
If you want to use the ANSI syntax with UNION or similar construct you have to use the syntax:
function
This page is licensed: CC BY-SA / Gnu FDL
Retrieve data from multiple tables based on related columns. This section covers INNER, LEFT, RIGHT, and CROSS JOIN syntax and usage.
Main JOIN page, documenting all join types available in MariaDB.
Introduction to joining tables in MariaDB.
Introduction to advanced joins in MariaDB.
How to use a comma instead of a JOIN clause, and why you should always prefer the latter.
Pass query results to a C procedure. This clause allows processing the result set on the server side before returning it to the client.
The PROCEDURE clause of SELECT passes the whole result set to a Procedure which will process it. These Procedures are not Stored Procedures, and can only be written in the C language, so it is necessary to recompile the server.
Currently, the only available procedure is ANALYSE, which examines the resultset and suggests the optimal datatypes for each column. It is defined in the sql/sql_analyse.cc file, and can be used as an example to create more Procedures.
This clause cannot be used in a view definition.
This page is licensed: CC BY-SA / Gnu FDL
Read rows with a shared lock. This clause ensures rows remain unchanged by other transactions while allowing other sessions to read them.
InnoDB supports row-level locking. Selected rows can be locked using LOCK IN SHARE MODE or . In both cases, a lock is acquired on the rows read by the query, and it will be released when the current transaction is committed.
When LOCK IN SHARE MODE is specified in a statement, MariaDB will wait until all transactions that have modified the rows are committed. Then, a write lock is acquired. All transactions can read the rows, but if they want to modify them, they have to wait until your transaction is committed.
If is set to 1 (the default), the LOCK IN SHARE MODE and clauses have no effect in InnoDB. For non-transactional storage engines like MyISAM and ARIA, a table level lock will be taken even if autocommit is set to 1.
Oracle-compatible synonym for the EXCEPT operator. It returns rows from the first query that are not present in the second query.
MINUS is a synonym for when is set.
This page is licensed: CC BY-SA / Gnu FDL
SELECT col1 FROM t1 WHERE EXISTS (SELECT col2 FROM t2);CREATE TABLE sq1 (num TINYINT);
CREATE TABLE sq2 (num2 TINYINT);
INSERT INTO sq1 VALUES(100);
INSERT INTO sq2 VALUES(40),(50),(60);
SELECT * FROM sq1 WHERE EXISTS (SELECT * FROM sq2 WHERE num2>50);
+------+
| num |
+------+
| 100 |
+------+
SELECT * FROM sq1 WHERE NOT EXISTS (SELECT * FROM sq2 GROUP BY num2 HAVING MIN(num2)=40);
Empty set (0.00 sec)INSERT INTO sq2 VALUES (10* (SELECT num FROM sq3 WHERE num='3'));
SELECT * FROM sq2;
+------+
| num |
+------+
| 10 |
| NULL |
+------+SELECT * FROM sq1 WHERE num = (SELECT MAX(num)/10 FROM sq2);
+------+
| num |
+------+
| 1 |
+------+SELECT ... INTO DUMPFILE 'file_path'SELECT _utf8'Hello world!' INTO DUMPFILE '/tmp/world';
SELECT LOAD_FILE('/tmp/world') AS world;
+--------------+
| world |
+--------------+
| Hello world! |
+--------------+SELECT ... INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
[export_options]
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]Nest queries within other SQL statements. Learn to use scalar, column, row, and table subqueries to filter or calculate data dynamically.
The OFFSET clause allows one to return only those elements of a resultset that come after a specified offset. The FETCH clause specifies the number of rows to return, while ONLY or WITH TIES specifies whether or not to also return any further results that tie for last place according to the ordered resultset.
Either the singular ROW or the plural ROWS can be used after the OFFSET and FETCH clauses; the choice has no impact on the results.
FIRST and NEXT give the same result.
In the case of WITH TIES, an ORDER BY clause is required, otherwise an error will be returned.
Given a table with 6 rows:
OFFSET 2 allows one to skip the first two results:
FETCH FIRST 3 ROWS ONLY limits the results to three rows only:
The same outcome can also be achieved with the LIMIT clause:
WITH TIES ensures the tied result 4 is also returned:
This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);
SET SQL_MODE='ORACLE';
SELECT i FROM seqs WHERE i <= 3 MINUS SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
+------+Group result sets by one or more columns. Learn to aggregate data using functions like SUM, COUNT, and AVG in conjunction with grouping.
Use the GROUP BY clause in a SELECT statement to group rows together that have the same value in one or more column, or the same computed value using expressions with any functions and operators except grouping functions. When you use a GROUP BY clause, you will get a single result row for each group of rows that have the same value for the expression given in GROUP BY.
When grouping rows, grouping values are compared as if by the = operator. For string values, the = operator ignores trailing whitespace and may normalize characters and ignore case, depending on the collation in use.
You can use any of the grouping functions in your select expression. Their values will be calculated based on all the rows that have been grouped together for each result row. If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY is used.
You can use multiple expressions in the GROUP BY clause, separated by commas.
Rows are grouped together if they match on each of the expressions.
You can also use a single integer as the grouping expression. If you use an integer n, the results will be grouped by the _n_th column in the select expression.
The WHERE clause is applied before the GROUP BY clause. It filters non-aggregated
rows before the rows are grouped together. To filter grouped rows based on aggregate values, use the HAVING clause. The HAVING clause takes any expression and evaluates it as a boolean, just like the WHERE clause. You can use grouping functions in the HAVING clause. As with the select expression, if you reference non-grouped columns in the HAVING clause, the behavior is undefined.
By default, if a GROUP BY clause is present, the rows in the output will be sorted by the expressions used in the GROUP BY. You can also specify ASC or DESC (ascending, descending) after those expressions, like in . The default is ASC.
If you want the rows to be sorted by another field, you can add an explicit . If you don't want the result to be ordered, you can add .
The WITH ROLLUP modifier adds extra rows to the result set that represent super-aggregate summaries. For a full description with examples, see .
Consider the following table that records how many times each user has played and won a game:
Get a list of win counts along with a count:
The GROUP BY expression can be a computed value, and can refer back to an identifier specified with AS. Get a list of win averages along with a count:
You can use any in the select expression. For each win average as above, get a list of the average play count taken to get that average:
You can filter on aggregate information using the HAVING clause. The HAVING clause is applied after GROUP BY and allows you to filter on aggregate data that is not available to the WHERE clause. Restrict the above example to results that involve an average number of plays over 20:
This page is licensed: CC BY-SA / Gnu FDL
Define simple temporary result sets. Non-recursive CTEs act like query-local views, improving readability by allowing you to define and reuse subqueries within a single statement.
Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. There are two kinds of CTEs: Non-Recursive, which this article covers; and .
The keyword signifies a CTE. It is given a name, followed by a body (the main query).
CTEs are similar to derived tables:
A non-recursive CTE is basically a query-local . There are several advantages and caveats to them. The syntax is more readable than a nested FROM (SELECT ...)
SELECT customer_id, firstname, surname FROM customer
INTO OUTFILE '/exportdata/customers.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customers;SELECT * INTO OUTFILE "/tmp/skr3"
FROM (SELECT * FROM t1 UNION SELECT * FROM t1);OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }SELECT i FROM t1 FETCH FIRST 2 ROWS WITH TIES;
ERROR 4180 (HY000): FETCH ... WITH TIES requires ORDER BY clause to be presentCREATE OR REPLACE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(2),(3),(4), (4), (5);
SELECT i FROM t1 ORDER BY i ASC;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 4 |
| 5 |
+------+SELECT i FROM t1 ORDER BY i ASC OFFSET 2 ROWS;
+------+
| i |
+------+
| 3 |
| 4 |
| 4 |
| 5 |
+------+SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS ONLY;
+------+
| i |
+------+
| 2 |
| 3 |
| 4 |
+------+SELECT i FROM t1 ORDER BY i ASC LIMIT 3 OFFSET 1;
+------+
| i |
+------+
| 2 |
| 3 |
| 4 |
+------+SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS WITH TIES;
+------+
| i |
+------+
| 2 |
| 3 |
| 4 |
| 4 |
+------+( expression )
[ORDER BY [column[, column...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]CREATE TABLE test.t1 (num INT);
INSERT INTO test.t1 VALUES (1),(2),(3);
(SELECT * FROM test.t1
UNION
VALUES (10))
INTERSECT
VALUES (1),(3),(10),(11);
+------+
| num |
+------+
| 1 |
| 3 |
| 10 |
+------+
((SELECT * FROM test.t1
UNION
VALUES (10))
INTERSECT
VALUES (1),(3),(10),(11))
ORDER BY 1 DESC;
+------+
| num |
+------+
| 10 |
| 3 |
| 1 |
+------+CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);SELECT AVG(SUM(score)) FROM student GROUP BY name;
ERROR 1111 (HY000): Invalid use of group functionSELECT AVG(sq_sum) FROM (SELECT SUM(score) AS sq_sum FROM student GROUP BY name) AS t;
+-------------+
| AVG(sq_sum) |
+-------------+
| 134.0000 |
+-------------+SELECT * FROM (SELECT 1 FROM DUAL), (SELECT 2 FROM DUAL);CREATE OR REPLACE TABLE t1(c1 INT, c2 INT, c3 INT);
SELECT a1, a2 FROM (SELECT c1, c2, c3 FROM t1 WHERE c2 > 0) AS dt (a1, a2, a3);SELECT * FROM trans WHERE period=2001 FOR UPDATE;Using this format makes for a more readable SQL than a nested FROM(SELECT ...) clause:
This can be an 'anti-self join', for example:
Or, for year-over-year comparisons, for example:
Another use is to compare individuals against their group. Below is an example of how this might be executed:
This page is licensed: CC BY-SA / Gnu FDL
WITH engineers AS
( SELECT * FROM employees
WHERE dept = 'Engineering' )
SELECT * FROM engineers
WHERE ...CREATE TABLE plays (name VARCHAR(16), plays INT, wins INT);
INSERT INTO plays VALUES
("John", 20, 5),
("Robert", 22, 8),
("Wanda", 32, 8),
("Susan", 17, 3);SELECT wins, COUNT(*) FROM plays GROUP BY wins;
+------+----------+
| wins | COUNT(*) |
+------+----------+
| 3 | 1 |
| 5 | 1 |
| 8 | 2 |
+------+----------+
3 rows in set (0.00 sec)SELECT (wins / plays) AS winavg, COUNT(*) FROM plays GROUP BY winavg;
+--------+----------+
| winavg | COUNT(*) |
+--------+----------+
| 0.1765 | 1 |
| 0.2500 | 2 |
| 0.3636 | 1 |
+--------+----------+
3 rows in set (0.00 sec)SELECT (wins / plays) AS winavg, AVG(plays) FROM plays
GROUP BY winavg;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.1765 | 17.0000 |
| 0.2500 | 26.0000 |
| 0.3636 | 22.0000 |
+--------+------------+
3 rows in set (0.00 sec)SELECT (wins / plays) AS winavg, AVG(plays) FROM plays
GROUP BY winavg HAVING AVG(plays) > 20;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.2500 | 26.0000 |
| 0.3636 | 22.0000 |
+--------+------------+
2 rows in set (0.00 sec)SELECT * FROM
( SELECT * FROM employees
WHERE dept = 'Engineering' ) AS engineers
WHERE
...WITH engineers AS (
SELECT * FROM employees
WHERE dept IN('Development','Support') ),
eu_engineers AS ( SELECT * FROM engineers WHERE country IN('NL',...) )
SELECT
...
FROM eu_engineers;WITH engineers AS (
SELECT * FROM employees
WHERE dept IN('Development','Support') )
SELECT * FROM engineers E1
WHERE NOT EXISTS
(SELECT 1 FROM engineers E2
WHERE E2.country=E1.country
AND E2.name <> E1.name );WITH sales_product_year AS (
SELECT product, YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year )
SELECT *
FROM sales_product_year CUR,
sales_product_year PREV,
WHERE CUR.product=PREV.product
AND CUR.year=PREV.year + 1
AND CUR.total_amt > PREV.total_amtWITH sales_product_year AS (
SELECT product,
YEAR(ship_date) AS year,
SUM(price) AS total_amt
FROM item_sales
GROUP BY product, year
)
SELECT *
FROM sales_product_year S1
WHERE
total_amt >
(SELECT 0.1 * SUM(total_amt)
FROM sales_product_year S2
WHERE S2.year = S1.year)There are two kinds of CTEs:
Recursive (signified by the RECURSIVE keyword).
You can use table_reference as any normal table in the external SELECT part. You can also use WITH in subqueries, as well as with EXPLAIN and SELECT.
Poorly-formed recursive CTEs can in theory cause infinite loops. The max_recursive_iterations system variable limits the number of recursions.
The CYCLE clause enables CTE cycle detection, avoiding excessive or infinite loops,
MariaDB supports a relaxed, non-standard grammar.
The SQL Standard permits a CYCLE clause, as follows:
WITH RECURSIVE ... (
...
)
CYCLE <cycle column list>
SET <cycle mark column> TO <cycle mark value> DEFAULT <non-cycle mark value>
USING where all clauses are mandatory. MariaDB does not support this, but permits a non-standard relaxed grammar, as follows:
WITH RECURSIVE ... (
...
)
CYCLE <cycle column list> RESTRICTWith the use of CYCLE ... RESTRICT it makes no difference whether the CTE uses UNION ALL or UNION DISTINCT anymore. UNION ALL means "all rows, but without cycles", which is exactly what the CYCLE clause enables. And UNION DISTINCT means all rows should be different, which, again, is what will happen — as uniqueness is enforced over a subset of columns, complete rows will automatically all be different.
CYCLE ... RESTRICT is not available.
Below is an example with the WITH at the top level:
The example below uses WITH in a subquery:
Below is an example of a Recursive CTE:
Consider the following structure and data:
Given the above, the following query would theoretically result in an infinite loop due to the last record in t1 (note that max_recursive_iterations is set to 10 for the purposes of this example, to avoid the excessive number of cycles):
However, the CYCLE ... RESTRICT clause can overcome this:
This page is licensed: CC BY-SA / Gnu FDL
Compare a value against all results from a subquery. The ALL operator returns TRUE if the comparison holds for every row returned by the subquery.
Subqueries using the ALL keyword will return true if the comparison returns true for each row returned by the subquery, or the subquery returns no rows.
scalar_expression may be any expression that evaluates to a single value.
comparison_operator may be any one of: =, >, <, >=, <=, <> or !=
ALL returns:
NULL if the comparison operator returns NULL for at least one row returned by the Table subquery or scalar_expression returns NULL.
FALSE if the comparison operator returns FALSE for at least one row returned by the Table subquery.
NOT IN is an alias for <> ALL.
Since 100 > all of 40,50 and 60, the evaluation is true and the row is returned.
Adding a second row to sq1, where the evaluation for that record is false:
Adding a new row to sq2, causing all evaluations to be false:
When the subquery returns no results, the evaluation is still true:
Evaluating against a NULL will cause the result to be unknown, or not true, and therefore return no rows:
This page is licensed: CC BY-SA / Gnu FDL
Sort query results. This clause arranges the returned rows in ascending or descending order based on specified columns or expressions.
Use the ORDER BY clause to order a resultset, such as that are returned from a statement. You can specify just a column or use any expression with functions. If you are using the GROUP BY clause, you can use grouping functions in ORDER BY. Ordering is done after grouping.
You can use multiple ordering expressions, separated by commas. Rows are sorted by the first expression, then by the second expression if they have the same value for the first, and so on.
Retrieve a single row of multiple values. A row subquery returns a tuple that can be compared against a row constructor in the outer query.
WITH [RECURSIVE] table_reference [(columns_list)] AS (
SELECT ...
)
[CYCLE cycle_column_list RESTRICT]
SELECT ...WITH t AS (SELECT a FROM t1 WHERE b >= 'c')
SELECT * FROM t2, t WHERE t2.c = t.a;SELECT t1.a, t1.b FROM t1, t2
WHERE t1.a > t2.c
AND t2.c IN(WITH t AS (SELECT * FROM t1 WHERE t1.a < 5)
SELECT t2.c FROM t2, t WHERE t2.c = t.a);WITH RECURSIVE ancestors AS
( SELECT * FROM folks
WHERE name="Alex"
UNION
SELECT f.*
FROM folks AS f, ancestors AS a
WHERE f.id = a.father OR f.id = a.mother )
SELECT * FROM ancestors;CREATE TABLE t1 (from_ int, to_ int);
INSERT INTO t1 VALUES (1,2), (1,100), (2,3), (3,4), (4,1);
SELECT * FROM t1;
+-------+------+
| from_ | to_ |
+-------+------+
| 1 | 2 |
| 1 | 100 |
| 2 | 3 |
| 3 | 4 |
| 4 | 1 |
+-------+------+SET max_recursive_iterations=10;
WITH RECURSIVE cte (depth, from_, to_) AS (
SELECT 0,1,1 UNION DISTINCT SELECT depth+1, t1.from_, t1.to_
FROM t1, cte WHERE t1.from_ = cte.to_
)
SELECT * FROM cte;
+-------+-------+------+
| depth | from_ | to_ |
+-------+-------+------+
| 0 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 100 |
| 2 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 1 |
| 5 | 1 | 2 |
| 5 | 1 | 100 |
| 6 | 2 | 3 |
| 7 | 3 | 4 |
| 8 | 4 | 1 |
| 9 | 1 | 2 |
| 9 | 1 | 100 |
| 10 | 2 | 3 |
+-------+-------+------+WITH RECURSIVE cte (depth, from_, to_) AS (
SELECT 0,1,1 UNION SELECT depth+1, t1.from_, t1.to_
FROM t1, cte WHERE t1.from_ = cte.to_
)
CYCLE from_, to_ RESTRICT
SELECT * FROM cte;
+-------+-------+------+
| depth | from_ | to_ |
+-------+-------+------+
| 0 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 100 |
| 2 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 1 |
+-------+-------+------+scalar_expression comparison_operator ALL <Table subquery>TRUETRUECREATE TABLE staff (name VARCHAR(10), age TINYINT);
CREATE TABLE customer (name VARCHAR(10), age TINYINT);
INSERT INTO staff VALUES ('Bilhah',37), ('Valerius',61), ('Maia',25);
INSERT INTO customer VALUES ('Thanasis',48), ('Valerius',61), ('Brion',51);
SELECT * FROM staff WHERE (name,age) = (SELECT name,age FROM customer WHERE name='Valerius');
+----------+------+
| name | age |
+----------+------+
| Valerius | 61 |
+----------+------+SELECT name,age FROM staff WHERE (name,age) IN (SELECT name,age FROM customer);
+----------+------+
| name | age |
+----------+------+
| Valerius | 61 |
+----------+------+CREATE TABLE sq1 (num TINYINT);
CREATE TABLE sq2 (num2 TINYINT);
INSERT INTO sq1 VALUES(100);
INSERT INTO sq2 VALUES(40),(50),(60);
SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);
+------+
| num |
+------+
| 100 |
+------+INSERT INTO sq1 VALUES(30);
SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);
+------+
| num |
+------+
| 100 |
+------+INSERT INTO sq2 VALUES(120);
SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);
Empty set (0.00 sec)SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2 WHERE num2 > 300);
+------+
| num |
+------+
| 100 |
| 30 |
+------+INSERT INTO sq2 VALUES (NULL);
SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);ASC and DESC after each ordering expression to force that ordering to be ascending or descending, respectively. Ordering is ascending by default.You can also use a single integer as the ordering expression. If you use an integer n, the results is ordered by the nth column in the select expression.
When string values are compared, they are compared as if by the STRCMP function. STRCMP ignores trailing whitespace and may normalize characters and ignore case, depending on the collation in use.
Duplicated entries in the ORDER BY clause are removed.
ORDER BY can also be used to order the activities of a DELETE or UPDATE statement (usually with the LIMIT clause).
MariaDB allows packed sort keys and values of non-sorted fields in the sort buffer. This can make filesort temporary files much smaller when VARCHAR, CHAR or BLOB columns are used, notably speeding up some ORDER BY sorts.
MariaDB does not allow packed sort keys and values of non-sorted fields in the sort buffer.
ORDER BY can be used in a multi-table update:
This page is licensed: CC BY-SA / Gnu FDL
Return rows from the first result set that do not appear in the second. This set operator performs a subtraction of two datasets.
The result of EXCEPT contains all records of the left SELECT result set except records which are in right SELECT result set. In other words, it is the subtraction of two result sets.
MINUS is a synonym when is set.
MINUS is a synonym is not available.
Brackets for explicit operation precedence are not supported; use a subquery in the FROM clause as a workaround.
MariaDB supports EXCEPT and in addition to .
The queries before and after EXCEPT must be or statements.
All behavior for naming columns, ORDER BY and LIMIT is the same as for . Note that the alternative syntax is only supported. This allows us to use the WITH TIES clause.
EXCEPT implicitly supposes a DISTINCT operation.
The result of EXCEPT is all records of the left SELECT result except records which are in right SELECT result set, i.e. it is subtraction of two result sets.
EXCEPT and UNION have the same operation precedence and INTERSECT has a higher precedence, unless , in which case all three have the same precedence.
Parentheses can be used to specify precedence. Before this, a syntax error would be returned.
MariaDB starting with
EXCEPT ALL and EXCEPT DISTINCT . The ALL operator leaves duplicates intact, while the DISTINCT operator removes duplicates. DISTINCT is the default behavior if neither operator is supplied.
Only EXCEPT DISTINCT is available.
Show customers which are not employees:
Difference between , EXCEPT and :
Parentheses for specifying precedence:
Here is an example that makes use of the storage engine and the statement, to generate a numeric sequence and remove some arbitrary numbers from it:
(video tutorial)
This page is licensed: CC BY-SA / Gnu FDL
Generate super-aggregate summaries. This modifier adds extra rows to the result set representing subtotals and grand totals for grouped columns.
See SELECT.
The WITH ROLLUP modifier adds extra rows to the result set that represent super-aggregate summaries. The super-aggregated column is represented by a NULL value. Multiple aggregates over different columns will be added if there are multiple GROUP BY columns.
The clause can be used at the same time, and is applied after the WITH ROLLUP rows have been added.
WITH ROLLUP cannot be used with . Some sorting is still possible by using ASC or DESC clauses with the GROUP BY column, although the super-aggregate rows will always be added last.
These examples use the following sample table
The WITH ROLLUP modifier in this example adds an extra row that aggregates both years:
Each time the genre, the year, or the country change, another super-aggregate row is added:
The LIMIT clause, applied after WITH ROLLUP:
Sorting by year descending:
This page is licensed: CC BY-SA / Gnu FDL
Compare a value against any result from a subquery. The ANY (or SOME) operator returns TRUE if the comparison holds for at least one row.
Subqueries using the ANY keyword will return true if the comparison returns true for at least one row returned by the subquery.
The required syntax for an ANY or SOME quantified comparison is:
Or:
scalar_expression may be any expression that evaluates to a single value.
comparison_operator may be any one of =, >, <, >=, <=, <> or !=
ANY returns:
TRUE if the comparison operator returns TRUE for at least one row returned by the Table subquery.
FALSE if the comparison operator returns FALSE for all rows returned by the Table subquery, or Table subquery has zero rows.
NULL if the comparison operator returns
SOME is a synonym for ANY, and IN is a synonym for = ANY .
100 is greater than two of the three values, and so the expression evaluates as true.
SOME is a synonym for ANY:
IN is a synonym for = ANY, and here there are no matches, so no results are returned:
Reading this query, the results may be counter-intuitive. It may seem to read as SELECT * FROM sq1 WHERE num does not match any results in sq2. Since it does match 100, it could seem that the results are incorrect. However, the query returns a result if the match does not match any of sq2. Since 100 already does not match 40, the expression evaluates to true immediately, regardless of the 100's matching. It may be more easily readable to use SOME in a case such as this:
This page is licensed: CC BY-SA / Gnu FDL
Compare implicit and explicit join syntax. This article explains why using the explicit JOIN syntax with an ON clause is preferred over comma-separated tables for readability and precedence.
A query to retrieve the list of phone numbers for clients who ordered in the last two weeks might be written in a couple of ways. Here are two:
SELECT *
FROM
clients
INNER JOIN orders ON clients.id = orders.clientId
INNER JOIN phoneNumbers ON clients.id = phoneNumbers.clientId
WHERE
orderPlaced >= NOW() - INTERVAL 2 WEEK;Does it make a difference? Not much as written. But you should use the second form, for these reasons:
Readability. Once the WHERE clause contains more than two conditions, it becomes tedious to pick out the difference between business logic (only dates in the last two weeks) and relational logic (which fields relate clients to orders). Using the JOIN syntax with an ON clause makes the WHERE list shorter, and makes it very easy to see how tables relate to each other.
Flexibility. Let's say we need to see all clients even if they don't have a phone number in the system. With the second version, it's easy; just change INNER JOIN phoneNumbers to LEFT JOIN phoneNumbers. Try that with the first version, and recent MySQL versions will issue a syntax error because of the change in precedence between the comma operator and the JOIN keyword. The solution is to rearrange the FROM clause or add parentheses to override the precedence, and that quickly becomes frustrating.
Portability. If your queries use standard SQL syntax, you will have an easier time switching to a different database should the need ever arise.
— A blog entry about this topic.
The initial version of this article was copied, with permission, from on 2012-10-05.
This page is licensed: CC BY-SA / Gnu FDL
Process hierarchical data using recursive CTEs. These expressions reference themselves to repeatedly execute a subquery, perfect for traversing tree structures or generating sequences.
Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. CTEs first appeared in the SQL standard in 1999, and the first implementations began appearing in 2007.
There are two kinds of CTEs:
;
Recursive, which this article covers.
SQL is generally poor at recursive structures.
Review restrictions on subquery usage. This page details unsupported operations, such as modifying a table while selecting from it in a subquery.
CREATE TABLE seq (i INT, x VARCHAR(1));
INSERT INTO seq VALUES (1,'a'), (2,'b'), (3,'b'), (4,'f'), (5,'e');
SELECT * FROM seq ORDER BY i;
+------+------+
| i | x |
+------+------+
| 1 | a |
| 2 | b |
| 3 | b |
| 4 | f |
| 5 | e |
+------+------+
SELECT * FROM seq ORDER BY i DESC;
+------+------+
| i | x |
+------+------+
| 5 | e |
| 4 | f |
| 3 | b |
| 2 | b |
| 1 | a |
+------+------+
SELECT * FROM seq ORDER BY x,i;
+------+------+
| i | x |
+------+------+
| 1 | a |
| 2 | b |
| 3 | b |
| 5 | e |
| 4 | f |
+------+------+UPDATE seq SET x='z' WHERE x='b' ORDER BY i DESC LIMIT 1;
SELECT * FROM seq;
+------+------+
| i | x |
+------+------+
| 1 | a |
| 2 | b |
| 3 | z |
| 4 | f |
| 5 | e |
+------+------+CREATE TABLE warehouse (product_id INT, qty INT);
INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);
CREATE TABLE store (product_id INT, qty INT);
INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);
UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty = store.qty+2
WHERE (warehouse.product_id = store.product_id AND store.product_id >= 1)
ORDER BY store.product_id DESC LIMIT 2;
SELECT * FROM warehouse;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 98 |
| 4 | 98 |
+------------+------+
SELECT * FROM store;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 5 |
| 2 | 5 |
| 3 | 7 |
| 4 | 7 |
+------------+------+scalar_expression comparison_operator ANY <Table subquery>SELECT *
FROM
clients,
orders,
phoneNumbers
WHERE
clients.id = orders.clientId
AND clients.id = phoneNumbers.clientId
AND orderPlaced >= NOW() - INTERVAL 2 WEEK;NULLTRUENULLIt's not possible to both modify and select from the same table in a subquery. For example:
There is only partial support for row comparison operations. The expression in
must be scalar and the subquery can only return a single column.
However, because of the way IN is implemented (it is rewritten as a sequence of = comparisons and AND), the expression in
is permitted to be an n-tuple and the subquery can return rows of n-tuples.
For example:
is permitted, but
is not.
Subqueries in the FROM clause cannot be correlated subqueries. They cannot be evaluated for each row of the outer query since they are evaluated to produce a result set during when the query is executed.
A subquery can refer to a stored function which modifies data. This is an extension to the SQL standard, but can result in indeterminate outcomes. For example, take:
where f() inserts rows. The function f() could be executed a different number of times depending on how the optimizer chooses to handle the query.
This sort of construct is therefore not safe to use in replication that is not row-based, as there could be different results on the master and the slave.
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE booksales (
country VARCHAR(35), genre ENUM('fiction','non-fiction'), year YEAR, sales INT);
INSERT INTO booksales VALUES
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);SELECT year, SUM(sales) FROM booksales GROUP BY year;
+------+------------+
| year | SUM(sales) |
+------+------------+
| 2014 | 173944 |
| 2015 | 180518 |
+------+------------+
2 rows in set (0.08 sec)
SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP;
+------+------------+
| year | SUM(sales) |
+------+------------+
| 2014 | 173944 |
| 2015 | 180518 |
| NULL | 354462 |
+------+------------+SELECT country, year, genre, SUM(sales)
FROM booksales GROUP BY country, year, genre;
+----------+------+-------------+------------+
| country | year | genre | SUM(sales) |
+----------+------+-------------+------------+
| Paraguay | 2014 | fiction | 87970 |
| Paraguay | 2014 | non-fiction | 8760 |
| Paraguay | 2015 | fiction | 76940 |
| Paraguay | 2015 | non-fiction | 9030 |
| Senegal | 2014 | fiction | 12234 |
| Senegal | 2014 | non-fiction | 64980 |
| Senegal | 2015 | fiction | 15647 |
| Senegal | 2015 | non-fiction | 78901 |
+----------+------+-------------+------------+
SELECT country, year, genre, SUM(sales)
FROM booksales GROUP BY country, year, genre WITH ROLLUP;
+----------+------+-------------+------------+
| country | year | genre | SUM(sales) |
+----------+------+-------------+------------+
| Paraguay | 2014 | fiction | 87970 |
| Paraguay | 2014 | non-fiction | 8760 |
| Paraguay | 2014 | NULL | 96730 |
| Paraguay | 2015 | fiction | 76940 |
| Paraguay | 2015 | non-fiction | 9030 |
| Paraguay | 2015 | NULL | 85970 |
| Paraguay | NULL | NULL | 182700 |
| Senegal | 2014 | fiction | 12234 |
| Senegal | 2014 | non-fiction | 64980 |
| Senegal | 2014 | NULL | 77214 |
| Senegal | 2015 | fiction | 15647 |
| Senegal | 2015 | non-fiction | 78901 |
| Senegal | 2015 | NULL | 94548 |
| Senegal | NULL | NULL | 171762 |
| NULL | NULL | NULL | 354462 |
+----------+------+-------------+------------+SELECT country, year, genre, SUM(sales)
FROM booksales GROUP BY country, year, genre WITH ROLLUP LIMIT 4;
+----------+------+-------------+------------+
| country | year | genre | SUM(sales) |
+----------+------+-------------+------------+
| Paraguay | 2014 | fiction | 87970 |
| Paraguay | 2014 | non-fiction | 8760 |
| Paraguay | 2014 | NULL | 96730 |
| Paraguay | 2015 | fiction | 76940 |
+----------+------+-------------+------------+SELECT country, year, genre, SUM(sales)
FROM booksales GROUP BY country, year DESC, genre WITH ROLLUP;
+----------+------+-------------+------------+
| country | year | genre | SUM(sales) |
+----------+------+-------------+------------+
| Paraguay | 2015 | fiction | 76940 |
| Paraguay | 2015 | non-fiction | 9030 |
| Paraguay | 2015 | NULL | 85970 |
| Paraguay | 2014 | fiction | 87970 |
| Paraguay | 2014 | non-fiction | 8760 |
| Paraguay | 2014 | NULL | 96730 |
| Paraguay | NULL | NULL | 182700 |
| Senegal | 2015 | fiction | 15647 |
| Senegal | 2015 | non-fiction | 78901 |
| Senegal | 2015 | NULL | 94548 |
| Senegal | 2014 | fiction | 12234 |
| Senegal | 2014 | non-fiction | 64980 |
| Senegal | 2014 | NULL | 77214 |
| Senegal | NULL | NULL | 171762 |
| NULL | NULL | NULL | 354462 |
+----------+------+-------------+------------+scalar_expression comparison_operator SOME <Table subquery>CREATE TABLE sq1 (num TINYINT);
CREATE TABLE sq2 (num2 TINYINT);
INSERT INTO sq1 VALUES(100);
INSERT INTO sq2 VALUES(40),(50),(120);
SELECT * FROM sq1 WHERE num > ANY (SELECT * FROM sq2);
+------+
| num |
+------+
| 100 |
+------+SELECT * FROM sq1 WHERE num < SOME (SELECT * FROM sq2);
+------+
| num |
+------+
| 100 |
+------+SELECT * FROM sq1 WHERE num IN (SELECT * FROM sq2);
Empty set (0.00 sec)INSERT INTO sq2 VALUES(100);
Query OK, 1 row affected (0.05 sec)
SELECT * FROM sq1 WHERE num <> ANY (SELECT * FROM sq2);
+------+
| num |
+------+
| 100 |
+------+SELECT * FROM sq1 WHERE num <> SOME (SELECT * FROM sq2);
+------+
| num |
+------+
| 100 |
+------+CREATE TABLE staff(name VARCHAR(10),age TINYINT);
CREATE TABLE customer(name VARCHAR(10),age TINYINT);INSERT INTO staff VALUES
('Bilhah',37), ('Valerius',61), ('Maia',25);
INSERT INTO customer VALUES
('Thanasis',48), ('Valerius',61), ('Brion',51);SELECT * FROM staff WHERE name IN (SELECT name FROM customer ORDER BY name);
+----------+------+
| name | age |
+----------+------+
| Valerius | 61 |
+----------+------+SELECT * FROM staff WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1);
ERROR 1235 (42000): This version of MariaDB doesn't
yet support 'LIMIT & IN/ALL/ANY/SOME subquery'DELETE FROM staff WHERE name = (SELECT name FROM staff WHERE age=61);
ERROR 1093 (HY000): Table 'staff' is specified twice, both
as a target for 'DELETE' and as a separate source for dataexpr op {ALL|ANY|SOME} subquery,expression [NOT] IN subquerySELECT * FROM staff WHERE (name,age) NOT IN (
SELECT name,age FROM customer WHERE age >=51]
);
+--------+------+
| name | age |
+--------+------+
| Bilhah | 37 |
| Maia | 25 |
+--------+------+SELECT * FROM staff WHERE (name,age) = ALL (
SELECT name,age FROM customer WHERE age >=51
);
ERROR 1241 (21000): Operand should contain 1 column(s)SELECT ... WHERE x IN (SELECT f() ...);table_referencesA table reference is also known as a join expression.
Each table can also be specified as db_name.tabl_name. This allows to write queries which involve multiple databases. See Identifier Qualifiers for syntax details.
The syntax of table_factor is an extension to the SQL Standard. The latter accepts only table_reference, not a list of them inside a pair of parentheses.
This is a conservative extension if we consider each comma in a list of table_reference items as equivalent to an inner join. Consider this query:
It is equivalent to this query:
In general, parentheses can be ignored in join expressions containing only inner join operations. MariaDB also supports nested joins (see Nested Join Optimization).
A table subquery is specified as a parenthesized query and must contain a following derived table name (specified as alias in the above syntax specification).
You can optionally specify a list of column names in parenthesis.
Here, the table subquery for t1 will be materialized and named dt2, with column names ic1, ic2, ic3. These column names are used outside the subquery.
You cannot optionally specify a list of column names in parenthesis.
See also Correlation Column List.
See System-versioned tables for more information about the FOR SYSTEM_TIME syntax.
Index hints can be specified to affect how the MariaDB optimizer makes use of indexes. For more information, see How to force query plans.
When is active, the Oracle-style + syntax can be used. For example, the following two queries are identical:
and
Similarly, the following two queries are identical:
and
This page is licensed: GPLv2, originally from fill_help_tables.sql
WITH RECURSIVE signifies a recursive CTE. It is given a name, followed by a body (the main query) as follows:
Given the following structure:
First execute the anchor part of the query:
Next, execute the recursive part of the query:
Compute anchor_data.
Compute recursive_part to get the new data.
If (new data is non-empty) goto 2.
As currently implemented by MariaDB and by the SQL Standard, data may be truncated if not correctly cast. It is necessary to CAST the column to the correct width if the CTE's recursive part produces wider values for a column than the CTE's nonrecursive part. Some other DBMS give an error in this situation, and MariaDB's behavior may change in future - see MDEV-12325. See the examples below.
Sample data:
Now, we want to return the bus destinations with New York as the origin:
The above example is computed as follows:
First, the anchor data is calculated:
Starting from New York.
Boston and Washington are added.
Next, the recursive part:
Starting from Boston and then Washington.
Raleigh is added.
UNION excludes nodes that are already present.
This time, we are trying to get bus routes such as “New York -> Washington -> Raleigh”.
Using the same sample data as the previous example:
In the following example, data is truncated because the results are not specifically cast to a wide enough type:
Explicitly use CAST to overcome this:
This page is licensed: CC BY-SA / Gnu FDL
SELECT ic1, ic2, ic3 FROM
(
SELECT c1, c2, c3 FROM t1 GROUP BY c1
) dt2 (ic1, ic2, ic3)
JOIN t2 ON t2.c1 = dt2.ic1
WHERE c2 > 0
GROUP BY ic1table_references:
table_reference [, table_reference] ...
table_reference:
table_factor
| join_table
table_factor (<= MariaDB 11.6):
tbl_name [PARTITION (partition_list)]
[query_system_time_period_specification] [[AS] alias] [index_hint_list]
| table_subquery [query_system_time_period_specification] [AS] alias
| ( table_references )
| { ON table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
table_factor (>= MariaDB 11.7):
tbl_name [PARTITION (partition_list)]
[query_system_time_period_specification] [[AS] alias] [index_hint_list]
| table_subquery [query_system_time_period_specification] [AS] alias [(column_name_list)]
| ( table_references )
| { ON table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
query_system_time_period_specification:
FOR SYSTEM_TIME AS OF point_in_time
| FOR SYSTEM_TIME BETWEEN point_in_time AND point_in_time
| FOR SYSTEM_TIME FROM point_in_time TO point_in_time
| FOR SYSTEM_TIME ALL
point_in_time:
[TIMESTAMP] expression
| TRANSACTION expression
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;SELECT * FROM t1, t2 WHERE t1.a = t2.b(+);SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.b;SELECT * FROM t1, t2 WHERE t1.a(+) = t2.b;SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;WITH RECURSIVE R AS (
SELECT anchor_data
UNION [all]
SELECT recursive_part
FROM R, ...
)
SELECT ...CREATE TABLE bus_routes (origin VARCHAR(50), dst VARCHAR(50));
INSERT INTO bus_routes VALUES
('New York', 'Boston'),
('Boston', 'New York'),
('New York', 'Washington'),
('Washington', 'Boston'),
('Washington', 'Raleigh');WITH RECURSIVE bus_dst as (
SELECT origin as dst FROM bus_routes WHERE origin='New York'
UNION
SELECT bus_routes.dst FROM bus_routes JOIN bus_dst ON bus_dst.dst= bus_routes.origin
)
SELECT * FROM bus_dst;
+------------+
| dst |
+------------+
| New York |
| Boston |
| Washington |
| Raleigh |
+------------+WITH RECURSIVE paths (cur_path, cur_dest) AS (
SELECT origin, origin FROM bus_routes WHERE origin='New York'
UNION
SELECT CONCAT(paths.cur_path, ',', bus_routes.dst), bus_routes.dst
FROM paths
JOIN bus_routes
ON paths.cur_dest = bus_routes.origin AND
NOT FIND_IN_SET(bus_routes.dst, paths.cur_path)
)
SELECT * FROM paths;
+-----------------------------+------------+
| cur_path | cur_dest |
+-----------------------------+------------+
| New York | New York |
| New York,Boston | Boston |
| New York,Washington | Washington |
| New York,Washington,Boston | Boston |
| New York,Washington,Raleigh | Raleigh |
+-----------------------------+------------+WITH RECURSIVE tbl AS (
SELECT NULL AS col
UNION
SELECT "THIS NEVER SHOWS UP" AS col FROM tbl
)
SELECT col FROM tbl
+------+
| col |
+------+
| NULL |
| |
+------+WITH RECURSIVE tbl AS (
SELECT CAST(NULL AS CHAR(50)) AS col
UNION SELECT "THIS NEVER SHOWS UP" AS col FROM tbl
)
SELECT * FROM tbl;
+---------------------+
| col |
+---------------------+
| NULL |
| THIS NEVER SHOWS UP |
+---------------------+SELECT ...
(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT])
SELECT ...
[(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT])
SELECT ...]
[ORDER BY [{col_name | expr | position} [ASC | DESC]
[, {col_name | expr | position} [ASC | DESC] ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}
| OFFSET start { ROW | ROWS }
| FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ](SELECT e_name AS name, email FROM customers)
EXCEPT
(SELECT c_name AS name, email FROM employees);CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);
SELECT i FROM seqs WHERE i <= 3 UNION SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
SELECT i FROM seqs WHERE i <= 3 UNION ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
SELECT i FROM seqs WHERE i <= 3 EXCEPT SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
SELECT i FROM seqs WHERE i <= 3 EXCEPT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 2 |
+------+
SELECT i FROM seqs WHERE i <= 3 INTERSECT SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 3 |
+------+
SELECT i FROM seqs WHERE i <= 3 INTERSECT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 3 |
| 3 |
+------+CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TABLE t2 (b INT);
CREATE OR REPLACE TABLE t3 (c INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (5),(6);
INSERT INTO t3 VALUES (1),(6);
((SELECT a FROM t1) UNION (SELECT b FROM t2)) EXCEPT (SELECT c FROM t3);
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
| 5 |
+------+
(SELECT a FROM t1) UNION ((SELECT b FROM t2) EXCEPT (SELECT c FROM t3));
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+(SELECT seq FROM seq_1_to_10) EXCEPT VALUES (2), (3), (4);
+-----+
| seq |
+-----+
| 1 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+-----+
Retrieve data from the database. This fundamental statement selects columns and rows from tables, supporting filtering, joining, and aggregation.
[/*+ hints */] syntax is available.
[/*+ hints */] syntax is not available.
Available join order hints .
Join order hints are not available.
[/*+ MAX_EXECUTION_TIME(milliseconds) */] syntax is available.
The hint limits the time of statement execution to the number of milliseconds given in the hint argument.
[/*+ MAX_EXECUTION_TIME(milliseconds) */] syntax is not available.
SELECT is used to retrieve rows selected from one or more tables, and can include statements and .
Each select_expr expression indicates a column or data that you want to retrieve. You must have at least one select expression. See below.
The FROM clause indicates the table or tables from which to retrieve rows. Use either a single table name or a JOIN expression. See for details. If no table is involved, can be specified.
Each table can also be specified as db_name
SELECT can also be used to retrieve rows computed without reference to any table.
A SELECT statement must contain one or more select expressions, separated by commas. Each select expression can be one of the following:
The name of a column.
Any expression using .
* to select all columns from all tables in the FROM clause.
tbl_name.*
When specifying a column, you can either use just the column name or qualify the column name with the name of the table using tbl_name.col_name. The qualified form is useful if you are joining multiple tables in the FROM clause. If you do not qualify the column names when selecting from multiple tables, MariaDB will try to find the column in each table. It is an error if that column name exists in multiple tables.
You can quote column names using backticks. If you are qualifying column names with table names, quote each part separately as tbl_name`.`col_name.
If you use any in any of the select expressions, all rows in your results will be implicitly grouped, as if you had used GROUP BY NULL. GROUP BY NULL being an expression behaves specially such that the entire result set is treated as a group.
A query may produce some identical rows. By default, all rows are retrieved, even when their values are the same. To explicitly specify that you want to retrieve identical rows, use the ALL option. If you want duplicates to be removed from the result set, use the DISTINCT option. DISTINCTROW is a synonym for DISTINCT. See also and .
The INTO clause is used to specify that the query results should be written to a file or variable.
- formatting and writing the result to an external file.
- binary-safe writing of the unformatted results to an external file.
- selecting and setting variables.
The reverse of SELECT INTO OUTFILE is .
Restricts the number of returned rows. See and for details.
See and for details on the respective locking clauses.
See .
The clause doesn't exist.
Order a result set. See for details.
Specifies to the optimizer which partitions are relevant for the query. Other partitions will not be read. See for details.
Passes the whole result set to a C Procedure. See and (the only built-in procedure not requiring the server to be recompiled).
This causes rows that couldn't be locked ( or ) to be excluded from the result set. An explicit NOWAIT is implied here. This is only implemented on tables and ignored otherwise.
The clause doesn't exist.
These include , , , , , and .
See for details.
By using in conjunction with , it is possible to limit the execution time of individual queries. For example:
Set the lock wait timeout. See .
See (Beginner tutorial), or the various sub-articles, for more examples.
(Beginner tutorial)
This page is licensed: GPLv2, originally from
SELECT
[/*+ hints */]
[/*+ JOIN_PREFIX(argument_list) */]
[/*+ JOIN_ORDER(argument_list) */]
[/*+ JOIN_FIXED_ORDER(argument_list) */]
[/*+ JOIN_SUFFIX(argument_list) */]
[/*+ MAX_EXECUTION_TIME(milliseconds) */]
[ALL | DISTINCT | DISTINCTROW]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[ FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset
[ROWS EXAMINED rows_limit] } |
[OFFSET start { ROW | ROWS }]
[FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }] ]
procedure|[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options] |
INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ]
[FOR UPDATE lock_option | LOCK IN SHARE MODE lock_option]
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
lock_option:
[WAIT n | NOWAIT | SKIP LOCKED]tabl_nametbl_namecol_namedb_nametbl_namecol_nameThe WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. The where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.
In the WHERE clause, you can use any of the functions and operators that MariaDB supports, except for aggregate (summary) functions. See Functions and Operators and Functions and Modifiers for use with GROUP BY (aggregate).
Use the ORDER BY clause to order the results.
Use the LIMIT clause to restrict the results to only a certain number of rows, optionally with an offset.
Use the GROUP BY and HAVING clauses to group rows together when they have columns or computed values in common.
tbl_nameUNION is used to combine the results from multiple SELECT statements into a single result set.
The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)
If they don't, the type and length of the columns in the result take into account the values returned by all of the SELECTs, so there is no need for explicit casting. Note that currently this is not the case for recursive CTEs - see MDEV-12325.
Table names can be specified as db_name.tbl_name. This permits writing UNIONs which involve multiple databases. See Identifier Qualifiers for syntax details.
UNION queries cannot be used with aggregate functions.
EXCEPT and UNION have the same operation precedence and INTERSECT has a higher precedence, unless , in which case all three have the same precedence.
The ALL keyword causes duplicate rows to be preserved. The DISTINCT keyword (the default if the keyword is omitted) causes duplicate rows to be removed by the results.
UNION ALL and UNION DISTINCT can both be present in a query. In this case, UNION DISTINCT will override any UNION ALL to its left.
The server can in most cases execute UNION ALL without creating a temporary table (see MDEV-334).
Individual SELECT statements can contain their own ORDER BY and LIMIT clauses. In this case, the individual queries need to be wrapped between parentheses. However, this does not affect the order of the UNION, so they only are useful to limit the record read by one SELECT.
The UNION can have global ORDER BY and LIMIT clauses, which affect the whole result set. If the columns retrieved by individual SELECT statements have an alias (AS), the ORDER BY must use that alias, not the real column names.
Specifying a query as HIGH_PRIORITY does not work inside a UNION. If applied to the first SELECT, it is ignored. Applying to a later SELECT results in a syntax error:
Individual SELECT statements cannot be written INTO DUMPFILE or INTO OUTFILE. If the last SELECT statement specifies INTO DUMPFILE or INTO OUTFILE, the entire result of the UNION will be written. Placing the clause after any other SELECT will result in a syntax error.
If the result is a single row, SELECT ... INTO @var_name can also be used.
Parentheses can be used to specify precedence.
Parentheses cannot be used to specify precedence.
UNION between tables having different column names:
Specifying the UNION global order and limiting total rows:
Adding a constant row:
Differing types:
Returning the results in order of each individual SELECT by use of a sort column:
(video tutorial)
This page is licensed: GPLv2, originally from fill_help_tables.sql
All behavior for naming columns, ORDER BY and LIMIT is the same as for UNION.
INTERSECT implicitly supposes a DISTINCT operation.
The result of an intersect is the intersection of right and left SELECT results, i.e. only records that are present in both result sets will be included in the result of the operation.
INTERSECT has higher precedence than UNION and EXCEPT (unless running , in which case all three have the same precedence). If possible it will be executed linearly, but if not, it will be translated to a subquery in the FROM clause:
will be translated to:
Parentheses can be used to specify precedence.
Parentheses cannot be used to specify precedence.
MariaDB starting with
INTERSECT ALL and INTERSECT DISTINCT . The ALL operator leaves duplicates intact, while the DISTINCT operator removes duplicates. DISTINCT is the default behavior if neither operator is supplied.
DISTINCT is the only behavior available.
Show customers which are employees:
Parentheses for specifying precedence:
This page is licensed: CC BY-SA / Gnu FDL
Restrict the number of rows returned. This clause specifies the maximum number of records to return and can optionally set an offset.
Use the LIMIT clause to restrict the number of returned rows. When you use a single integer n with LIMIT, the first n rows will be returned. Use the clause to control which rows come first. You can also select a number of rows after an offset using either of the following:
When you provide an offset m with a limit n, the first m rows will be ignored, and the following
SET STATEMENT max_statement_time=100 FOR
SELECT field1 FROM table_name ORDER BY field1;SELECT f1,f2 FROM t1 WHERE (f3<=10) AND (f4='y');SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]ERROR 1234 (42000): Incorrect usage/placement of 'HIGH_PRIORITY'(SELECT e_name AS name, email FROM employees)
UNION
(SELECT c_name AS name, email FROM customers);(SELECT name, email FROM employees)
UNION
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email)
UNION
(SELECT name, email FROM customers);SELECT CAST('x' AS CHAR(1)) UNION SELECT REPEAT('y',4);
+----------------------+
| CAST('x' AS CHAR(1)) |
+----------------------+
| x |
| yyyy |
+----------------------+(SELECT 1 AS sort_column, e_name AS name, email FROM employees)
UNION
(SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column;CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);
SELECT i FROM seqs WHERE i <= 3 UNION SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
SELECT i FROM seqs WHERE i <= 3 UNION ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
SELECT i FROM seqs WHERE i <= 3 EXCEPT SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
SELECT i FROM seqs WHERE i <= 3 EXCEPT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 2 |
+------+
SELECT i FROM seqs WHERE i <= 3 INTERSECT SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 3 |
+------+
SELECT i FROM seqs WHERE i <= 3 INTERSECT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 3 |
| 3 |
+------+CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TABLE t2 (b INT);
CREATE OR REPLACE TABLE t3 (c INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (5),(6);
INSERT INTO t3 VALUES (1),(6);
((SELECT a FROM t1) UNION (SELECT b FROM t2)) INTERSECT (SELECT c FROM t3);
+------+
| a |
+------+
| 1 |
| 6 |
+------+
(SELECT a FROM t1) UNION ((SELECT b FROM t2) INTERSECT (SELECT c FROM t3));
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+------+SELECT ...
(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT]) SELECT ...
[(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT]) SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}](SELECT a,b FROM t1)
UNION
(SELECT c,d FROM t2)
INTERSECT
(SELECT e,f FROM t3)
UNION
(SELECT 4,4);(SELECT a,b FROM t1)
UNION
SELECT c,d FROM
((SELECT c,d FROM t2)
INTERSECT
(SELECT e,f FROM t3)) dummy_subselect
UNION
(SELECT 4,4)(SELECT e_name AS name, email FROM employees)
INTERSECT
(SELECT c_name AS name, email FROM customers);CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);
SELECT i FROM seqs WHERE i <= 3 UNION SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
SELECT i FROM seqs WHERE i <= 3 UNION ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
SELECT i FROM seqs WHERE i <= 3 EXCEPT SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
SELECT i FROM seqs WHERE i <= 3 EXCEPT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 1 |
| 2 |
| 2 |
+------+
SELECT i FROM seqs WHERE i <= 3 INTERSECT SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 3 |
+------+
SELECT i FROM seqs WHERE i <= 3 INTERSECT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i |
+------+
| 3 |
| 3 |
+------+CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TABLE t2 (b INT);
CREATE OR REPLACE TABLE t3 (c INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (5),(6);
INSERT INTO t3 VALUES (1),(6);
((SELECT a FROM t1) UNION (SELECT b FROM t2)) INTERSECT (SELECT c FROM t3);
+------+
| a |
+------+
| 1 |
| 6 |
+------+
(SELECT a FROM t1) UNION ((SELECT b FROM t2) INTERSECT (SELECT c FROM t3));
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
+------+Executing an UPDATE with the LIMIT clause is not safe for replication. LIMIT 0 is an exception to this rule (see MDEV-6170).
There is a LIMIT ROWS EXAMINED optimization which provides the means to terminate the execution of SELECT statements which examine too many rows, and thus use too many resources. See LIMIT ROWS EXAMINED.
It is possible to use LIMIT with GROUP_CONCAT().
It is not possible to use LIMIT with GROUP_CONCAT().
Select the first two names (no ordering specified):
All the names in alphabetical order:
The first two names, ordered alphabetically:
The third name, ordered alphabetically (the first name would be offset zero, so the third is offset two):
From , LIMIT can be used in a multi-table update:
When using LIMIT with GROUP_CONCAT, you can simplify certain queries. Consider this table:
The following query works fine, but is rather complex:
It can be simplified to this:
OFFSET ... FETCH Like limit, but also support WITH TIES
This page is licensed: CC BY-SA / Gnu FDL
LIMIT offset, row_count
LIMIT row_count OFFSET offsetCREATE TABLE members (name VARCHAR(20));
INSERT INTO members VALUES('Jagdish'),('Kenny'),('Rokurou'),('Immaculada');
SELECT * FROM members;
+------------+
| name |
+------------+
| Jagdish |
| Kenny |
| Rokurou |
| Immaculada |
+------------+SELECT * FROM members LIMIT 2;
+---------+
| name |
+---------+
| Jagdish |
| Kenny |
+---------+SELECT * FROM members ORDER BY name;
+------------+
| name |
+------------+
| Immaculada |
| Jagdish |
| Kenny |
| Rokurou |
+------------+SELECT * FROM members ORDER BY name LIMIT 2;
+------------+
| name |
+------------+
| Immaculada |
| Jagdish |
+------------+SELECT * FROM members ORDER BY name LIMIT 2,1;
+-------+
| name |
+-------+
| Kenny |
+-------+CREATE TABLE warehouse (product_id INT, qty INT);
INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);
CREATE TABLE store (product_id INT, qty INT);
INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);
UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty = store.qty+2
WHERE (warehouse.product_id = store.product_id AND store.product_id >= 1)
ORDER BY store.product_id DESC LIMIT 2;
SELECT * FROM warehouse;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 98 |
| 4 | 98 |
+------------+------+
SELECT * FROM store;
+------------+------+
| product_id | qty |
+------------+------+
| 1 | 5 |
| 2 | 5 |
| 3 | 7 |
| 4 | 7 |
+------------+------+CREATE TABLE d (dd DATE, cc INT);
INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc)
ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3 |
+----------------------------------------------------------------------------+SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc)
ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3 |
+-------------------------------------------------------------+Understand when to use subqueries versus joins. This guide explains performance implications and how to rewrite subqueries as joins for efficiency.
A subquery can quite often, but not in all cases, be rewritten as a JOIN.
A subquery using IN can be rewritten with the DISTINCT keyword. Consider this query:
It can be rewritten like this:
NOT IN or NOT EXISTS queries can also be rewritten. For example, these two queries return the same result:
They can both be rewritten like this:
Subqueries that can be rewritten as a LEFT JOIN are sometimes more efficient.
There are some scenarios, though, which call for subqueries rather than joins:
When you want duplicates, but not false duplicates. Suppose Table_1 has three rows — {1,1,2}
— and Table_2 has two rows
— {1,2,2}. If you need to list the rows
in Table_1 which are also in Table_2, only this subquery-based SELECT
This SQL statement won't work:
because the result will be {1,1,2,2}
— and the duplication of 2 is an error. This SQL statement won't work either:
because the result will be {1,2} — and the removal of the duplicated 1 is an error too.
When the outermost statement is not a query. The SQL statement:
can't be expressed using a join unless some rare SQL3 features are used.
When the join is over an expression. The SQL statement:
is hard to express with a join. In fact, the only way we can think of is this SQL statement:
which still involves a parenthesized query, so nothing is gained from the transformation.
When you want to see the exception. For example, suppose the question is: Which books are longer than Das Kapital? These two queries are effectively almost the same:
The difference is between these two SQL statements is, if there are two editions of Das Kapital (with different page counts), then the self-join example will return the books which are longer than the shortest edition of Das Kapital. That might be the wrong answer, since the original question didn't ask for "... longer than ANY book named Das Kapital" (it seems to contain a false assumption that there's only one edition).
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table2);SELECT DISTINCT table1.* FROM table1, table2 WHERE table1.col1=table2.col1;112SELECT * FROM table1 WHERE col1 NOT IN (SELECT col1 FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT col1 FROM table2
WHERE table1.col1=table2.col1);SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;SELECT Table_1.column_1
FROM Table_1
WHERE Table_1.column_1 IN
(SELECT Table_2.column_1
FROM Table_2);SELECT Table_1.column_1
FROM Table_1,Table_2
WHERE Table_1.column_1 = Table_2.column_1;SELECT DISTINCT Table_1.column_1
FROM Table_1,Table_2
WHERE Table_1.column_1 = Table_2.column_1;UPDATE Table_1 SET column_1 = (SELECT column_1 FROM Table_2);SELECT * FROM Table_1
WHERE column_1 + 5 =
(SELECT MAX(column_1) FROM Table_2);SELECT Table_1.*
FROM Table_1,
(SELECT MAX(column_1) AS max_column_1 FROM Table_2) AS Table_2
WHERE Table_1.column_1 + 5 = Table_2.max_column_1;SELECT DISTINCT Bookcolumn_1.*
FROM Books AS Bookcolumn_1 JOIN Books AS Bookcolumn_2 USING(page_count)
WHERE title = 'Das Kapital';
SELECT DISTINCT Bookcolumn_1.*
FROM Books AS Bookcolumn_1
WHERE Bookcolumn_1.page_count >
(SELECT DISTINCT page_count
FROM Books AS Bookcolumn_2
WHERE title = 'Das Kapital');