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

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'

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

select name, street, borough, phone
  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 id, name from players
  where team_id = (select id from teams where name = 'Buffaloes');

select sum (population) from towns
  where name like '%dam'

select password from usertable

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:

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.

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

  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.