Русский    English

Exercise #17

Find the laptops having speeds less than all PCs. Result set: type, model, speed.

Following errors are associated with overuse of join operations. The most egregious example, the author believes, is the following:

  1. SELECT DISTINCT p.type, l.model, l.speed
  2. FROM Product p, Laptop l, PC c
  3. WHERE l.speed < (SELECT MIN (speed)
  4.                  FROM PC
  5.                  ) AND
  6.       p.type = 'laptop';

In the FROM clause it is using of the Cartesian product of three tables! If the presence of the Product table can still be somehow justified because is needed to specify also the type of product, then the PC table can be safely removed without affect on the result. Obviously, the decision would not be optimal for speed of execution. In addition, you may have problems with memory, since the power of an intermediate result can be huge even for relatively small tables. Recall that the power of the Cartesian product is the product of power operands. For example, for tables with the number of rows 100, 500 and 1000 of the Cartesian product contains 50000000 row!

And, nevertheless, the decision was correct, because the DISTINCT statement eliminates all duplicates, resulting from the Cartesian product.


To solve the problem on SQL-EX.RU

Bookmark and Share
The book was updated
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100