MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
TABLE
is a DML statement introduced
in MySQL 8.0.19 which returns rows and columns of the named table.
TABLEtable_name
[ORDER BYcolumn_name
] [LIMITnumber
[OFFSETnumber
]]
The TABLE
statement in some ways
acts like SELECT
. Given the
existance of a table named t
, the following two
statements produce identical output:
TABLE t; SELECT * FROM t;
You can order and limit the number of rows produced by
TABLE
using ORDER
BY
and LIMIT
clauses, respectively.
These function identically to the same clauses when used with
SELECT
(including an optional
OFFSET
clause with LIMIT
),
as you can see here:
mysql>TABLE t;
+----+----+ | a | b | +----+----+ | 1 | 2 | | 6 | 7 | | 9 | 5 | | 10 | -4 | | 11 | -1 | | 13 | 3 | | 14 | 6 | +----+----+ 7 rows in set (0.00 sec) mysql>TABLE t ORDER BY b;
+----+----+ | a | b | +----+----+ | 10 | -4 | | 11 | -1 | | 1 | 2 | | 13 | 3 | | 9 | 5 | | 14 | 6 | | 6 | 7 | +----+----+ 7 rows in set (0.00 sec) mysql>TABLE t LIMIT 3;
+---+---+ | a | b | +---+---+ | 1 | 2 | | 6 | 7 | | 9 | 5 | +---+---+ 3 rows in set (0.00 sec) mysql>TABLE t ORDER BY b LIMIT 3;
+----+----+ | a | b | +----+----+ | 10 | -4 | | 11 | -1 | | 1 | 2 | +----+----+ 3 rows in set (0.00 sec) mysql>TABLE t ORDER BY b LIMIT 3 OFFSET 2;
+----+----+ | a | b | +----+----+ | 1 | 2 | | 13 | 3 | | 9 | 5 | +----+----+ 3 rows in set (0.00 sec)
TABLE
differs from
SELECT
in two key respects:
For limiting which table columns are returned, filtering rows
beyond what can be accomplished using ORDER BY
and LIMIT
, or both, use
SELECT
.
TABLE
can be used with temporary
tables.
TABLE
can also be used in place of
SELECT
in a number of other
constructs, including those listed here:
With UNION
, as shown here:
mysql>TABLE t1;
+---+----+ | a | b | +---+----+ | 2 | 10 | | 5 | 3 | | 7 | 8 | +---+----+ 3 rows in set (0.00 sec) mysql>TABLE t2;
+---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | | 6 | 7 | +---+---+ 3 rows in set (0.00 sec) mysql>TABLE t1 UNION TABLE t2;
+---+----+ | a | b | +---+----+ | 2 | 10 | | 5 | 3 | | 7 | 8 | | 1 | 2 | | 3 | 4 | | 6 | 7 | +---+----+ 6 rows in set (0.00 sec)
The UNION
just shown is
equivalent to the following statement:
mysql> SELECT * FROM t1 UNION SELECT * FROM t2;
+---+----+
| a | b |
+---+----+
| 2 | 10 |
| 5 | 3 |
| 7 | 8 |
| 1 | 2 |
| 3 | 4 |
| 6 | 7 |
+---+----+
6 rows in set (0.00 sec)
TABLE
can also be used together in unions
with SELECT
statements,
VALUES
statements, or both. See
Section 13.2.10.3, “UNION Clause”.
With INTO
to populate user variables, and
with INTO OUTFILE
or INTO
DUMPFILE
to write table data to a file. See
Section 13.2.10.1, “SELECT ... INTO Statement”, for more specific information
and examples.
In many cases where you can employ subqueries. Given any table
t1
with a column named
a
, and a second table t2
having a single column, statements such as the following are
possible:
SELECT * FROM t1 WHERE a IN (TABLE t2);
Assuming that the single column of table ts
is named x
, the preceding is equivalent to
each of the statements shown here (and produces exactly the
same result in either case):
SELECT * FROM t1 WHERE a IN (SELECT x FROM t2); SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
See Section 13.2.11, “Subqueries”, for more information.
With INSERT
and
REPLACE
statements, where you
would otherwise use
SELECT *
.
See Section 13.2.6.1, “INSERT ... SELECT Statement”, for more information and
examples.
TABLE
can also be used in many
cases in place of the SELECT
in
CREATE
TABLE ... SELECT
or
CREATE VIEW ...
SELECT
. See the descriptions of these statements for
more information and examples.