This week, we're going to briefly hit the Pause button from the Some Top SQL Query Mistakes series in order to talk about Predicates in SQL. The reason is that Predicates will factor into Part 3 of the Top SQL Query Mistakes series.
What Is a Predicate?
A predicate is simply an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are typically employed in the search condition of WHERE and HAVING clauses, the join conditions of FROM clauses, as well as any other part of a query where a boolean value is required.
There are numerous types of predicates, including:
- Comparison
- LIKE
- BETWEEN
- IN
- EXISTS
- IS NULL (/INTEGER/DECIMAL/FLOAT...)
In the remainder of this article, we'll examine a few examples of the above Predicate types.
Comparison Predicates
Any time that we use a comparison operator in an expression, such as WHERE employee_salary > 100000, we are constructing a Predicate that evaluates to TRUE, FALSE, or UNKNOWN. Comparison operators include:
- = Equal to
- > Greater than
- < Less than
- >= Greater than or equal to
- <= Less than or equal to
- <> Not equal to
Hence, a comparison predicate takes the form of:
expression_1 comparison_operator expression_2
In a comparison predicate, expression2 can also be a subquery. If the subquery does not return any rows, the comparison predicate evaluates to FALSE.
LIKE Predicate
IN SQL, the number one pattern-matching predicate is the LIKE operator, as it compares column values with a specified pattern. Like works with any character or date data type. Here's an example:
BETWEEN Predicate
The BETWEEN operator specifies a range, which determines the lower and upper bounds of qualifying values. For instance, in the Predicate income BETWEEN 5000 AND 20000 the selected data is a range of greater then or equal to 5000 and less then or equal to 20000. The Between operator can be used with numeric, text and date data types. Here's an example:
IN Predicate
An IN operator allows the specification of two or more expressions to be used for a query search. The result of the condition is TRUE if the value of the corresponding column equals one of the expressions specified by the IN predicate:
EXISTS Predicate
The EXISTS Predicate accepts a subquery as an argument. It returns TRUE if the subquery returns one or more rows, and returns FALSE if it returns zero rows.
Here's an example:
IS NULL Predicate
Use IS NULL to determine whether an expression is null, because you cannot test for null by using the = comparison operator. When applied to row value expressions, all elements must test the same.
The IS NULL predicate takes the following form:
IS [NOT] NULL
For example, the expression x IS NULL is TRUE if x is a null.
IS UNKNOWN is a synonym for IS NULL when the expression is of the BOOLEAN type.
Here's a query that uses the IS NOT NULL Predicate to fetch all actors whose last name is a non-NULL value:
Conclusion
In this blog, we interrupted the regularly scheduled blog to bring you this important lesson on SQL Predicates. Typically employed in the search condition of WHERE and HAVING clauses, the join conditions of FROM clauses, Predicates are expressions that evaluate to TRUE, FALSE, or UNKNOWN. We'll be seeing Predicates again in next weeks continuation of the Top SQL Query Mistakes series.
