Databases Reference
In-Depth Information
mysql>
SELECT producer_name FROM producer WHERE producer_name
-> IN (SELECT engineer_name FROM engineer);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row in set (0.06 sec)
Let's rewrite the query to use
EXISTS
. First, think about the subquery: it should produce
output when there's an engineer with the same name as a producer.
Second, think about the outer query: it should return the producer's name when the
inner query produces output. Here's the rewritten query:
mysql>
SELECT producer_name FROM producer WHERE EXISTS
-> (SELECT * FROM engineer WHERE producer_name = engineer_name)
;
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row in set (0.06 sec)
Again, you can see that the subquery references the
producer_name
column, which
comes from the outer query.
Correlated subqueries can be used with any nested query type. Here's the previous
IN
query rewritten with an outer reference:
mysql>
SELECT producer_name FROM producer WHERE producer_name
-> IN (SELECT engineer_name FROM engineer WHERE
-> engineer_name = producer_name);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row in set (0.14 sec)
The query is more convoluted than it needs to be, but it illustrates the idea. You can
see that the
producer_name
in the subquery references the
producer
table from the outer
query. This query can also be rewritten to use an equals instead of
IN
:
mysql>
SELECT producer_name FROM producer WHERE producer_name
-> = (SELECT engineer_name FROM engineer WHERE
-> engineer_name = producer_name);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row in set (0.01 sec)
