# InterBase Quick Start: Part IV - Search Conditions

The text following the `WHERE` keyword is called a search condition, because a `SELECT` statement searches for rows that meet the condition. Search conditions consist of a column name, an operator, and a value. The `WHERE` clause has the following general form:

```WHERE column_name operator value
```
• `column_name` is the column name in the table that you query from.
• `operator` is a comparison operator.
• `value` is a value or a range of values to compare against the column.

## Comparison Operators

Search conditions use the following operators:

Operator Description Examples

Comparison operators

Compare data in a column to a value in the search condition.

`<`, `>`, `{{{1}}}`, `{{{1}}}`, `{{{1}}}`, `{{{1}}}`, `<>`, `BETWEEN`, `CONTAINING`, `IN`, `IS NULL`, `LIKE`, and `STARTING WITH`.

Arithmetic operators

Calculate and evaluate search condition values.

`+`, `-`, `*`, and `/`.

Logical operators

Combine search conditions or to negate a condition.

`NOT`, `AND`, and `OR`.

## Search Condition Values

You can use literal or calcualted (derived) values in a search condition. Additionally, the value can be the return value of a subquery. A subquery is a nested `SELECT` statement. You must specify values that are text literals in single quotes.

Type of Values Description Examples

Literal values

Numbers and text strings whose value you want to test literally.

The number `1138` or the string `'Smith'`.

Note: String comparisons are case sensitive.

Derived values

Functions and arithmetic expressions.

`salary * 2` or `last_name`.

Subqueries

A nested `SELECT` statement that returns one or more values.

The result represents the search condition.

When you compare a row to a search condition, the result is one of three values:

• `True`: A row meets the conditions specified in the `WHERE` clause.
• `False`: A row does not meet the conditions specified in the `WHERE` clause.
• `Unknown`: A field in the `WHERE` clause contains an `NULL` state that could not be evaluated.

## Using a Complex WHERE Clause

Enter the following statement to query the `Sales` table for all the customers who made an order before January 1, 1994, received the order, and have not paid yet. There are three search conditions in the `WHERE` clause, joined together with the `AND` operator.

```SELECT *
FROM   Sales
WHERE  order_date < '1-JAN-1994'
AND order_status = 'shipped'
AND paid = 'n'
```

The result returns two rows:

### Using Negation

You can negate any expression with negation operators: `!`, `^`, and `~`. These operators are all synonyms for `NOT`. for example, if you want to find customers that are not from the United States you can do that using the following `SELECT` statement:

```SELECT customer,
country
FROM   Customer
WHERE  NOT country = 'USA'
```

The result shows a list of 10 customers:

There are other ways to achieve this exact result. To confirm that, enter each of the queries below and compare the results:

```SELECT customer,
country
FROM   Customer
WHERE  country != 'USA'

SELECT customer,
country
FROM   Customer
WHERE  country ~= 'USA'

SELECT customer,
country
FROM   Customer
WHERE  country ^= 'USA'
```