An outer join adds an outer row for each row from the preserved table (in our case, the Employees table) that the ON filter eliminates. So the ON filter now contains both the correlation between the Employees and Orders tables and the country filter, and you end up getting back all employees— regardless of whether they handled orders or are from the US. Also note that with an outer join, you must provide an input from the nonpreserved table (Orders) to the COUNT() aggregate function so that you don't consider the outer rows. GROUP BY ALL generates empty groups, so COUNT() produces a 0 for those groups and you don't need special treatment for them.
As another example of how you can use GROUP BY ALL in a join query, suppose you want only employees from Middle Earth and their order counts, including employees who didn't handle any orders. Because you want to keep only employees from Middle Earth, you put the country filter in the ON clause. And because you want to also create groups for Middle Earth employees who handled no orders, you need the correlation between Employees and Orders in the WHERE clause. The query in Listing 7 generates the desired result, as Table 7 shows.
Of course, you can achieve the same result in other ways, including using an outer join. But the GROUP BY ALL option gives you a deeper level of control and, in some cases, lets you simplify your join solutions. When you're done experimenting with these queries, you can run the following cleanup code to delete the employees and the order you added to the Northwind database:
DELETE FROM dbo.Orders
WHERE OrderID = 12345;
DELETE FROM dbo.Employees
WHERE EmployeeID IN(10, 11);
GROUP BY ALL's Future
GROUP BY ALL lets me develop simpler solutions than its alternatives. And although this option isn't part of the ANSI SQL standard, it fits well in the phases of logical query processing and meets the "spirit" of the standard. However, GROUP BY ALL apparently complicates matters for Microsoft's SQL Server development team as it adds new language elements to T-SQL. As a result, Microsoft might not support the option in the future—something to consider before deciding to use the feature in production code. The good news is that Microsoft hasn't formally marked GROUP BY ALL for deprecation yet. Typically, Microsoft removes a feature from SQL Server two versions after the version in which the feature formally enters a deprecation process. So, GROUP BY ALL should be around quite a while longer, giving you time to tap into its benefits.
End of Article
Prev. page
1
[2]
next page -->
