MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

3 – WHERE Clause

without comments

SQL Statement Behavior

Learning Outcomes

  • Learn how to compare two variables in a WHERE clause.
  • Learn how to compare two variables when one or more holds a NULL value.
  • Learn how to compare one variable against a range of variables with the BETWEEN operator when all are variables hold non-null values or one or more holds a null value.
  • Learn how to use more than one comparison inside a WHERE clause.
  • Learn how to use lookup values to find a value in a list of values.

Lesson Materials

Statements and queries have two phases and they differ. While statements and queries have two phases, they differ. A statement selects tables and filters rows by the WHERE clause, and then a statement acts on a row or set of rows.

The two phases of a query are selection and projection. Selection has two components like statements, and they are:

  • The first component identifies the sources or tables in the FROM clause.
  • The second component filters the rows returned by comparing values and evaluating whether the comparison is true or false.

WHERE Clause Selection

The WHERE clause of statements and queries filter rows based on several rules. The following qualifies the rules for comparative operations:

Two-value Comparisons

Two value comparisons work by comparing two values or variables of the same data type. Comparison operations may check to see if they are equal, less than, greater than, on in a set of values.

Three-value Comparisons

Three-value comparisons are required in database comparisons because you can define any column to accept null values. You can’t use an =, >, >=, <, <=, or <> operator to compare a value with a null value.

Range Comparisons

Range value comparisons exist to let you find a value in an inclusive range of values.

Logical AND, OR, and NOT Comparisons

Logical AND, OR, and NOT operators let you chain comparisons together and perform negation comparisons.

Lookup Comparisons

Lookup comparisons allow you to look for a value in a set of values. They can look for the occurrence of one in a set or the occurrence of one as all of the set members.

Written by maclochlainn

December 12th, 2017 at 12:42 am