SQL syntax Language 

Conventional set-theoretic operations and the SELECT statement

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:

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.





Previous | Index | Next


Home SELECT exercises (rating stages) DML exercises Developers