Joining Tables With Sub-selects

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.profile and 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 information.

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 and geo.places tables are joined on two keys. The inner select returns two data fields (centroid.latitude and centroid.latitude) which are compared with the two input keys (latitude and longitude) of the outer select.

select * from 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="" | 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 where search_text in (SELECT FROM where album in (select title from where query = "Together" limit 5) limit 5)

However, the next statement is illegal because it has two IN keywords in a select:

ILLEGAL: select * from where lat in (select centroid.latitude from geo.places where text="sfo") and lon in (select centroid.longitude from geo.places where text="sfo")

Table of Contents