TABLE
is a DML statement introduced
in MySQL 8.0.19 which returns rows and columns of the named table.
TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]
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 withSELECT
statements,VALUES
statements, or both. See Section 13.2.10.3, “UNION Clause”.With
INTO
to populate user variables, and withINTO OUTFILE
orINTO 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 nameda
, and a second tablet2
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 namedx
, 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
andREPLACE
statements, where you would otherwise useSELECT *
. 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 theSELECT
inCREATE TABLE ... SELECT
orCREATE VIEW ... SELECT
. See the descriptions of these statements for more information and examples.