SQL - (Query) Predicate

Data System Architecture

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.

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")





Discover More
Data System Architecture
Data Partition - Partition Pruning (Elimination)

Partition Pruning is access paths methods that occurs when a table is partitioned by the column in the predicate. In this case, the database will read only the partitions involved and not the full table....
Jpa Mapping Method
JPA - Entity Annotations

A key feature of EJB 3.0 and JPA is the ability to create entities that contain object-relational mappings by using (metadata) annotations rather than deployment descriptors (orm.xml) as in earlier versions....
Grammar Different Structural Trees For The Same Expression
Language - (Grammar | Syntax | Lexicon)

Grammar in the context of a compiler. Ie how to define it. grammar section In processing computer languages, semantic processing generally comes after syntactic processing, but in some cases semantic...
Obiee Permission Automatic Filter
OBIEE - How to define the BI server security to add automatically a filter when a column is added to an answer (row security level)

“How do we make BI EE to generate different filters for every column(within a dimension) chosen from Answers?” The idea is when you add the column Channel desc from the SH sample schema to an answer...
Obiee Pivot Measures
OBIEE - Pivot Measures (The Filter Function)

Indicator, description of Measures are generally the sign that you need to pivot your measure (i.e. to create a Pivot Measure) The revenue is booked. Revenue is the measure and booked is a description...
Saw Object
OBIEE 10G/11G - Conditional Filtering based on Presentation Service Variable

How to apply a filter based on a condition ? The value of stock is a good example. You can't sum it because it's point in time fact. The stock at the end of the week is not the sum of all stock by...
Card Puncher Data Processing
Oracle Database - Null value (Missing Value)

SQL Null in the Oracle context. By default all columns allow NULL. Nulls are stored in the database and require 1 byte to store the length of the column (zero). Null is not aggregated and selected on...
Card Puncher Data Processing
Oracle Database - Selectivity

The first measure of the plan_estimator, selectivity, represents a fraction of rows from a row set. The row set can be a base table, a view, or the result of a join or a GROUP BY operator. The selectivity...
Oracle Database Star Transformation
Oracle Database - Star Transformation

This article talk the application of the star query in Oracle which involve a star schema. Star transformation executes the query in two phases: retrieves the necessary rows from the fact table (row...
Data System Architecture
Phantom read/update problem

Phantom problem is a phenomena. A data problem during concurrency update. In the phantom problem, a transaction accesses a relation more than once with the same predicate in the same transaction, but...



Share this page:
Follow us:
Task Runner