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:
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:
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:
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 ].