Combining Result Sets with or without Duplicates in Font

Create Code 39 Extended in Font Combining Result Sets with or without Duplicates

The first two UNION samples below demonstrate the operation of the UNION operator with and without its ALL keyword. Both of these UNION samples combine identical SELECT statements to show the contrast between the two forms of the operator. The first statement returns all rows from the ProductCatetory table with a Name column value beginning with C. The second statement returns all rows from the ProductCategory table with a Name column value beginning with CL. In the ProductCategory table that ships with the AdventureWorks database, the first SELECT statement returns two rows, and the second one returns just one row. Furthermore, the single row returned by the second SELECT statement is a duplicate of one of the rows returned by the first statement.

When the first UNION sample runs with its ALL keyword, all three rows are in the combined result set. When the second UNION sample runs without the ALL keyword, only two rows are in the result set because the duplicate in the second SELECT statement is rejected. SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name LIKE 'C%' UNION ALL SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name LIKE 'Cl%' ProductCategoryID ----------------3 2 3 Name ---------Clothing Components Clothing

SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name LIKE 'C%' UNION SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name LIKE 'Cl%' ProductCategoryID ----------------2 3 Name ---------Components Clothing

When working with data from multiple data sources, you may occasionally want to add an extra column that designates the data source. This technique is particularly appropriate for cases in which you are not working with such a highly normalized database as AdventureWorks. For example, the Northwind database has telephone and other contact data in multiple data sources, such as the Customers, Employees, Suppliers, and Shippers tables. This approach is not as elegant as the AdventureWorks design, which stores all kinds of contact data in the Contact table within the Person schema. If you are working with a database that is more like Northwind than AdventureWorks, you may find consolidating the contact telephone data with UNION operators provides you a virtual central repository for querying contact data even while you manage pools of contact data separately. The following series of SELECT statements with UNION operators combines data from the Customers, Employees, and Shippers tables in the Northwind database. To make the combined result set compact, each SELECT statement includes TOP 3 to restrict its output to just three rows. The first three columns contain information related to contact name, phone, and fax. These column values are derived from the data sources, such as the Customers table. The last column denotes the data source that contains the contact information. The codes of Customer, Employee, and Shipper refer to the Customers, Employees, and Shippers tables in the Northwind database.

Notice the ORDER BY clause as the last clause in the series of SELECT statements. This clause applies to the overall combined result set. It does not just apply to the result set for the third SELECT statement. Using this kind of technique allows the grouping of rows from the same data source. USE Northwind GO SELECT TOP 3 ContactName, Phone, ISNULL(Fax, '') 'Fax', 'Customer' 'ContactType' FROM Customers UNION SELECT TOP 3 FirstName + ' ' + LastName, HomePhone, '', 'Employee' 'ContactType' FROM Employees UNION SELECT TOP 3 CompanyName , Phone, '', 'Shipper' 'ContactType' FROM Shippers ORDER BY ContactType ContactName ---------------Ana Trujillo Antonio Moreno Maria Anders Andrew Fuller Janet Leverling Nancy Davolio Federal Shipping Speedy Express United Package Phone -------------(5) 555-4729 (5) 555-3932 030-0074321 (206) 555-9482 (206) 555-3412 (206) 555-9857 (503) 555-9931 (503) 555-9831 (503) 555-3199 Fax ContactType -------------- ----------(5) 555-3745 Customer Customer 030-0076545 Customer Employee Employee Employee Shipper Shipper Shipper