15:59

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SQL data manipulation language

SELECT statement

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 product
WHERE maker = 'A' AND type = 'pc';

Intersection

Console

Execute

SELECT * FROM product
WHERE maker = 'A'
INTERSECT
SELECT * FROM product
WHERE 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 product
WHERE maker = 'A' OR maker = 'B';

Union

Console

Execute

SELECT * FROM product
WHERE maker = 'A'
UNION
SELECT * FROM product
WHERE 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 product
WHERE (maker = 'A' OR maker = 'B') AND type = 'pc';

may be expressed via intersection

Console

Execute

SELECT * FROM product
WHERE maker = 'A' OR maker = 'B'
INTERSECT
SELECT * FROM product
WHERE  type = 'pc';

but its equivalent form

Console

Execute

SELECT * FROM product
WHERE (maker = 'A' AND type = 'pc') OR (maker = 'B' AND type = 'pc');

may be expressed via union

Console

Execute

SELECT * FROM product
WHERE maker = 'A' AND type = 'pc'
UNION
SELECT * FROM product
WHERE maker = 'B' AND type = 'pc';

Find the models which are not PC models.

Here

cond1: type = 'pc'

NOT cond1

Console

Execute

SELECT * FROM product
WHERE NOT type = 'pc';

Set difference

Console

Execute

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.