Русский    English

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):

  1. SELECT model, price
  2. FROM PC
  3. WHERE model IN (SELECT model
  4. FROM Product
  5. WHERE maker = 'B' AND
  6. type = 'PC'
  7. )
  8. UNION
  9. SELECT model, price
  10. FROM Laptop
  11. WHERE model IN (SELECT model
  12. FROM Product
  13. WHERE maker = 'B' AND
  14. type = 'Laptop'
  15. )
  16. UNION
  17. SELECT model, price
  18. FROM Printer
  19. WHERE model IN (SELECT model
  20. FROM Product
  21. WHERE maker = 'B' AND
  22. type = 'Printer'
  23. );

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:

  1. SELECT * FROM (SELECT model, price
  2. FROM PC
  3. UNION
  4. SELECT model, price
  5. FROM Laptop
  6. UNION
  7. SELECT model, price
  8. FROM Printer
  9. ) AS a
  10. WHERE a.model IN (SELECT model
  11. FROM Product
  12. WHERE maker = 'B'
  13. );

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).

Bookmark and Share
Pages 1 2 3
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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.