## The conditional expression

SQL determines on which rows will be performed an operation by means of a conditional expression in the **WHERE **clause.

At each row of the table, the conditional expression is re-evaluated based on row-specific values.

If the expression evaluates to TRUE, then the requested operation (SELECT, DELETE, UPDATE) will be performed on the row.

If the expression evaluates to FALSE, then the row is rejected and the operation will not be performed.

The conditional expression is written as a list of conditions, separated by a logical operator.

There are two logical operators the AND (and) and the OR (or).

`condition`

{AND | OR} condition ...

A condition is a comparison expression that also evaluates to TRUE or FALSE according to the values of the row concerned.

Applying logical operators on the individual result of the conditions yields a single result for the entire conditional expression.

**AND**requires both conditions to be TRUE for the result to be TRUE.**OR**only requires only one TRUE condition for the result to be TRUE.

TRUE | and | TRUE | returns | TRUE |

TRUE | and | FALSE | returns | FALSE |

FALSE | and | FALSE | returns | FALSE |

TRUE | or | TRUE | returns | TRUE |

TRUE | or | FALSE | returns | TRUE |

FALSE | or | FALSE | returns | FALSE |

**NOTE**:

- The conditional expression can consist of only one condition. There is so no logical operator.
- When several conditions are united by a mixture of
**ANDs**and**ORs**, it It is best to use**parentheses**to clarify what is being asked.

`WHERE (condition 1 AND condition2)`

OR (condition3 AND condition4)

## The condition

An individual condition is written as follows:

`expression operator expression`

**An expression can be any of the following:**

a column | ex: The [Name] column of Employee table |

a value | ex: A text, like “Hello”, or a number like 3456 |

a list of values | which requires the use of parentheses and commas, ex. : (23, 45, 48, 234, 345) |

a function | the DBMS provide a set of functions that can be applied on columns, ex. : UPPER (name) |

a mathematical expression | quantity * cost / 34 |

a sub-query |

**Note**:

- The number of items included in a list of values is limited depending on DBMS used.
- The available functions vary depending on the DBMS.
- A mathematical expression that contains a NULL always gives NULL as a result.

## Operators

Numerous operators can be used in one condition.

There are comparison operators

- = equals
- < smaller than
- > bigger than
- <= smaller than or equal to
- > = greater than or equal to
- <> different from

In addition, some more specific operators are also used. Note that it is always possible to use a negative format (with **NOT**) for all operators of comparison.

- IN for sets of values
- BETWEEN for value intervals
- LIKE for substrings of characters
- IS for NULLs

## The IN operator

If you want to compare a column to a list of values, it is necessary to use the **IN **operator.

```
WHERE column
[NOT] IN (list of values)
```

This is used to compare the column with a list of values. If the column is equal to one of the values mentioned in the list, then the condition is respected.

Using the negation (NOT), the condition is considered respected if the column is not equal to any value of the list.

Find projects led by employees whose number is 104, 108 or 111.

```
SELECT project_code
FROM project
WHERE emp_no IN (104, 108, 111);
```

## The BETWEEN operator

Sometimes, we want to look for values located between the limits of an interval.

Two conditions united by **AND **are then necessary to establish the lower bound and the terminal upper range.

What projects started between January 1, 1989 and March 15, 1991?

`SELECT project_code`

FROM project

WHERE start_date> = '1989-01-01'

AND end_date <= '1991-03-15';

The **BETWEEN **operator can be used to set the bounds using a single condition rather than two.

`WHERE column`

[NOT] BETWEEN bound1 AND bound2

The same query as above can be written as:

`SELECT project_code`

FROM project

WHERE start_date BETWEEN ‘1989-01-01’ AND ‘1991-03-15’;

**Note**:**BETWEEN **considers that the limits are included in the interval.

## The LIKE Operator

To set a condition on the alphanumeric content of a column, we use the LIKE operator.

```
WHERE column
[NOT] LIKE ‘value’
```

In the ‘value’:

- the symbol ‘
**_**’ replaces exactly one character or space; - the symbol
**‘%’**replaces an arbitrary sequence of characters (including none).

What are the products whose class starts with the letter Z?

`SELECT *`

FROM product

WHERE class LIKE ‘Z%’;

## The IS operator

If we want to verify that a column contains, or not, the term **NULL**, we must obligatorily use the **IS **operator rather than **“=“. **Any comparison made with a **NULL **and involving a usual comparison operator gives **FALSE **as an evaluation.

`column IS [NOT] NULL`

Indeed, the following condition can not be verified:

WHERE column = NULL

**returns FALSE**

**you must use:**

WHERE column IS NULL

**returns TRUE**

**Move to the next article**: SQL Joins