SQL - (Query) Predicate

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - About

Boolean - Predicate 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.

Advertising

3 - Example

3.1 - Single

last_name = 'Smith', 

3.2 - Combination

combination of predicates

last_name = 'Smith' AND job_type = 'Clerk'

4 - Example of Predicates Operator

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

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