Databases Reference
In-Depth Information
The id Column
This column always contains a number, which identifies the
SELECT
to which the row
belongs. If there are no subqueries or unions in the statement, there is only one
SELECT
, so every row will show a
1
in this column. Otherwise, the inner
SELECT
state-
ments generally will be numbered sequentially, according to their positions in the orig-
inal statement.
MySQL divides
SELECT
queries into simple and complex types, and the complex types
can be grouped into three broad classes: simple subqueries, so-called derived tables
(subqueries in the
FROM
clause),
3
and
UNION
s. Here's a simple subquery:
mysql>
EXPLAIN SELECT (SELECT 1 FROM sakila.actor LIMIT 1) FROM sakila.film;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
| 1 | PRIMARY | film |...
| 2 | SUBQUERY | actor |...
+----+-------------+-------+...
Subqueries in the
FROM
clause and
UNION
s add more complexity to the
id
column. Here's
a basic subquery in the
FROM
clause:
mysql>
EXPLAIN SELECT film_id FROM (SELECT film_id FROM sakila.film) AS der;
+----+-------------+------------+...
| id | select_type | table |...
+----+-------------+------------+...
| 1 | PRIMARY | <derived2> |...
| 2 | DERIVED | film |...
+----+-------------+------------+...
As you know, this query is executed with an anonymous temporary table. MySQL
internally refers to the temporary table by its alias (
der
) within the outer query, which
you can see in the
ref
column in more complicated queries.
Finally, here's a
UNION
query:
mysql>
EXPLAIN SELECT 1 UNION ALL SELECT 1;
+------+--------------+------------+...
| id | select_type | table |...
+------+--------------+------------+...
| 1 | PRIMARY | NULL |...
| 2 | UNION | NULL |...
| NULL | UNION RESULT | <union1,2> |...
+------+--------------+------------+...
Note the extra row in the output for the result of the
UNION
.
UNION
results are always
placed into an anonymous temporary table, and MySQL then reads the results back
out of the temporary table. The temporary table doesn't appear in the original SQL, so
its
id
column is
NULL
. In contrast to the preceding example (illustrating a subquery in
3. The statement “a subquery in the
FROM
clause is a derived table” is true, but “a derived table is a subquery
in the
FROM
clause” is false. The term “derived table” has a broader meaning in SQL.
