A subquery is a SQL statement that
is embedded within the
of another statement. For instance, Example 21-1 uses a subquery to
determine the number of customers who are also employees.
SELECT COUNT(*) FROM customers WHERE (contact_surname, contact_firstname,date_of_birth) IN (select surname,firstname,date_of_birth FROM employees)
We can identify the subquery through the
DEPENDENT SUBQUERY tag in the
Select type column of the
EXPLAIN statement output, as shown
Explain plan ------------ ID=1 Table=customers Select type=PRIMARY Access type=ALL Rows=100459 Key= (Possible= ) Ref= Extra=Using where ID=2 Table=employees Select type=
DEPENDENT SUBQUERYAccess type=ALL Rows=1889 Key= (Possible= ) Ref= Extra=Using where
The same query can also be rewritten as an
EXISTS subquery, as in Example 21-2.
SELECT count(*) FROM customers WHERE EXISTS (SELECT 'anything' FROM employees where surname=customers.contact_surname AND firstname=customers.contact_firstname AND date_of_birth=customers.date_of_birth) Short Explain ------------- 1 PRIMARY select(ALL) on customers using no key Using where 2
DEPENDENT SUBQUERYselect(ALL) on employees using no key
Note that the
EXISTS subquery is
identical to that of the
subquery. This is because MySQL rewrites
IN-based subqueries as
EXISTS-based syntax before execution. The performance of subqueries will, ...