With sub-selects, you can join data across different YQL tables. (In SQL, a sub-select is usually called a "subquery.") Because YQL tables are often backed by Web services, sub-selects enable you to join data from different Web services. In a join, the sub-select provides input for the IN operator of the outer select. The values in the outer select can be either input keys (remote filters) or fields in the response (local filters).
By using a sub-select, the following statement returns the profiles of all of the
connections (friends) of the user currently logged in to Yahoo. This statement joins the
social.connection tables on the values
of the GUIDs. The inner SELECT, which follows the word IN, returns the
GUIDs for the user's connections. For each of these GUIDs, the outer SELECT returns the profile
select * from social.profile where guid in (select guid from social.connections where owner_guid=me)
Tables can be joined on multiple keys. In the following example, the
geo.places tables are joined on two keys.
The inner select returns two data fields (
centroid.latitude) which are compared with the two input keys
longitude) of the outer select.
select * from local.search where (latitude,longitude) in (select centroid.latitude, centroid.longitude from geo.places where
text="north beach, san francisco") and radius=1 and query="pizza" and location=""
The next example shows an inner select that returns data from an RSS feed:
select * from search.web where query in (select title from rss where url="http://rss.news.yahoo.com/rss/topstories" | truncate(count=1))
One sub-select is allowed in each select. In other words, each select statement can only have one IN keyword, but the inner select may also have an IN keyword. The following statement is legal:
select name, description, ticket_url, url, venue_address, venue_state_code from upcoming.events where search_text in (SELECT
artist.name FROM spotify.search.album where album in (select title from youtube.search where query = "Together" limit 5) limit
However, the next statement is illegal because it has two IN keywords in a select:
ILLEGAL: select * from flickr.photos.search where lat in (select centroid.latitude from geo.places where text="sfo") and lon
in (select centroid.longitude from geo.places where text="sfo")