About

Boolean - Predicate (Boolean Expression, Condition) in SQL.

A predicate acts as a filter that filters a certain number of rows from a row set.

You have two principal types of predicates:

• join predicates
• or filter predicates

Predicates are used in:

• WHERE clauses,
• HAVING clauses,
• JOIN conditions,
• FROM clauses,

and other constructs where a Boolean value is required.

Articles Related

Example of Predicates Operator

• BETWEEN
• EXISTS
• IN
• IS [NOT] NULL
• LIKE

How to write a predicate that takes null into account

How to write a predicate that takes null into account

• The state

create table IHateNull (id number not null, val1 number null, val2 number null);
insert into IHateNull values (1,2,NULL);
insert into IHateNull values (1,NULL,1);
insert into IHateNull values (1,0,0);
insert into IHateNull values (1,1,1);

• All records where ( val1 + val2 ) <> 0

select * from IHateNull where ( val1 + val2 ) <> 0;

ID       VAL1       VAL2
---------- ---------- ----------
         1          1          1

• should be written with:

select * from IHateNull where ( nvl(val1,0) + nvl(val2,0) ) <> 0;

ID       VAL1       VAL2
---------- ---------- ----------
         1          2
         1                     1
         1          1          1

Example of predicates information

The output of an Oracle explain plain contains a predicate information section. You can see for instance the the filter clause but also the join clause.

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("I1"."N"="MAP"."I2")
   3 - filter("I1"."V"='cx')
   5 - filter("I2"."V"='y')
   6 - access("I2"."N"="MAP"."I2")