Filtering Query Results (WHERE)

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 Barcelona:

select * from flickr.photos.search where text='Barcelona'

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")

YQL has two types of filters: remote and local. These terms refer to where the filtering takes place relative to the YQL Web Service.

Remote Filters

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=literal

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 photo_id:

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:

http://api.flickr.com/services/rest/?method=flickr.photos.getInfo&photo_id='2186714153'

Using the IN operator, the following statement returns the latest percentage change of the stock prices for Yahoo!, Oracle, and Cisco.

select * from finance.scrape.trend where name IN ("Yahoo!", "Oracle", "Cisco")

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 results of 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'

Local Filters

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 title='moon'.

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 Chinese or CHINESE.

select * from flickr.photos.interestingness where title like 'Chinese%'

The filter in the following statement contains a regular expression that checks for the substring blue:

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

The 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 literal is either a quoted string, an integer, or a float. The following table lists the allowed comparison operators.

Operator Description
= Equal.
!= Not equal.
> Greater than.
< Less than.
>= 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, Sys% matches any string starting with Sys.
[NOT] MATCHES Tests for a string pattern match, allowing regular expressions. The comparison is case sensitive.

Combining Filter Expressions (AND, OR)

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 query and 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"

Table of Contents