SQL Conditions with WHERE

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