Conventional set-theoretic operations are union, intersect, exception, and Cartesian product.

Cartesian product

The Cartesian product discussed previously is realized as a comma-separated list of table expressions (tables, views, subqueries) in the FROM clause. In addition, another explicit join operation may be used:

SELECT Laptop.model, Product.model FROM Laptop CROSS JOIN Product;

Recall that the Cartesian product combines each row in the first table with each row in the second table. The number of the rows in the result set is equal to the number of the rows in the first table multiplied by the number of the rows in the second table. In the example under consideration, the Laptop table has 5 rows while the Product table has 16 rows. As a result, we get 5*16 = 80 rows. Hence, there is no result set of that query here. You may check this assertion executing above query on the academic database.

In the uncombined state, the Cartesian product is hardly used in practice. As a rule, it presents an intermediate restriction (horizontal ptojection) operation where the WHERE clause is available in the SELECT statement.

Union

The UNION keyword is used for integrating queries:

<query 1>
UNION [ALL]
<query 2>

The UNION operator combines the results of two SELECT statements into a single result set. If the ALL parameter is given, all the duplicates of the rows returned are retained; otherwise the result set includes only unique rows. Note that any number of queries may be combined. Moreover, the union order can be changed with parentheses.

The following conditions should be observed:

• The number of columns of each query must be the same.
  
• Result set columns of each query must be compared by the data type to each other (as they follows).
  
• The result set uses the column names in the first query.
• The ORDER BY clause is applied to the union result, so it may only be written at the end of the combined query.

Example. Find the model numbers and prices of the PCs and laptops:

SELECT model, price FROM PC UNION SELECT model, price FROM Laptop ORDER BY price DESC;

model	price
1750	1200.0
1752	1150.0
1298	1050.0
1233	980.0
1321	970.0
1233	950.0
1121	850.0
1298	700.0
1232	600.0
1233	600.0
1232	400.0
1232	350.0
1260	350.0

Example. Find the product type, the model number, and the price of the PCs and laptops:

SELECT Product .type, PC.model, price FROM PC INNER JOIN     Product ON PC.model = Product .model UNION SELECT Product .type, Laptop.model, price FROM Laptop INNER JOIN     Product ON Laptop.model = Product .model ORDER BY price DESC;

type	model	price
Laptop	1750	1200.0
Laptop	1752	1150.0
Laptop	1298	1050.0
PC	1233	980.0
Laptop	1321	970.0
PC	1233	950.0
PC	1121	850.0
Laptop	1298	700.0
PC	1232	600.0
PC	1233	600.0
PC	1232	400.0
PC	1232	350.0
PC	1260	350.0

Intersect and Exception

The SQL standard offers SELECT statement clauses for operating with the intersect and exception of queries. These are INTERSECT and EXCEPT clauses, which work as the UNION clause. The result set will include only those rows that are present in each query (INTERSECT) or only those rows from the first query that are not present in the second query (EXCEPT).

Many of the DBMS do not support these clauses in the SELECT statement. This is also true for MS SQL Server. There are also other means to be involved while performing intersect and exception operations. It should be noted here that the same result may be reached by differently formulating the SELECT statement. In the case of intersection and exception one could use the EXISTS predicate.

The EXISTS predicate

EXISTS::=
        [NOT] EXISTS (<table subquery>)

The EXISTS predicate evaluates to TRUE providing the subquery contains any rows, otherwise it evaluates to FALSE. NOT EXISTS works the same as EXISTS being satisfied if no rows are returnable by the subquery. This predicate does not evaluate to UNKNOWN.

As in our case, the EXISTS predicate is generally used with dependent subqueries. That subquery type has an outer reference to the value in the main query. The subquery result may be dependent on this value and must be separately evaluated for each row of the query that includes the subquery. Because of this, the EXISTS predicate may have different values for each row of the main query.

Intersection example. Find those laptop makers who also produce printers:

SELECT DISTINCT maker FROM Product AS Lap_product WHERE type = 'Laptop' AND EXISTS        (SELECT maker        FROM Product        WHERE type = 'Printer' AND maker = Lap_product.maker);

The printer makers are retrieved by the subquery and compared with the maker returned from the main query. The main query returns the laptop makers. So, for each laptop maker it is checked that the subquery returns any rows (i.e. this maker also produces printers). Because the two queries in the WHERE clause must simultaneously be satisfied (AND), the result set includes only wanted rows. The DISTINCT keyword is used to make sure each maker is in the returned data only once. As a result, we get:

maker

A

Exception example. Find those laptop makers who do not produce printers:

SELECT DISTINCT maker FROM Product AS Lap_product WHERE type = 'Laptop' AND NOT EXISTS        (SELECT maker        FROM Product        WHERE type = 'Printer' AND maker = Lap_product.maker);

Here, it is sufficient to replace EXIST in the previous example with NOT EXIST. So, the returned data includes only those main query rows, for which the subquery return no rows. As a result we get:

maker

B

C

Suggested exercises: 7, 29, 35, 36, 41, 45, 48, 49.