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

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 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 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 where photo_id='2186714153'

For this SELECT statement, the YQL Web Service calls the Flickr Web Service, passing photo_id as follows:'2186714153'

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 results of DESC 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 where photo_id='2186714153' or photo_id='3502889956'

The SELECT statements for some tables require multiple remote filters, for example:

select * from 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 table, then applies the local filter title='moon'.

select * from 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 where title like 'Chinese%'

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

select * from where title matches '.*blue.*'

The following statement returns recent photos with the IDs specified in the parentheses:

select * from 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 where query="sushi" and location="san francisco, ca" and Rating.AverageRating="4.5"

Table of Contents