Predicates I

Predicate is an expression taking truth value. It may be both a single expression or any combination of a numberless expressions built by means of Boolean operators AND, OR, and NOT. Besides, these combinations may inclide SQL-operator IS, and parentheses that define the order in which operations are to be executed.

SQL predicate evaluates to TRUE, FALSE and UNKNOWN. The following predicates are exceptions to this rule: IS NULL, EXISTS, UNIQUE, and MATCH, which could not evaluate to UNKNOWN.

To remember combination rules for these three truth values, let us denote TRUE as 1, FALSE as 0, and UNKNOWN as 1/2 (somewhere between true and false) [2].

• AND with two truth values gives minimum of these values. For example, TRUE AND UNKNOWN is UNKNOWN.

• OR with two truth values gives maximum of these values. For example, FALSE OR UNKNOWN is UNKNOWN.

• Negation of truth value is 1 minus this truth value. For example, NOT UNKNOWN is UNKNOWN.

Suggested exercises: 5, 33

Logical operators as arithmetic ones are being estimated in accordance with their seniority.

One-place operation NOT has highest priority. We can assure ourselves if we’ll execute the two following queries.

-- models which are not PC;
-- the second predicate is useless because it is entering condition that yet taken into account
-- by the first predicate
select maker, model, type
from Product
where not type='PC' or type='Printer';

-- models of maker A, which are not PC
select maker, model, type
from Product
where not type='PC' and maker='A';

Parentheses can change the order of estimation of logical operators:

-- models that are not PC or printer, i.e. models of laptops in our case
select maker, model, type
from Product
where not (type='PC' or type='Printer');

-- models that are not PC produced by maker A
select maker, model, type
from Product
where not (type='PC' and maker='A');

Next priority has AND operator. Compare results of the following queries.

-- PC models produced by maker A and models of any type belonging to maker B
select maker, model, type
from Product
where type='PC' and maker='A' or maker='B';

-- PC models produced by maker A or maker B
select maker, model, type
from Product
where type='PC' and (maker='A' or maker='B');

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.

Here

cond1: maker = ‘A’ ,

cond2: type = ‘pc’.

cond1 AND cond2

select * from product
where maker = 'A' and type = 'pc';

Intersection

select * from product
where maker = 'A'
INTERSECT
select * from product
where type = 'pc';

Here

cond1: maker = ‘A’ ,

cond2: maker = ‘B’.

cond1 OR cond2

select * from product
where maker = 'A' OR maker = 'B';

Union

select * from product
where maker = 'A'
UNION
select * from product
where maker = 'B';

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

Solution

select * from product
where (maker = 'A' OR maker = 'B') and type = 'pc';

select * from product
where maker = 'A' OR maker = 'B'
INTERSECT
select * from product
where  type = 'pc';

select * from product
where (maker = 'A' and type = 'pc') OR (maker = 'B' and type = 'pc');

select * from product
where maker = 'A' and type = 'pc'
UNION
select * from product
where maker = 'B' and type = 'pc';

Here

cond1: type = ‘pc’

NOT cond1

select * from product
where NOT type = 'pc';

Set difference

select * from product
EXCEPT
select * 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.