Predicates - Firebird

A predicate is simply an expression that asserts a fact about a value. SQL statements generally test predicates in WHERE clauses and CASE expressions. ON is the test for JOIN predicates; HAVING tests attributes in grouped output. In PSQL, flow-of-control statements test predicates in IF, WHILE, and WHEN clauses. Decisions are made according to whether the predicates are evaluated as true or false.

Strictly speaking, a predicate can be true, false, or not proven. For SQL purposes, false and not proven results are rolled together and treated as if all were false. In effect, “If it is not true, then it is false.”

The standard SQL language has formal specifications for a number of expression operators that are recognized as necessary for constructing search predicates. A predicate consists of three basic elements: two comparable values and an operator that predicates the assertion to be tested on the pair of values.

All of the operators included in Table (which appears later in the chapter) can be predicate operators.The values involved with the predicate can be simple or they can be extremely complex, nested expressions. As long as it is possible for the expressions being compared to melt down to constant values for the test, they will be valid, no matter how complex.

Take this simple statement where the equivalence operator = is used to test for exact matches:

SELECT * FROM EMPLOYEE WHERE LAST_NAME = 'Smith';

The predicate is “that the value in the column LAST_NAME is 'Smith'.” Two constants (the current column value and a string literal) are compared to test the assertion that they are equal. Taking each row in the Employee table, the engine will discard any where the predicate is either false (the value is something other than 'Smith') or not proven (the column has NULL, so it cannot be determined whether it is 'Smith' or a value that is not 'Smith').

The Truth Testers

The syntax elements that test the truth or non-truth of an assertion form a no-name paradigm of “truth testers” or “condition testers.” They all test predicates. They are

  • In DDL: CHECK, for testing validation conditions
  • In SQL: WHERE (for search conditions), HAVING and NOT HAVING (for group selection conditions), ON (for join conditions), and the multi-conditional case testers CASE, COALESCE, and NULLIF
  • In PSQL: IF (the universal true/false tester), WHILE (for testing loop conditions), and WHEN (for testing exception codes)

Assertions

Often, the condition being tested by WHERE, IF, and so on is not a single predicate, but a cluster of several predicates, each of which, when resolved, contributes to the truth or otherwise the ultimate assertion. The assertion might be a single predicate, or it might contain multiple predicates logically associated by AND or OR within it, which themselves might nest predicates. Resolution of an assertion to the ultimate true or false result is a process that works from the inner predicates outward. Each “level” must be resolved in precedence order, until it becomes possible to test the overall assertion.

In the next set of search conditions, the assertion tested encloses two conditions. The keyword AND ties the two predicates to each other, causing the ultimate assertion to be that both predicates must be true in order for the whole assertion to be true:

SELECT * FROM EMPLOYEE WHERE ( (HIRE_DATE > CURRENT_DATE - 366) AND (SALARY BETWEEN 25000.00 AND 39999.99));

Rows where one assertion is true but the other is false will be discarded.

The first predicate, (HIRE_DATE > CURRENT_DATE – 366), uses an expression consisting of a variable and a calculation to establish the value that is to be tested against the column value. In this case, the assertion uses a different operator —the comparison is not that the column value and the resolved value of the expression be equal, but that the column value be greater than that value.

The second predicate uses a different operator again. The BETWEEN symbol implements the test “greater than or equal to the value on the left AND less than or equal to the value on the right.”

The parentheses here are not obligatory, although, in many complex expressions, parentheses must be used to specify the order of precedence for evaluation of both expressions and the predicates. In situations where a series of predicates is to be tested, choosing to parenthesize predicates even where it is optional can be helpful for documentation and debugging.

Deciding What Is True

Figure depicts the possible results of the two predicates in the previous example.

In our example,(HIRE_DATE > CURRENT_DATE - 366) is tested first, because it is the leftmost predicate. If it nested any predicates, those would be evaluated first. There is no test for NULL in either of the predicates, but it is included here to illustrate that a null (“not known”) encountered in the test data will cause a result of false because it cannot return true . Predicates must be provably true in order to be true.

If NULL occurs in HIRE_DATE, testing will stop immediately and return false.1 The AND association causes that: Logically, two predicates conjoined by AND cannot be true if one is false, so there is nothing to gain by continuing on to test the second predicate.

Truth evaluation

Truth evaluation

If the data being tested is not null, the next test is whether it is true that the value is greater than the test condition. If not, testing finishes here. In other words, NULL and false have the same effect because neither is true.

If the first predicate is true, a similar process of elimination occurs with the second predicate. Only when that is all done can the ultimate assertion be evaluated.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics