6.1.5. The WHERE clause

The WHERE clause serves to limit the rows returned to the ones that the caller is interested in. The condition following the keyword WHERE can be as simple as a check like “AMOUNT = 3” or it can be a multilayered, convoluted expression containing subselects, predicates, function calls, mathematical and logical operators, context variables and more.

The condition in the WHERE clause is often called the search condition, the search expression or simply the search.

In DSQL and ESQL, the search expression may contain parameters. This is useful if a query has to be repeated a number of times with different input values. In the SQL string as it is passed to the server, question marks are used as placeholders for the parameters. They are called positional parameters because they can only be told apart by their position in the string. Connectivity libraries often support named parameters of the form :id, :amount, :a etc. These are more user-friendly; the library takes care of translating the named parameters to positional parameters before passing the statement to the server.

The search condition may also contain local (PSQL) or host (ESQL) variable names, preceded by a colon.

Syntax

SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]
<search-condition> ::=
  a boolean expression returning
  TRUE, FALSE or possibly UNKNOWN (NULL)

Only those rows for which the search condition evaluates to TRUE are included in the result set. Be careful with possible NULL outcomes: if you negate a NULL expression with NOT, the result will still be NULL and the row will not pass. This is demonstrated in one of the examples below.

Examples

select genus, species from mammals
  where family = 'Felidae'
  order by genus;

select * from persons
  where birthyear in (1880, 1881)
     or birthyear between 1891 and 1898;

select name, street, borough, phone
  from schools s
  where exists (select * from pupils p where p.school = s.id)
  order by borough, street;

select * from employees
  where salary >= 10000 and position <> 'Manager';

select name from wrestlers
  where region = 'Europe'
    and weight > all (select weight from shot_putters
                      where region = 'Africa');

select id, name from players
  where team_id = (select id from teams where name = 'Buffaloes');

select sum (population) from towns
  where name like '%dam'
  and province containing 'land';

select password from usertable
  where username = current_user;

The following example shows what can happen if the search condition evaluates to NULL.

Suppose you have a table listing some children’s names and the number of marbles they possess. At a certain moment, the table contains these data:

First, please notice the difference between NULL and 0: Fritz is known to have no marbles at all, Chris’s and Hadassah’s marble counts are unknown.

Now, if you issue this SQL statement:

select list(child) from marbletable where marbles > 10;

you will get the names Anita, Bob E., Eve and Gerry. These children all have more than 10 marbles.

If you negate the expression:

select list(child) from marbletable where not marbles > 10

it’s the turn of Deirdre, Fritz and Isaac to fill the list. Chris and Hadassah are not included, because they aren’t known to have ten marbles or less. Should you change that last query to:

select list(child) from marbletable where marbles <= 10;

the result will still be the same, because the expression NULL <= 10 yields UNKNOWN. This is not the same as TRUE, so Chris and Hadassah are not listed. If you want them listed with the “poor” children, change the query to:

select list(child) from marbletable
where marbles <= 10 or marbles is null;

Now the search condition becomes true for Chris and Hadassah, because “marbles is null” obviously returns TRUE in their case. In fact, the search condition cannot be NULL for anybody now.

Lastly, two examples of SELECT queries with parameters in the search. It depends on the application how you should define query parameters and even if it is possible at all. Notice that queries like these cannot be executed immediately: they have to be prepared first. Once a parameterized query has been prepared, the user (or calling code) can supply values for the parameters and have it executed many times, entering new values before every call. How the values are entered and the execution started is up to the application. In a GUI environment, the user typically types the parameter values in one or more text boxes and then clicks an “Execute”, “Run” or “Refresh” button.

select name, address, phone frome stores
  where city = ? and class = ?;

select * from pants
  where model = :model and size = :size and color = :col;

The last query cannot be passed directly to the engine; the application must convert it to the other format first, mapping named parameters to positional parameters.