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
Single
last_name = 'Smith',
Combination
combination of predicates
last_name = 'Smith' AND job_type = 'Clerk'
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")