However, it’s legal when executed as a subquery because tables listed in the outer query are allowed to be accessed in the subquery. So, in this example, the current value of artist_name in the outer query is supplied
to the subquery as a constant, scalar value and compared to the album name. If the album name matches the artist name,
the subquery is true, and so the outer query outputs a row. Consider two cases that illustrate this more clearly When the artist_name being processed by the outer query is New Order, the sub- query is false because SELECT * FROM album WHERE album_name = "New Order"
doesn’t
return any rows, and so the artist row for New Order isn’t output as an answer When the artist_name being processed by the outer query is The Beatles, the sub- query is true because SELECT * FROM album WHERE album_name = "The Beatles"
returns at least one row. Overall, the artist row for The Beatles is output as an answer.
Can you seethe power of correlated subqueries? You can use values from the outer query in the inner query to evaluate complex information needs.
We’ll now explore another example using EXISTS. Let’s try to find all artists from whom we own at least two albums.
To do this with EXISTS, we need to think through what the inner and outer queries should do. The inner query should produce a result only when the condition we’re checking is true in this case, it should produce output when the artist has at least two albums in the database. The outer query should produce the artist name whenever the inner query is true. Here’s the query:
mysql>
SELECT artist_name FROM artist WHERE EXISTSShare with your friends: 
