09:17

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Computer Firm Database

Exercise #7 page 2

There is no difficulty correcting the query - the simple predicate («=») just has to be replaced by the predicate checking the occurrence in a list of values (IN):

Console

Execute

SELECT model, price 
FROM PC 
WHERE model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'PC'
 )
UNION
SELECT model, price 
FROM Laptop 
WHERE model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'Laptop'
 )
UNION
SELECT model, price 
FROM Printer 
WHERE model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'Printer'
);

Note that possible duplicate pairs of values {model, price} will be eliminated by the UNION operator.

However, redundancy is evident in this case: for each of the queries combined filtering by manufacturer B is done separately. This can be fixed by first doing the combining, then the filtering by manufacturer:

Console

Execute

SELECT * FROM (SELECT model, price 
 FROM PC
 UNION
 SELECT model, price 
 FROM Laptop
 UNION
 SELECT model, price 
 FROM Printer
 ) AS a
WHERE a.model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B'
 );

Here, filtering by product type is no longer possible, but we don’t really need it since the model number is unique within the Product table – i.e., a single model number can’t belong to products of different types. As a result, the query plan will have 8 operations instead of 12 for the initial query. Consequently, the second query will have a shorter execution time.

The IN predicate is evaluated for each record combined by UNION. Therefore the efficiency of such a query depends on how far down the list the model sought for is. For models excluded from the result set the whole list has to be scanned. Eventually, the execution time of such queries is the longer, the larger the list (i.e. the more models are manufactured by maker B).

Pages

IN UNION subquery

Previous [Exercise #6]

[Exercise #8] Next

Last added:

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
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Computer Firm Database

Exercise #7 page 2