The filter in the WHERE clause determines which rows are returned by the SELECT statement.
The filter in the following statement, for example, returns rows only if the
text field matches the string
select * from geo.places where text=’sunnyvale’
Filters can also be used to return rows if a field is contained in a list of values. The filter in this statement returns
rows if the the
location field matches "San Francisco" or "San Jose".
select * from upcoming.events where location in ("San Francisco", "San Jose")
With a remote filter, the filtering takes place in the back-end datasource (usually a Web service) called by the YQL Web Service.
A remote filter has the following syntaxes:
input_key IN [list of one or more literals]
The input key is a parameter that YQL passes to the back-end datasource. The literal is a value, either a string, integer, or float. The equality (=) operator and the IN operator are allowed in a remote filter. (A local filter, in contrast, can contain other types of comparison operators.)
For example, in the following statement, the input key is
select * from flickr.photos.info where photo_id='2186714153'
For this SELECT statement, the YQL Web Service calls the Flickr Web Service, passing
photo_id as follows:
Using the IN operator, the following statement returns the geo data for Sunnyvale, Mountain View, and Cupertino.
select * from geo.places where text IN ("Sunnyvale", "Mountain View", "Cupertino")
Most YQL tables require the SELECT statement to specify a remote filter, which requires
an input key. Often, the input key is not one of the fields included in the results returned
by a SELECT. To see which input keys are allowed or required, enter the DESC statement for the
YQL table and note the
key XML element of the results. For example, the
DESC flickr.photos.info show that the input key
photo_id is required:
Multiple remote filters can be combined with the boolean AND or OR operators, for example:
select * from flickr.photos.info where photo_id='2186714153' or photo_id='3502889956'
The SELECT statements for some tables require multiple remote filters, for example:
select * from local.search where zip='94085' and query='pizza'
The YQL Web Service performs local filtering on the data it retrieves from the back-end datasource. Before examining the syntax of local filters, let's look at a few examples.
In the following example, YQL gets data from the
flickr.photos.interestingness table, then applies the local filter
select * from flickr.photos.interestingness where title='moon'
In the next statement, the local filter checks that the value of the
title field starts with the string
select * from flickr.photos.interestingness where title like 'Chinese%'
The filter in the following statement contains a regular expression that checks for the
select * from flickr.photos.interestingness where title matches '.*blue.*'
The following statement returns recent photos with the IDs specified in the parentheses:
select * from flickr.photos.recent where id in ('3630791520', '3630791510', '3630791496')
A local filter has the following syntax:
field comparison_operator literal
field (column) specifies the name of the XML element or JSON
object in the results. To specify a sub-field, separate the containing fields with periods.
For an example sub-field, see
Rating.AverageRating in the SELECT statement
in Combining Boolean Operations. The
is either a quoted string, an integer, or a float. The following table lists the allowed
|>=||Greater than or equal to.|
|<=||Less than or equal to.|
|[NOT] IN||Tests whether a value is contained in a set of values. This operator can be followed by either a sub-select or by a comma-delimited set of values within parentheses.|
|IS [NOT] NULL||Tests for the existence of the field in the results. An IS NULL expression is true if the field is not in the results.|
|[NOT] LIKE||Tests for a string pattern match. The comparison is case-insensitive. The "%" character in the literal indicates zero or more
characters. For example,
|[NOT] MATCHES||Tests for a string pattern match, allowing regular expressions. The comparison is case sensitive.|
Local and remote filter expressions can be combined with the boolean AND and OR operators. The AND operator has precedence over the OR operator. To change precedence, enclose expressions in parentheses.
In the following example, the first two filters are remote expressions because
location are input keys. The third filter,
containing the field
Rating.AverageRating, is a local filter.
select * from local.search where query="sushi" and location="san francisco, ca" and Rating.AverageRating="4.5"