loading..
Русский    English
04:28

Intersect and Except page 3

It is worth mentioning that not all the DBMS support these clauses of SELECT statement. In particular, INTERSECT/EXCEPT are not supported by MySQL and has been supported by MS SQL Server beginning with 2005 version, and the key word ALL was not included into it. ALL in conjunction with INTERSECT/EXCEPT are not maintained by Oracle either. It should be noted that MINUS keyword is used in Oracle instead of standard EXCEPT.

Therefore for fulfillment of operations of intersection and subtraction other means can be used. It should be mentioned in this respect that the same result can be obtained using various wordings of SELECT statement. In the case of intersection and subtraction one can use the predicate of existence EXISTS.

In the end let’s consider the example of use of INTERSECT ALL operation.

Example 5.7.5

Find the manufacturers which produce not less than two models of PCs and not less than two models of the printers.

  1. SELECT maker FROM (
  2. SELECT maker FROM Product WHERE type='PC'
  3. INTERSECT ALL
  4. SELECT maker FROM Product WHERE type ='Printer'
  5. ) X GROUP BY maker HAVING COUNT(*)>1;

INTERSECT ALL in the subquery of this solution will leave the minimal number of duplicates, i.e. if the producer manufactures 2 models of PC and one model of the printer (or vice a versa), he will be mentioned in the resulting data set once. Further on we make the grouping in accordance with the name of producer, leaving only those of them, that are indicated in the results of the subquery more than once.

Of course, we can solve this problem without the use of intersection operation. For example, by means of one query we will select those producers, that manufacture not less than 2 PC models, and by means of another query we will select those that produce not less than 2 models of printers. The solution of the problem will lead to joining of the subqueries. Below this algorithm is achieved on the basis of one standard type of joins – natural join:

  1. SELECT PC.maker FROM (
  2. SELECT maker FROM Product
  3. WHERE type='PC' GROUP BY maker HAVING COUNT(*)>1) PC
  4. NATURAL JOIN
  5. (
  6. SELECT maker FROM Product
  7. WHERE type='Printer' GROUP BY maker HAVING COUNT(*)>1) Pr;

NATURAL JOIN is equijoin within the columns with identical names. SQL Server does not support the natural join, therefore the last query may be executed, say, by means of PostgreSQL.
Bookmark and Share
Pages 1 2 3 4
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100