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.
Find the manufacturers which produce not less than two models of PCs and not less than two models of the printers.
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:
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.