SELECT
clauses define tabular data. They can be used either as standalone statements, of which SELECT
is the most common, or as subqueries in table expressions.
Overview
SQL is fundamentally about manipulating tabular data, and SELECT
clauses are the primary way to obtain tabular data for manipulation.
There are three specific syntax forms collectively named SELECT
clauses:
Form | Usage |
---|---|
VALUES |
List tabular data by the client. |
TABLE |
Load tabular data from the database. |
SELECT |
Load or compute tabular data from various sources. |
SELECT
clauses appear in every statement that takes tabular data as input, for example:
- As operand to
INSERT
,UPSERT
andCREATE TABLE AS
statements. - As sub-queries in table expressions.
Possible Combinations
Statement | Example using SELECT |
Example using VALUES |
Example using TABLE |
---|---|---|---|
INSERT |
INSERT INTO foo SELECT * FROM bar |
INSERT INTO foo VALUES (1), (2), (3) |
INSERT INTO foo TABLE bar |
UPSERT |
UPSERT INTO foo SELECT * FROM bar |
UPSERT INTO foo VALUES (1), (2), (3) |
UPSERT INTO foo TABLE bar |
CREATE TABLE AS |
CREATE TABLE foo AS SELECT * FROM bar |
CREATE TABLE foo AS VALUES (1),(2),(3) |
CREATE TABLE foo AS TABLE bar |
SELECT |
SELECT * FROM (SELECT * FROM bar) |
SELECT * FROM (VALUES (1),(2),(3)) |
SELECT * FROM (TABLE bar) |
Expression subquery | SELECT * FROM foo WHERE x IN (SELECT * FROM bar) |
SELECT * FROM foo WHERE x IN (VALUES (1),(2),(3)) |
SELECT * FROM foo WHERE x IN (TABLE bar) |
VALUES
Clause
Syntax
VALUES (<expr>, <expr>, ...), (<expr>, <expr>, ...), ...
A VALUES
clause defines tabular data defined by the expressions
listed within parentheses. Each parenthesis group defines a single row
in the resulting table.
The columns of the resulting table data have automatically generated
names. These names can be modified with
AS
when the
VALUES
clause is used as a sub-query.
Example
> VALUES (1, 2, 3), (4, 5, 6);
+---------+---------+---------+
| column1 | column2 | column3 |
+---------+---------+---------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+---------+---------+---------+
TABLE
Clause
Syntax
TABLE <tablename>
A TABLE
clause reads tabular data from a specified table. The
columns of the resulting table data are named after the schema of the
table.
In general, TABLE x
is equivalent to SELECT * FROM x
, but it is
shorter to type.
Example
> CREATE TABLE employee_copy AS TABLE employee;
This statement copies the content from table employee
into a new
table. However, note that the TABLE
clause does not preserve the indexing,
foreign key, or constraint and default information from the schema of the
table it reads from, so in this example, the new table employee_copy
will likely have a simpler schema than employee
.
Other examples:
> TABLE employee;
> INSERT INTO employee_copy TABLE employee;
SELECT
Clause
The SELECT
clause is documented separately as the stand-alone SELECT
statement, which is its most common use in SQL. However, it's important to note the broader application of SELECT
clauses, which can be used alongside the other two selection clause forms everywhere such a clause is admissible.