Use the ALL Option with Set Operators






Use the ALL Option with Set Operators

In Chapter 13, "Combining Table Expressions," we discussed the ALL option for the set operators UNION, INTERSECT, and EXCEPT. Adding ALL to these operators has the effect that duplicate rows are not removed from the result. The ALL option has a function that is comparable to ALL in the SELECT clause; see Section 9.4, in Chapter 9. If ALL is not specified, all rows have to be sorted to be able to remove duplicate rows (sorting takes places behind the scenes). In other words, the guidelines given in the previous section also apply to the ALL option: If possible, use ALL in conjunction with the set operators.

14. Find the names and initials of the players who live in Stratford and Douglas.

SELECT   NAME, INITIALS
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
UNION ALL
SELECT   NAME, INITIALS
FROM     PLAYERS
WHERE    TOWN = 'Douglas'

Explanation Because of the presence of the keyword ALL, SQL will not perform a sort to remove possible duplicate rows. Luckily, this result will never return duplicate rows because each player lives in only one town. So, in this example, a sort would always be performed unnecessarily, thus wasting performance.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows