WHERE Clause
SQL Statement Behavior
Learning Outcomes
- Learn how to compare two variables in a
WHEREclause. - Learn how to compare two variables when one or more holds a
NULLvalue. - Learn how to compare one variable against a range of variables with the
BETWEENoperator 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
WHEREclause. - 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
FROMclause. - 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.
Instruction Details →
Equality Comparison
The following compares a string variable to a string literal value:
WHERE string_variable = 'some string' |
The WHERE clause returns true when the value of the string_variable column is equal to the case sensitive 'some string' and false when it isn’t equal. For reference, string literal values are delimited by single quotes, and they are case sensitive strings.
The following compares a numeric_variable column to a numeric literal value:
WHERE numeric_variable = 1.5 |
The contents of a numeric variable are compared to other numeric values, whether they are in other variable. The numeric comparison cast the two numeric values to the same type before comparing their values.
The following compares a date variable to a string literal that conforms to one of the date formats:
WHERE date_variable = '29-FEB-17' |
The contents of a date variable is a scalar numeric value, which means it is a double precision number. The number to the left of the decimal point represents the day and the number to the right of the decimal point represents the hours, minutes, seconds, 10th of seconds, and 100th of seconds. Timestamps in databases are like dates but they also contain smaller increments of time, like 1,000th of a second.
Date-time comparisons are therefore like numeric comparisons.
Non-equality Comparison
The following compares a string variable to a string literal value:
WHERE string_variable > 'some string' |
While the contents of a string variable are compared as a case sensitive string in most relational databases and string literal values are delimited by single quotes, they are compared based on their ASCII values.
The ASCII value of characters differs between character sets, like English, French, Japanese, and Chinese differ. In a Western European character set, a capital “A” holds a value of 65 and a lowercase “a” holds a value of 97.
That means a comparison like this is true:
WHERE 'A' > 'a' |
and, a comparison like this is false:
WHERE 'a' > 'A' |
The following compares a numeric variable to a numeric literal value:
WHERE numeric_variable > 1.5 |
When the numeric_variable holds a value greater than the numeric literal value the comparison is true, and when the numeric_variable holds a value less than the numeric literal value the comparison is false.
The following compares a date variable to a string literal that conforms to one of the date formats:
WHERE date_variable > '29-FEB-17' |
When the date_variable holds a value greater than the date literal value the comparison is true, and when the date_variable holds a value less than the date literal value the comparison is false.
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.
Instruction Details →
Null Value Comparison
You have only one option when you want to compare a value and a null column value. You need to wrap the column value in an NVL() function call. The NVL() function replaces the column value with a default value, like the following:
WHERE NVL(string_variable,'default string') = 'some string' |
You can use reference comparison operators to filter rows when a column value is null or when a column value is not null. You use the IS NULL to return true when the column value holds a null value and the IS NOT NULL to return true when the column doesn't hold a null value.
The following compares a null column value:
WHERE string_variable IS NULL |
The following compares a not null column value:
WHERE string_variable IS NOT NULL |
Range Comparisons
Range value comparisons exist to let you find a value in an inclusive range of values.
Instruction Details →
Range Value Comparison
The rules work as follows:
- You provide a lookup value, which must be a not null value.
- You provide a lower range value, which must be a not null value.
- You provide an upper range value, which must be a not null value.
WHERE numeric_variable BETWEEN low_range_value AND high_range_value |
A range comparison works by checking whether the first value is found between an inclusive range of values. The BETWEEN operator takes the lower value first and the higher value second. The first value before the BETWEEN operator is sometimes called the needle, and two values after the BETWEEN operator are sometimes called the haystacks. The haystacks run from the smaller on the left to larger on the right haystack.
Range comparisons like value comparisons require that you work with real values as the needle and haystacks. If either the needle or one of the haystacks holds a null value, the BETWEEN operator returns a false value.
You can use the NVL function to check for a null value and replace a null value with a default value. For example, the next example looks for a value in a range of values but assigns 0 as the default value, 0 as the lower range value, and 10 as the higher range value.
WHERE NVL(needle,0) BETWEEN NVL(small_haystack,0) AND NVL(large_haystack,10) |
The range comparison finds that 0 is inside the inclusive range of 0 to 10 and marks it as
Logical AND, OR, and NOT Comparisons
Logical AND, OR, and NOT operators let you chain comparisons together and perform negation comparisons.
Instruction Details →
Combining Comparison with the AND Operator
The AND operator lets you chain comparisons of truth. You can check whether two or more things are true by connecting them with an AND operator.
The following comparison checks whether the numeric_column value is equal to 25 and the string_column is equal to the case sensitive 'Something' string. Both comparisons must be true for the the WHERE clause to be true.
WHERE numeric_column = 25 AND string_column = 'Something' |
Combining Comparison with the OR Operator
The OR operator also lets you chain comparisons of truth. You can check whether one or more things among many things are true by connecting them with an OR operator.
The following comparison checks whether the numeric_column value is equal to 25 or the string_column column is equal to the case sensitive 'Something' string.
Only one of the comparisons must be true for the the WHERE clause to be true because the OR operator evaluates looking for only one comparison that is true. That's possible because the WHERE clauses evaluates one or the other.
A WHERE clause with a string of comparisons that you connect with OR operators is true when at least one of the comparisons is true. SQL stops the evaluation when one comparison is true because it uses a short-circuit logic. The statement below is true when either the numeric_column value is 25 or the string_column value is a case sensitive 'Something' string.
WHERE numeric_column = 25 OR string_column = 'Something' |
Combining Comparison with the NOT Operator
The NOT operator lets find a true comparison statement to be false, and a false comparison statement to be true. In its simplest format you take the opposite of the comparison.
WHERE NOT numeric_column = 25 |
For example, the NOT operator converts a true comparison when the value of the numeric_column column is 25 to false. When the WHERE clause returns false no rows are returned.
You need to use parentheses to ensure that two or more comparisons that are individually found be true can be collectively negated as false. The parentheses are required to guarantee the order of operation, which ensures all comparisons within them are true before negating the result. Likewise, the negation operator returns true when one of the comparisons returns a false value.
The following shows you how to perform a compound comparison where one of the comparisons is false, which the NOT operator negates to be true.
WHERE NOT (25 = 25 AND 'Nothing' = 'Something') |
The preceding WHERE clause returns a false comparison but if you change the individual comparisons inside the parentheses so that one is false, the whole WHERE clause becomes true.
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.
Instruction Details →
The idea of matching the value of a column or set of columns from one row against a list of potential values is accomplished with a lookup behavior. You have the IN, =ANY, =SOME, =ALL operators to perform lookup behaviors. The IN, =ANY, =SOME operators act like a sequence of OR operators; whereas the =ALL operator acts like a sequence of AND operators. The examples show how to match them against hard coded lists of values but you could just as easily substitute subqueries for the lists of values.
