SELECT Examples (Transact-SQL)
This topic provides examples of using the SELECT statement.
The following example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the Product table in the AdventureWorks database.
USE AdventureWorks ; GO SELECT * FROM Production.Product ORDER BY Name ASC ; -- Alternate way. USE AdventureWorks ; GO SELECT p.* FROM Production.Product p ORDER BY Name ASC ; GO
This example returns all rows (no WHERE clause is specified), and only a subset of the columns (Name, ProductNumber, ListPrice) from the Product table in the AdventureWorks database. Additionally, a column heading is added.
USE AdventureWorks ; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC ; GO
This example returns only the rows for Product that have a product line of R and that have days to manufacture that is less than 4.
USE AdventureWorks ; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC ; GO
The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product.
USE AdventureWorks ; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC ; GO
This is the query that calculates the revenue for each product in each sales order.
USE AdventureWorks ; GO SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID ORDER BY ProductName ASC ; GO
The following first example creates a temporary table named #Bicycles in tempdb. To use this table, always refer to it with the exact name that is shown. This includes the number sign (#).
USE tempdb ; IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL DROP TABLE #Bicycles ; GO USE AdventureWorks; GO SET NOCOUNT ON SELECT * INTO #Bicycles FROM Production.Product WHERE ProductNumber LIKE 'BK%' SET NOCOUNT OFF SELECT name FROM tempdb..sysobjects WHERE name LIKE '#Bicycles%' ; GO
Here is the result set.
name ------------------------------ #Bicycles_____________________
This second example creates the permanent table NewProducts.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts ;
GO
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED ;
GO
SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100
SELECT name
FROM sysobjects
WHERE name LIKE 'New%'
USE master ;
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL ;
GO
Here is the result set.
name ------------------------------ NewProducts (1 row(s) affected)
The following example shows queries that are semantically equivalent and illustrates the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery that retrieves one instance of each product name for which the product model is a long sleeve logo jersey, and the ProductModelID numbers match between the Product and ProductModel tables.
USE AdventureWorks ; GO SELECT DISTINCT Name FROM Production.Product p WHERE EXISTS (SELECT * FROM Production.ProductModel pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name = 'Long-sleeve logo jersey') ; GO -- OR USE AdventureWorks ; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN (SELECT ProductModelID FROM Production.ProductModel WHERE Name = 'Long-sleeve logo jersey') ; GO
The following example uses IN in a correlated, or repeating, subquery. This is a query that depends on the outer query for its values. The query is executed repeatedly, one time for each row that may be selected by the outer query. This query retrieves one instance of the first and last name of each employee for which the bonus in the SalesPerson table is 5000.00 and for which the employee identification numbers match in the Employee and SalesPerson tables.
USE AdventureWorks ; GO SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.EmployeeID = sp.SalesPersonID) ; GO
The previous subquery in this statement cannot be evaluated independently of the outer query. It requires a value for Employee.EmployeeID, but this value changes as the SQL Server 2005 Database Engine examines different rows in Employee.
A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the product models for which the maximum list price is more than twice the average for the model.
USE AdventureWorks GO SELECT p1.ProductModelID FROM Production.Product p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ALL (SELECT 2 * AVG(p2.ListPrice) FROM Production.Product p2 WHERE p1.ProductModelID = p2.ProductModelID) ; GO
This example uses two correlated subqueries to find the names of employees who have sold a particular product.
USE AdventureWorks ; GO SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE EmployeeID IN (SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN (SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42'))) ; GO
The following example finds the total of each sales order in the database.
USE AdventureWorks ; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail sod GROUP BY SalesOrderID ORDER BY SalesOrderID ; GO
Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.
The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.
Use AdventureWorks
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price',
SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID
GO
The following example puts the results into groups after retrieving only the rows with list prices greater than $1000.
USE AdventureWorks; GO SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price' FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID ; GO
The following example groups by an expression. You can group by an expression if the expression does not include aggregate functions.
USE AdventureWorks ; GO SELECT AVG(OrderQty) AS 'Average Quantity', NonDiscountSales = (OrderQty * UnitPrice) FROM Sales.SalesOrderDetail sod GROUP BY (OrderQty * UnitPrice) ORDER BY (OrderQty * UnitPrice) DESC ; GO
The first example that follows produces groups only for orders with quantities > 10.
The second example produces groups for all orders.
The column that holds the aggregate value (the average price) is NULL for groups that lack qualifying rows.
USE AdventureWorks ; GO SELECT ProductID, AVG(UnitPrice) AS 'Average Price' FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY ProductID ; GO -- Using GROUP BY ALL USE AdventureWorks ; GO SELECT ProductID, AVG(UnitPrice) AS 'Average Price' FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ALL ProductID ORDER BY ProductID ; GO
The first example that follows shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less. The second example shows a HAVING clause without aggregate functions.
USE AdventureWorks ; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID ; GO
This query uses the LIKE clause in the HAVING clause.
USE AdventureWorks ; GO SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumber HAVING CarrierTrackingNumber LIKE '4BD%' ORDER BY SalesOrderID ; GO
The following example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. It also organizes the results by ProductID.
USE AdventureWorks ; GO SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID ; GO
The following example groups the SalesOrderDetail table by product ID and includes only those groups of products that have orders totaling more than $1000000.00 and whose average order quantities are less than 3.
USE AdventureWorks ; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3 ; GO
To see the products that have had total sales greater than $2000000.00, use this query:
USE AdventureWorks ; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00 ; GO
If you want to make sure there are at least one thousand five hundred items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold. The query looks like this:
USE AdventureWorks ; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500 ; GO
The following example uses two code examples to show the use of COMPUTE BY. The first code example uses one COMPUTE BY with one aggregate function, and the second code example uses one COMPUTE BY item and two aggregate functions.
This query calculates the sum of the orders, for products with prices less than $5.00, for each type of product.
USE AdventureWorks ; GO SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, LineTotal COMPUTE SUM(LineTotal) BY ProductID ; GO
This query retrieves the product type and order total for products with unit prices under $5.00. The COMPUTE BY clause uses two different aggregate functions.
USE AdventureWorks ; GO SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, LineTotal COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID ; GO
The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on.
The following example finds the grand total of the prices and advances for all types of products les than $2.00.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $2.00 COMPUTE SUM(OrderQty), SUM(LineTotal) ; GO
You can use COMPUTE BY and COMPUTE without BY in the same query. The following query finds the sum of order quantities and line totals by product type, and then computes the grand total of order quantities and line totals.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID COMPUTE SUM(OrderQty), SUM(LineTotal) ; GO
The following example finds the sum of the prices of all orders whose unit price is less than $5 organized by product ID and order quantity, as well as the sum of the prices of all orders less than $5 organized by product ID only. You can use different aggregate functions in the same statement by including more than one COMPUTE BY clause.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, OrderQty, LineTotal COMPUTE SUM(LineTotal) BY ProductID, OrderQty COMPUTE SUM(LineTotal) BY ProductID ; GO
The first example that follows uses the COMPUTE clause to calculate the sum of all orders whose product's unit price is less than $5.00, by type of product. The second example produces the same summary information by using only GROUP BY.
USE AdventureWorks ; GO SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID COMPUTE SUM(LineTotal) BY ProductID ; GO
This is the second query that uses GROUP BY.
USE AdventureWorks ; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID ORDER BY ProductID ; GO
The following example returns only those orders whose unit price is less than $5, and then computes the line total sum by product and the grand total. All computed columns appear within the select list.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty COMPUTE SUM(SUM(LineTotal)) ; GO
The following example shows two code examples. The first example returns a result set from a SELECT statement by using the CUBE operator. By using the CUBE operator, the statement returns an extra row.
USE AdventureWorks ; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty WITH CUBE ORDER BY ProductID ; GO
NULL represents all values in the ProductID column. The result set returns values for the quantity sold of each product and the total quantity sold of all products. Applying the CUBE operator or ROLLUP operator returns the same result.
The following example uses the CubeExample table to show how the CUBE operator affects the result set and uses an aggregate function (SUM). The CubeExample table contains a product name, a customer name, and the number of orders each customer has made for a particular product.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.CubeExample', 'U') IS NOT NULL
DROP TABLE dbo.CubeExample ;
GO
CREATE TABLE dbo.CubeExample(
ProductName VARCHAR(30) NULL,
CustomerName VARCHAR(30) NULL,
Orders INT NULL
)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Wilman Kala', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 20)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Wilman Kala', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Wilman Kala', 20)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Wilman Kala', 30)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Eastern Connection', 40)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Outback Lager', 'Eastern Connection', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Wilman Kala', 40)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', 'Romero y tomillo', 10)
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Filo Mix', 'Romero y tomillo', 50) ;
GO
First, issue a typical query with a GROUP BY clause and the result set.
USE AdventureWorks ; GO SELECT ProductName, CustomerName, SUM(Orders) FROM CubeExample GROUP BY ProductName, CustomerName ORDER BY ProductName ; GO
The GROUP BY causes the result set to form groups within groups.
Here is the result set.
ProductName CustomerName ------------------------------ ------------------------------ ----------- Filo Mix Eastern Connection 40 Filo Mix Romero y tomillo 80 Filo Mix Wilman Kala 30 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Outback Lager Eastern Connection 10 Outback Lager Wilman Kala 30 (7 row(s) affected)
Next, issue a query with a GROUP BY clause by using the CUBE operator. The result set should include the same information and super-aggregate information for each of the GROUP BY columns.
USE AdventureWorks ; GO SELECT ProductName, CustomerName, SUM(Orders) FROM CubeExample GROUP BY ProductName, CustomerName WITH CUBE ; GO
The result set for the CUBE operator holds the values from the previous simple GROUP BY result set, and adds the super-aggregates for each column in the GROUP BY clause. NULL represents all values in the set from which the aggregate is computed.
Here is the result set.
ProductName CustomerName ------------------------------ ------------------------------ ----------- Filo Mix Eastern Connection 40 Filo Mix Romero y tomillo 80 Filo Mix Wilman Kala 30 Filo Mix NULL 150 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Ikura NULL 70 Outback Lager Eastern Connection 10 Outback Lager Wilman Kala 30 Outback Lager NULL 40 NULL NULL 260 NULL Eastern Connection 50 NULL Romero y tomillo 100 NULL Wilman Kala 110 (14 row(s) affected)
Line 4 of the result set indicates that a total of 150 orders for Filo Mix were placed for all customers.
Line 11 of the result set indicates that the total number of orders placed for all products by all customers is 260.
Lines 12-14 of the result set indicate that the total numbers of orders for each customer for all products are 100, 110, and 50, respectively.
In the following example, the SELECT statement returns the product model ID, product name, and quantity of orders. The GROUP BY clause in this example includes the ProductModelID and Name columns.
By using the CUBE operator, the result set contains more detailed information about the quantities of orders on products and product models. NULL represents all values in the title column.
USE AdventureWorks ; GO SELECT ProductModelID, p.Name AS ProductName, SUM(OrderQty) FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID GROUP BY ProductModelID, p.Name WITH CUBE ; GO
Increasing the number of columns in the GROUP BY clause shows why the CUBE operator is an n-dimensional operator. A GROUP BY clause with two columns returns three more kinds of groupings when the CUBE operator is used. The number of groupings can be more than three, depending on the distinct values in the columns.
The result set is grouped by the product model ID and then by the product name.
NULL in the ProductModelID column represents all ProductModels. NULL in the Name columns represents all Products. The CUBE operator returns the following groups of information from one SELECT statement:
-
Quantity of orders for each product model
-
Quantity of orders for each product
-
Total number of orders
Each column referenced in the GROUP BY clause has been cross-referenced with all other columns in the GROUP BY clause, and the SUM aggregate has been reapplied. This produces additional rows in the result set. Information returned in the result set grows n-dimensionally along with the number of columns in the GROUP BY clause.
 Note: |
|---|
| Make sure that the columns that follow the GROUP BY clause have meaningful, real-life relationships with each other. For example, if you use Name and ProductID, the CUBE operator returns irrelevant information. Using the CUBE operator on a real-life hierarchy, such as yearly sales and quarterly sales, produces meaningless rows in the result set. It is more efficient to use the ROLLUP operator. |
The following example shows how the SELECT statement uses the SUM aggregate, the GROUP BY clause, and the CUBE operator. It also uses the GROUPING function on the two columns that are listed after the GROUP BY clause.
USE AdventureWorks ; GO SELECT ProductModelID, GROUPING(ProductModelID), p.Name AS ProductName, GROUPING(p.Name), SUM(OrderQty) FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID GROUP BY ProductModelID, p.Name WITH CUBE ; GO
The result set has two columns that contain 0 and 1 values. These are produced by the GROUPING(ProductModelID) and GROUPING(p.Name) expressions.
The following example shows two code examples. This first example retrieves the product name, customer name, and the sum of orders placed and uses the ROLLUP operator.
USE AdventureWorks ; GO SELECT ProductName, CustomerName, SUM(Orders) AS 'Sum orders' FROM dbo.CubeExample GROUP BY ProductName, CustomerName WITH ROLLUP ; GO
Here is the result set.
ProductName CustomerName Sum orders ------------------------------ ------------------------------ ----------- Filo Mix Eastern Connection 40 Filo Mix Romero y tomillo 80 Filo Mix Wilman Kala 30 Filo Mix NULL 150 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Ikura NULL 70 Outback Lager Eastern Connection 10 Outback Lager Wilman Kala 30 Outback Lager NULL 40 NULL NULL 260 (11 row(s) affected)
This second example that follows performs a ROLLUP operation on the company and department columns and totals the number of employees.
The ROLLUP operator produces a summary of aggregates. This is useful when summary information is needed, but a full CUBE provides extraneous data or when you have sets within sets. For example, departments within a company are a set within a set.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Personnel', 'U') IS NOT NULL
DROP TABLE dbo.Personnel ;
GO
CREATE TABLE dbo.Personnel
(
CompanyName VARCHAR(20) NOT NULL,
Department VARCHAR(15) NOT NULL,
NumEmployees int NOT NULL
)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Finance', 10)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Engineering', 40)
INSERT dbo.Personnel VALUES ('Du monde entier', 'Marketing', 40)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Accounting', 20)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Personnel', 30)
INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Payroll', 40) ;
GO
In the following query, the company name, department, and the sum of all employees for the company become part of the result set, in addition to the ROLLUP calculations.
USE AdventureWorks ; GO SELECT CompanyName, Department, SUM(NumEmployees) FROM dbo.Personnel GROUP BY CompanyName, Department WITH ROLLUP ; GO
Here is the result set.
CompanyName Department -------------------- --------------- ----------- Du monde entier Engineering 40 Du monde entier Finance 10 Du monde entier Marketing 40 Du monde entier NULL 90 Piccolo und mehr Accounting 20 Piccolo und mehr Payroll 40 Piccolo und mehr Personnel 30 Piccolo und mehr NULL 90 NULL NULL 180 (9 row(s) affected)
The following example adds three new rows to the CubeExample table. Each of the three records NULL in one or more columns to show only the ROLLUP function produces a value of 1 in the grouping column. Also, this example modifies the SELECT statement that was used in the previous example.
USE AdventureWorks ;
GO
-- Add first row with a NULL customer name and 0 orders.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES ('Ikura', NULL, 0)
-- Add second row with a NULL product and NULL customer with real value
-- for orders.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES (NULL, NULL, 50)
-- Add third row with a NULL product, NULL order amount, but a real
-- customer name.
INSERT dbo.CubeExample (ProductName, CustomerName, Orders)
VALUES (NULL, 'Wilman Kala', NULL)
SELECT ProductName AS Prod, CustomerName AS Cust,
SUM(Orders) AS 'Sum Orders',
GROUPING(ProductName) AS 'Group ProductName',
GROUPING(CustomerName) AS 'Group CustomerName'
FROM CubeExample
GROUP BY ProductName, CustomerName
WITH ROLLUP ;
GO
The GROUPING function can be used only with CUBE or ROLLUP. The GROUPING function returns 1 when an expression evaluates to NULL, because the column value is NULL and represents the set of all values. The GROUPING function returns 0 when the corresponding column, whether it is NULL or not, did not come from either the CUBE or ROLLUP options as a syntax value. The returned value has a tinyint data type.
Here is the result set.
Prod Cust Sum Orders Group ProductName Group CustomerName ------------------------------ ------------------------------ ----------- ----------------- ------------------ NULL NULL 50 0 0 NULL Wilman Kala NULL 0 0 NULL NULL 50 0 1 Filo Mix Eastern Connection 40 0 0 Filo Mix Romero y tomillo 80 0 0 Filo Mix Wilman Kala 30 0 0 Filo Mix NULL 150 0 1 Ikura NULL 0 0 0 Ikura Romero y tomillo 20 0 0 Ikura Wilman Kala 50 0 0 Ikura NULL 70 0 1 Outback Lager Eastern Connection 10 0 0 Outback Lager Wilman Kala 30 0 0 Outback Lager NULL 40 0 1 NULL NULL 310 1 1 Warning: Null value is eliminated by an aggregate or other SET operation. (15 row(s) affected)
The following example uses a SELECT query that contains an aggregate function and a GROUP BY clause.
USE AdventureWorks ; GO SELECT pm.Name AS ProductModel, p.Name AS ProductName, SUM(OrderQty) FROM Production.ProductModel pm INNER JOIN Production.Product p ON pm.ProductModelID = p.ProductModelID INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID GROUP BY pm.Name, p.Name WITH ROLLUP ; GO
In the result set, NULL represents all values for that column.
If you use the SELECT statement without the ROLLUP operator, the statement creates a single grouping. The query returns a sum value for each unique combination of ProductModel, ProductModelID, and ProductName:
ProductModel ProductModelID title SUM(qty)
The GROUPING function can be used with the ROLLUP operator or with the CUBE operator. You can apply this function to one of the columns in the select list. The function returns either 1 or 0 depending upon whether the column is grouped by the ROLLUP operator.
The following example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table, and the second example forces a table scan by using an index of 0.
-- Use the specifically named INDEX. USE AdventureWorks ; GO SELECT c.FirstName, c.LastName, e.Title FROM HumanResources.Employee e WITH (INDEX(IX_Employee_ManagerID)) JOIN Person.Contact c on e.ContactID = c.ContactID WHERE ManagerID = 3 ; GO -- Force a table scan by using INDEX = 0. USE AdventureWorks ; GO SELECT c.LastName, c.FirstName, e.Title FROM HumanResources.Employee e WITH (INDEX = 0) JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE LastName = 'Johnson' ; GO
The following example shows how the OPTION (GROUP) clause is used with a GROUP BY clause.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10) ; GO
In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO
-- Here is the simple union.
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name ;
GO
In the following example, the INTO clause in the second SELECT statement specifies that the table named ProductResults holds the final result set of the union of the designated columns of the ProductModel and Gloves tables. Note that the Gloves table is created in the first SELECT statement.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
INTO ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves ;
GO
SELECT *
FROM dbo.ProductResults ;
The order of certain parameters used with the UNION clause is important. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves ;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4) ;
GO
/* INCORRECT */
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves ;
GO
/* CORRECT */
USE AdventureWorks ;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name ;
GO
The following examples use UNION to combine the results of three tables that all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.
The third example uses ALL with the first UNION and parentheses enclose the second UNION that is not using ALL. The second UNION is processed first because it is in parentheses, and returns 5 rows because the ALL option is not used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT by using the UNION ALL keywords. This does not remove the duplicates between the two sets of 5 rows. The final result has 10 rows.
USE AdventureWorks ;
GO
IF OBJECT_ID ('EmployeeOne', 'U') IS NOT NULL
DROP TABLE EmployeeOne ;
GO
IF OBJECT_ID ('EmployeeTwo', 'U') IS NOT NULL
DROP TABLE EmployeeTwo ;
GO
IF OBJECT_ID ('EmployeeThree', 'U') IS NOT NULL
DROP TABLE EmployeeThree ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeOne
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeTwo
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO EmployeeThree
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66 ;
GO
-- Union ALL
SELECT LastName, FirstName
FROM EmployeeOne
UNION ALL
SELECT LastName, FirstName
FROM EmployeeTwo
UNION ALL
SELECT LastName, FirstName
FROM EmployeeThree ;
GO
SELECT LastName, FirstName
FROM EmployeeOne
UNION
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree ;
GO
SELECT LastName, FirstName
FROM EmployeeOne
UNION ALL
(
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree
) ;
GO
Reference
CREATE TRIGGER (Transact-SQL)CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
Expressions (Transact-SQL)
INSERT (Transact-SQL)
LIKE (Transact-SQL)
UNION (Transact-SQL)
EXCEPT and INTERSECT (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)
Other Resources
Distributed QueriesSubquery Fundamentals
Using Variables and Parameters (Database Engine)
