 |
|||||
go
Exercise #15 (tips and solutions) |
||
|
The incorrect solution 1.11.1 for this exercise can be corrected easily by using, instead of the model number, the proper column – the primary key code - for distinguishing PCs: 
Console
Execute
Since it is sufficient to check matching hard drive capacities for as little as two PCs, a self-join of the PC table with the same conditions can be used:
Console
Execute
However, the optimal solution would be using grouping with a filtering condition in a HAVING clause For the sake of completeness, here is another solution using a subquery with grouping, whose performance is inferior to that of the one above:
Console
Execute
The reason for the low efficiency of the solutions with subqueries is that they all use a correlated subquery, i. e. a subquery to be re-executed for each row returned by the main query. The query using join is the worst-performing one. This is quite understandable, since join operations are very costly despite the fairly efficient algorithms of their implementation [ 5 ]. |
||
