WHERE clause

reference

The WHERE clause filters resultsets based specified conditions.

Purpose

When you want to narrow down your resultset by one or more criteria, use the WHERE clause to filter your resultset.

For example, list airports in France, detailed in Example 1:

SELECT airportname, city, country
FROM `travel-sample`
WHERE type = "airport"
AND country = "France"
LIMIT 4;

Syntax

WHERE cond [ bool_type cond2 ]*

Arguments

cond

[Required] String expression that represents a filter to be applied to the resultset.

bool_type

AND: String expression of an inclusive filter to be applied to the resultset.
OR: String expression of a union filter to be applied to the resultset.
NOT: String expression of an exclusive filter to be applied to the resultset.

Examples

Example 1: Use WHERE and AND to filter the resultset.

To list only airports that are in France, use the WHERE clause for the "type" field and the AND clause for the "country' field.

SELECT airportname, city, country
FROM `travel-sample`
WHERE type = "airport"
AND country = "France"
LIMIT 4;

Results:

[
  {
    "airportname": "Calais Dunkerque",
    "city": "Calais",
    "country": "France"
  },
  {
    "airportname": "Peronne St Quentin",
    "city": "Peronne",
    "country": "France"
  },
  {
    "airportname": "Les Loges",
    "city": "Nangis",
    "country": "France"
  },
  {
    "airportname": "Couterne",
    "city": "Bagnole-de-l'orne",
    "country": "France"
  }
]

Example 2: Use WHERE and OR to filter the resultset.

List only the landmarks that start with the letter "C" or "K". Note that the first position of the SUBSTR function is 0.

SELECT name
FROM `travel-sample`
WHERE type = "landmark"
AND ( CONTAINS(SUBSTR(name,0,1),"C")
   OR CONTAINS(SUBSTR(name,0,1),"K") )
LIMIT 4;

Results:

[
  {
    "name": "City Chambers"
  },
  {
    "name": "Kingston Bridge"
  },
  {
    "name": "Clyde Arc"
  },
  {
    "name": "Clyde Auditorium"
  }
]

Example 3: Use WHERE and NOT to filter the resultset.

List landmark restaurants, except Thai restaurants.

SELECT name, activity
FROM `travel-sample`
WHERE type = "landmark"
AND activity = "eat"
AND NOT CONTAINS(name,"Thai")
LIMIT 4;

Results:

[
  {
    "activity": "eat",
    "name": "Hollywood Bowl"
  },
  {
    "activity": "eat",
    "name": "Spice Court"
  },
  {
    "activity": "eat",
    "name": "Beijing Inn"
  },
  {
    "activity": "eat",
    "name": "Ossie's Fish and Chips"
  }
]