Databases Reference
In-Depth Information
An example of complex SELECT types
Here's a nonsense query that serves as a fairly compact example of some of the complex
SELECT
types:
1 EXPLAIN
2 SELECT actor_id,
3 (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id =
4 der_1.actor_id LIMIT 1)
5 FROM (
6 SELECT actor_id
7 FROM sakila.actor LIMIT 5
8 ) AS der_1
9 UNION ALL
10 SELECT film_id,
11 (SELECT @var1 FROM sakila.rental LIMIT 1)
12 FROM (
13 SELECT film_id,
14 (SELECT 1 FROM sakila.store LIMIT 1)
15 FROM sakila.film LIMIT 5
16 ) AS der_2;
The
LIMIT
clauses are just for convenience, in case you wish to execute the query
without
EXPLAIN
and see the results. Here is the result of the
EXPLAIN
:
+------+----------------------+------------+...
| id | select_type | table |...
+------+----------------------+------------+...
| 1 | PRIMARY | <derived3> |...
| 3 | DERIVED | actor |...
| 2 | DEPENDENT SUBQUERY | film_actor |...
| 4 | UNION | <derived6> |...
| 6 | DERIVED | film |...
| 7 | SUBQUERY | store |...
| 5 | UNCACHEABLE SUBQUERY | rental |...
| NULL | UNION RESULT | <union1,4> |...
+------+----------------------+------------+...
We've been careful to make each part of the query access a different table, so you can
see what goes where, but it's still hard to figure out! Taking it from the top:
• The first row is a forward reference to
der_1
, which the query has labeled as
<derived3>
. It comes from line 2 in the original SQL. To see which rows in the
output refer to
SELECT
statements that are part of
<derived3>
, look forward ...
• ...to the second row, whose
id
is
3
. It is
3
because it's part of the third
SELECT
in the
query, and it's listed as a
DERIVED
type because it's nested inside a subquery in the
FROM
clause. It comes from lines 6 and 7 in the original SQL.
• The third row's
id
is
2
. It comes from line 3 in the original SQL. Notice that it
comes after a row with a higher
id
number, suggesting that it is executed afterward,
which makes sense. It is listed as a
DEPENDENT SUBQUERY
, which means its results
depend on the results of an outer query (also known as a
correlated subquery
). The
