Writing Conditions with WHERE

1.1 The conditional expression

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

At each line of the table, the expression conditional is re-evaluated based on line-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 be not 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 line concerned.

The application of logical operators on the result individual conditions gives a single result for the entire expression conditional. AND requires both conditions to be TRUE for the result to be TRUE ; the OR only requests one TRUE condition for the result to be TRUE.

TRUE and TRUE gives TRUE
TRUE and FALSE gives FALSE
FALSE and FALSE gives FALSE
TRUE or TRUE gives TRUE
TRUE or FALSE gives TRUE
FALSE or FALSE gives FALSE

Remark

  • The conditional expression can consist of only one condition. There is no then no logical operator.
  • When multiple conditions are united by a mixture of ANDs and ORs, It is better to use parentheses to clarify what is required.
WHERE (condition 1 AND condition2) 
OR (condition3 AND condition4)

1.2 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 the table used
  • a text value (ex: â € ~alloâ € ™) or a number (ex: 3456)
  • a list of values, which necessitates 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: This element is presented at a later point in this document.

Remark

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

1.3 Operators

Operators that can be used in one condition are numerous. First, known comparison operators of mathematics are accepted.

  • = equal
  • < 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 intervals of values
  • LIKE for substrings of characters
  • IS for NULLs

1.3.1 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 values from the list.

Find employee-led projects whose number is 104, 108 or 111.
SELECT project_code
FROM project
WHERE emp_no IN (104, 108, 111);

1.3.2 The BETWEEN operator

Sometimes, one wants 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 terminals 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’;

Remark

  • BETWEEN considers that the limits are included in the interval.

1.3.3 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%’;

1.3.4 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.

IS column
[NOT] NULL

Indeed, the following condition can not be verified:

WHERE column
= NULL returns FALSE

you must use:

WHERE NULL IS
NULL column gives TRUE

Print Friendly, PDF & Email

Leave a Reply