Understanding predicates in SQL statements

A predicate is an element of SQL that is used for search operations. These are specified in the WHERE and the HAVING clause. Every database has its own set of predicates, which are used to filter the table data. There are different types of predicates in DB2, and DB2 has its own way of evaluating them. Based on the cost and the efficiency of a predicate, we can classify them as follows:

• Range delimiting predicates

• Index SARGable predicates

• Data SARGable predicates

• Residual predicates

Note

SARGable is a term used for Search Argument

In this recipe, we will discuss these predicates, and analyze their impact on query performance.

Getting ready

All the examples used in this recipe use the tables in the SAMPLE database. A SAMPLE database can be created by using the db2sampl command.

How to do it...

While applying predicates in an SQL statement, we should aim for the highest selectivity possible, so that the fewest rows are returned. Depending...