06:07

# Predicates I page 3

Predicate in a WHERE clause corresponds to relational operation known as restriction, i.e. rows at the exit of FROM clause restrict themselves to those for which the predicate is evaluated as TRUE.

If cond1 and cond2  are simple conditions, the restriction by predicate

cond1 AND cond2

is equivalent to intersection of restrictions by each of predicates.

The restriction by predicate

cond1 OR cond2

is equivalent to union of restrictions by each of predicates, whereas the restriction by predicate

NOT cond1

is equivalent to set difference where restriction by predicate cond1 is subtracted from initial relation.

Let's turn to examples.

Get info about maker A's models.

Here

cond1: maker = 'A' ,

cond2: type = 'pc'.

#### cond1 AND cond2

Console
Execute
`SELECT * FROM productWHERE maker = 'A' AND type = 'pc';`

#### Intersection

Console
Execute
`SELECT * FROM productWHERE maker = 'A'INTERSECTSELECT * FROM productWHERE type = 'pc';`

Get info about models of makers A and B.

Here

cond1: maker = 'A' ,

cond2: maker = 'B'.

#### cond1 OR cond2

Console
Execute
`SELECT * FROM productWHERE maker = 'A' OR maker = 'B';`

#### Union

Console
Execute
`SELECT * FROM productWHERE maker = 'A'UNIONSELECT * FROM productWHERE maker = 'B';`

In its turn, conditions condX need not be simple. For example,

Get info about PC models of makers A and B.

Solution

Console
Execute
`SELECT * FROM productWHERE (maker = 'A' OR maker = 'B') AND type = 'pc';`
may be expressed via intersection

Console
Execute
`SELECT * FROM productWHERE maker = 'A' OR maker = 'B'INTERSECTSELECT * FROM productWHERE  type = 'pc';`
but its equivalent form

Console
Execute
`SELECT * FROM productWHERE (maker = 'A' AND type = 'pc') OR (maker = 'B' AND type = 'pc');`
may be expressed via union

Console
Execute
`SELECT * FROM productWHERE maker = 'A' AND type = 'pc'UNIONSELECT * FROM productWHERE maker = 'B' AND type = 'pc';`

Find the models which are not PC models.

Here

cond1: type = 'pc'

#### NOT cond1

Console
Execute
`SELECT * FROM productWHERE NOT type = 'pc';`

#### Set difference

Console
Execute
`SELECT * FROM productEXCEPTSELECT * FROM product WHERE type = 'pc';`

#### There are some words about performance

If no indexes are on the columns in conditions, query execution plan will use table scan.
In the first variants of above solutions such a table scan will be used only once, whereas the solutions on the basis of union, intersection, and exception of queries will use a table scan twice plus operation for comparison of row sets will be presented (ex. Nested Loops).
This makes a query a less effective, although there may exist optimizers capable for producing the same execution plan for two cases being compared by us.

 Pages 1 2 3