Learning Mysql

-> (SELECT * FROM album WHERE artist.artist_id = album.artist_id

Download 4.24 Mb.

View original pdf

Page	238/366
Date	04.08.2023
Size	4.24 Mb.
	#61806

1 ... 234 235 236 237 238 239 240 241 ... 366

Learning MySQL

260 | Chapter 7: Advanced Querying mysql> SELECT producer_name FROM producer WHERE producer_name
SELECT producer_name FROM producer WHERE EXISTS -> (SELECT * FROM engineer WHERE producer_name = engineer_name)
SELECT producer_name FROM producer WHERE producer_name

-> (SELECT * FROM album WHERE artist.artist_id = album.artist_id
-> GROUP BY artist.artist_id HAVING COUNT) >= 2);
+-------------+
| artist_name |
+-------------+
| New Order |
| Miles Davis |
+-------------+
2 rows inset sec)
This is yet another query where nesting isn’t necessary and a join would suffice, but let’s stick with this version for the purpose of explanation. Have a look at the inner query you can see that the
WHERE
clause ensures only album rows for the artist being referenced by the outer query—the current artist—are considered by the subquery. The
GROUP BY
clause clusters the rows for that artist, but only if there are at least two albums.
Therefore, the inner query only produces output when there are at least two albums for the current artist. The outer query is straightforward it outputs an artist’s name when the subquery produces output.
Here’s one more example before we move on and discuss other issues. We’ve already shown you a query that uses
IN
and finds producers who are also engineers:
260 | Chapter 7:
Advanced Querying

mysql> SELECT producer_name FROM producer WHERE producer_name
-> IN (SELECT engineer_name FROM engineer);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row inset 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 inset 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

Download 4.24 Mb.

Share with your friends:

1 ... 234 235 236 237 238 239 240 241 ... 366