SELECT statement

Conventional set-theoretic operations and the SELECT statement

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.

SELECT maker FROM (
SELECT maker FROM Product WHERE type='PC'
INTERSECT ALL
SELECT maker FROM Product WHERE type ='Printer'
) 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:

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