loading..
Русский    English
15:09

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
  1. SELECT * FROM product
  2. WHERE maker = 'A' AND type = 'pc';

Intersection

Console
Execute
  1. SELECT * FROM product
  2. WHERE maker = 'A'
  3. INTERSECT
  4. SELECT * FROM product
  5. WHERE type = 'pc';

Get info about models of makers A and B.

Here

cond1: maker = 'A' ,

cond2: maker = 'B'.

cond1 OR cond2

Console
Execute
  1. SELECT * FROM product
  2. WHERE maker = 'A' OR maker = 'B';

Union

Console
Execute
  1. SELECT * FROM product
  2. WHERE maker = 'A'
  3. UNION
  4. SELECT * FROM product
  5. 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
  1. SELECT * FROM product
  2. WHERE (maker = 'A' OR maker = 'B') AND type = 'pc';
may be expressed via intersection

Console
Execute
  1. SELECT * FROM product
  2. WHERE maker = 'A' OR maker = 'B'
  3. INTERSECT
  4. SELECT * FROM product
  5. WHERE  type = 'pc';
but its equivalent form

Console
Execute
  1. SELECT * FROM product
  2. WHERE (maker = 'A' AND type = 'pc') OR (maker = 'B' AND type = 'pc');
may be expressed via union

Console
Execute
  1. SELECT * FROM product
  2. WHERE maker = 'A' AND type = 'pc'
  3. UNION
  4. SELECT * FROM product
  5. WHERE maker = 'B' AND type = 'pc';

Find the models which are not PC models.

Here

cond1: type = 'pc'

NOT cond1

Console
Execute
  1. SELECT * FROM product
  2. WHERE NOT type = 'pc';

Set difference

Console
Execute
  1. SELECT * FROM product
  2. EXCEPT
  3. 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.

Bookmark and Share
Pages 1 2 3
Развернуть всё
Свернуть всё

Content:

Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
https://exchangesumo.com/obmen/OMG-KSTRUAH/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100