{"id":891,"date":"2010-05-24T00:00:00","date_gmt":"2010-05-24T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-apply-basics\/"},"modified":"2021-09-29T16:21:59","modified_gmt":"2021-09-29T16:21:59","slug":"sql-server-apply-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-apply-basics\/","title":{"rendered":"SQL Server APPLY Basics"},"content":{"rendered":"
\n

Starting with SQL Server 2005, you can use the APPLY operator in a Transact-SQL query to join a table to a table-valued function so the function is evoked for each row returned from the table. For example, you might create a query that returns a list of employees from the Employee table. For each employee, you also want to return a list of the individual job positions that person has held in the company.<\/p>\n

You can create a function that retrieves the employees’ positions and then evoke that function for each row returned from the Employee table. Your result set will then include a row for each position that an employee has held. For instance, if John has worked as a salesman and a regional supervisor, the result set will include two rows for John, one for each position. Any data returned from the Employee table will be repeated for each row, but the data returned by the function will be specific to each row in the function’s results.<\/p>\n

The APPLY operator can take one of two forms: CROSS APPLY or OUTER APPLY. The CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. That means, in the example above, an employee would be included in the returned data only if that employee has held a specific position within the company. The OUTER APPLY form, on the other hand, returns all rows from the outer table, even if the function produces no results. So an employee would be listed even if that employee held no specific position.<\/p>\n

In this article, I demonstrate how to work with both forms of the APPLY operator. The examples I show you were created on a local instance of SQL Server 2008 and the AdventureWorks2008 sample database. If you want to run these examples against the AdventureWorks database on an instance of either SQL Server 2005 or 2008, you must change references to the BusinessEntityID column to SalesPersonID column, where appropriate. In addition, you’ll find that your results might vary slightly from those shown here because the values between the databases are slightly different, particularly primary key values.<\/p>\n

Using the CROSS APPLY Operator<\/h1>\n

As I mentioned, CROSS APPLY returns only those rows in the outer table for which the table value function returns data. Let’s look at an example to demonstrate how this works. Fist, we’ll create a function that returns the top three sales generated by a salesperson, as those sales appear in the Sales.SalesOrderHeader table in the AdventureWorks2008 database:<\/p>\n

USE AdventureWorks2008\nGO\nIF OBJECT_ID (N'fn_sales', N'IF') IS NOT NULL\n  DROP FUNCTION dbo.fn_sales\nGO\nCREATE FUNCTION fn_sales (@SalesPersonID int)\nRETURNS TABLE\nAS\nRETURN\n(\n  SELECT TOP 3 \n    SalesPersonID, \n    ROUND(TotalDue, 2) AS SalesAmount\n  FROM \n    Sales.SalesOrderHeader\n  WHERE \n    SalesPersonID = @SalesPersonID\n  ORDER BY \n    TotalDue DESC\n)\nGO\n<\/pre>\n
As you can see, the fn_sales function takes one parameter, @SalesPersonID, which is configured with the int data type. The function returns the three highest sales for the specified salesperson. Note that this is a table-valued function, which means that it returns the entire result set generated by the SELECT statement. For the fn_sales function, the result set includes the SalesPersonID and SalesAmount columns.<\/p>\n
After you create your function, you can test it by running a SELECT statement that retrieves data from the function. For example, the following SELECT statement returns the SalesAmount column for salesperson ID 285:<\/p>\n
SELECT SalesAmount FROM fn_sales(285)\n<\/pre>\n
The following table shows the results returned by the statement. As you would expect, three rows have been returned-the three highest sales for this salesperson.<\/p>\n\n\n\n\n\n\n
\n
SalesAmount<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
85652.33<\/p>\n<\/td>\n<\/tr>\n
\n
45338.76<\/p>\n<\/td>\n<\/tr>\n
\n
36317.54<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
After you’ve verified that the function is returning the correct results, you can use the function within a statement that includes the CROSS APPLY operator. In the following example, I retrieve data from the Sales.vSalesPerson view and join it to the fn_sales function:<\/p>\n
SELECT \n  sp.FirstName + ' ' + sp.LastName AS FullName,\n  fn.SalesAmount\nFROM\n  Sales.vSalesPerson AS sp\nCROSS APPLY\n  fn_sales(sp.BusinessEntityID) AS fn\nORDER BY\n  sp.LastName, fn.SalesAmount DESC\n<\/pre>\n
As you can see, I use the CROSS APPLY operator in the FROM clause by first specifying the outer table (vSalesPerson), then the CROSS APPLY operator, and finally the fn_sales function. Notice that I pass in the BusinessEntityID value as the function’s parameter. This value is based on the BusinessEntityID value as it appears in the current row that is being returned from the outer table.<\/p>\n
\n
NOTE:<\/b> The BusinessEntityID column in the vSalesPerson view uses the same IDs that are used in the SalesPersonID column in the SalesOrderHeader table. In the original version of the AdventureWorks database, the column name in vSalesPerson is SalesPersonID, just like it is in the SalesOrderHeader table.<\/p>\n<\/div>\n
Also notice that the columns in the SELECT list reference the source table and function as they would if I were joining two tables. I assign an alias to the table (sp) and one to the function (fn) and then reference the columns accordingly. For example, because the SalesAmount column in the SELECT list is returned by the function, I qualify the column name as fn.SalesAmount.<\/p>\n
The following table shows part of the results returned by the SELECT statement above. Notice that each salesperson is listed three times, once for each result returned by the fn_sales function.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
FullName<\/b><\/p>\n<\/td>\n
\n
SalesAmount<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Syed Abbas<\/p>\n<\/td>\n
\n
85652.33<\/p>\n<\/td>\n<\/tr>\n
\n
Syed Abbas<\/p>\n<\/td>\n
\n
45338.76<\/p>\n<\/td>\n<\/tr>\n
\n
Syed Abbas<\/p>\n<\/td>\n
\n
36317.54<\/p>\n<\/td>\n<\/tr>\n
\n
Amy Alberts<\/p>\n<\/td>\n
\n
98405.08<\/p>\n<\/td>\n<\/tr>\n
\n
Amy Alberts<\/p>\n<\/td>\n
\n
96243.80<\/p>\n<\/td>\n<\/tr>\n
\n
Amy Alberts<\/p>\n<\/td>\n
\n
95193.67<\/p>\n<\/td>\n<\/tr>\n
\n
Pamela Ansman-Wolfe<\/p>\n<\/td>\n
\n
125254.49<\/p>\n<\/td>\n<\/tr>\n
\n
Pamela Ansman-Wolfe<\/p>\n<\/td>\n
\n
125144.01<\/p>\n<\/td>\n<\/tr>\n
\n
Pamela Ansman-Wolfe<\/p>\n<\/td>\n
\n
118284.78<\/p>\n<\/td>\n<\/tr>\n
\n
Michael Blythe<\/p>\n<\/td>\n
\n
198628.31<\/p>\n<\/td>\n<\/tr>\n
\n
Michael Blythe<\/p>\n<\/td>\n
\n
142942.01<\/p>\n<\/td>\n<\/tr>\n
\n
Michael Blythe<\/p>\n<\/td>\n
\n
139659.67<\/p>\n<\/td>\n<\/tr>\n
\n
David Campbell<\/p>\n<\/td>\n
\n
149897.36<\/p>\n<\/td>\n<\/tr>\n
\n
David Campbell<\/p>\n<\/td>\n
\n
136046.44<\/p>\n<\/td>\n<\/tr>\n
\n
David Campbell<\/p>\n<\/td>\n
\n
125068.34<\/p>\n<\/td>\n<\/tr>\n
\n
Jillian Carson<\/p>\n<\/td>\n
\n
162629.75<\/p>\n<\/td>\n<\/tr>\n
\n
Jillian Carson<\/p>\n<\/td>\n
\n
154912.07<\/p>\n<\/td>\n<\/tr>\n
\n
Jillian Carson<\/p>\n<\/td>\n
\n
144355.88<\/p>\n<\/td>\n<\/tr>\n
\n
Shu Ito<\/p>\n<\/td>\n
\n
247913.91<\/p>\n<\/td>\n<\/tr>\n
\n
Shu Ito<\/p>\n<\/td>\n
\n
227737.72<\/p>\n<\/td>\n<\/tr>\n
\n
Shu Ito<\/p>\n<\/td>\n
\n
189198.62<\/p>\n<\/td>\n<\/tr>\n
\n
Stephen Jiang<\/p>\n<\/td>\n
\n
149861.07<\/p>\n<\/td>\n<\/tr>\n
\n
Stephen Jiang<\/p>\n<\/td>\n
\n
114361.94<\/p>\n<\/td>\n<\/tr>\n
\n
Stephen Jiang<\/p>\n<\/td>\n
\n
112733.70<\/p>\n<\/td>\n<\/tr>\n
\n
Tete Mensa-Annan<\/p>\n<\/td>\n
\n
140734.49<\/p>\n<\/td>\n<\/tr>\n
\n
Tete Mensa-Annan<\/p>\n<\/td>\n
\n
137108.39<\/p>\n<\/td>\n<\/tr>\n
\n
Tete Mensa-Annan<\/p>\n<\/td>\n
\n
115068.64<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That’s all there is to using the CROSS APPLY operator. Not let’s look at another example that uses the operator with a common table expression (CTE).<\/p>\n
Using the CROSS APPLY Operator with a CTE<\/h1>\n
To demonstrate how you can use the APPLY operator with a CTE, I created the following function, which returns the product model associated with the specified product:<\/p>\n
USE AdventureWorks2008\nGO\nIF OBJECT_ID (N'fn_products', N'IF') IS NOT NULL\n  DROP FUNCTION dbo.fn_products\nGO\nCREATE FUNCTION fn_products (@ProductID int)\nRETURNS TABLE\nAS\nRETURN\n(\n  SELECT\n    p.Name AS ProductName,\n    pm.Name AS ProductModel\n  FROM\n    Production.Product AS p\n    LEFT OUTER JOIN Production.ProductModel AS pm\n      ON p.ProductModelID = pm.ProductModelID\n  WHERE\n    p.ProductID = @ProductID\n)\nGO\n<\/pre>\n
The function joins the Production.Product and Production.ProductModel tables to return the product name and model name for the specified product ID. I then used the following SELECT statement to verify that the function works as I expect:<\/p>\n
SELECT ProductName, ProductModel \nFROM fn_products(707)\n<\/pre>\n
The statement returns the product name and model for product 707 (shown in the following table). As you can see, the product Sport-100 Helmet, Red<\/i> is associated with the model Sport-100<\/i>.<\/p>\n\n\n\n\n
\n
ProductName<\/b><\/p>\n<\/td>\n
\n
ModelName<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Sport-100 Helmet, Red<\/p>\n<\/td>\n
\n
Sport-100<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You can then use the CROSS APPLY operator to join a CTE to the function, as shown in the following example:<\/p>\n
WITH \n  ProductSales (ProductID, TotalSales)\n  AS\n  (\n    SELECT \n      ProductID, \n      SUM(LineTotal)\n    FROM Sales.SalesOrderDetail\n    GROUP BY ProductID\n  )\nSELECT \n  ps.ProductID,\n  ps.TotalSales,\n  fn.ProductModel\nFROM \n  ProductSales AS ps\nCROSS APPLY\n  fn_products(ps.ProductID) AS fn\nORDER BY \n  ps.ProductID\n<\/pre>\n
Notice that I first define a CTE named ProductSales. The CTE returns the total sales for each product as they appear in the Sales.SalesOrderDetail table. I then use the CTE in the main SELECT statement, along with the fn_products function. Notice that I use the CROSS APPLY operator in the FROM clause to join the CTE to the function, as I would use the operator to join a table or view to the function. The following table shows part of the results returned by the SELECT statement.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
ProductID<\/b><\/p>\n<\/td>\n
\n
TotalSales<\/b><\/p>\n<\/td>\n
\n
ProductModel<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
707<\/p>\n<\/td>\n
\n
157772.394392<\/p>\n<\/td>\n
\n
Sport-100<\/p>\n<\/td>\n<\/tr>\n
\n
708<\/p>\n<\/td>\n
\n
160869.517836<\/p>\n<\/td>\n
\n
Sport-100<\/p>\n<\/td>\n<\/tr>\n
\n
709<\/p>\n<\/td>\n
\n
6060.388200<\/p>\n<\/td>\n
\n
Mountain Bike Socks<\/p>\n<\/td>\n<\/tr>\n
\n
710<\/p>\n<\/td>\n
\n
513.000000<\/p>\n<\/td>\n
\n
Mountain Bike Socks<\/p>\n<\/td>\n<\/tr>\n
\n
711<\/p>\n<\/td>\n
\n
165406.617049<\/p>\n<\/td>\n
\n
Sport-100<\/p>\n<\/td>\n<\/tr>\n
\n
712<\/p>\n<\/td>\n
\n
51229.445623<\/p>\n<\/td>\n
\n
Cycling Cap<\/p>\n<\/td>\n<\/tr>\n
\n
713<\/p>\n<\/td>\n
\n
21445.710000<\/p>\n<\/td>\n
\n
Long-Sleeve Logo Jersey<\/p>\n<\/td>\n<\/tr>\n
\n
714<\/p>\n<\/td>\n
\n
115249.214976<\/p>\n<\/td>\n
\n
Long-Sleeve Logo Jersey<\/p>\n<\/td>\n<\/tr>\n
\n
715<\/p>\n<\/td>\n
\n
198754.975360<\/p>\n<\/td>\n
\n
Long-Sleeve Logo Jersey<\/p>\n<\/td>\n<\/tr>\n
\n
716<\/p>\n<\/td>\n
\n
95611.197080<\/p>\n<\/td>\n
\n
Long-Sleeve Logo Jersey<\/p>\n<\/td>\n<\/tr>\n
\n
717<\/p>\n<\/td>\n
\n
394255.572400<\/p>\n<\/td>\n
\n
HL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
718<\/p>\n<\/td>\n
\n
395182.699300<\/p>\n<\/td>\n
\n
HL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
719<\/p>\n<\/td>\n
\n
89872.173600<\/p>\n<\/td>\n
\n
HL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
722<\/p>\n<\/td>\n
\n
177635.904000<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
723<\/p>\n<\/td>\n
\n
24844.692200<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
725<\/p>\n<\/td>\n
\n
194692.599104<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
726<\/p>\n<\/td>\n
\n
132125.252200<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
727<\/p>\n<\/td>\n
\n
20104.443400<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
729<\/p>\n<\/td>\n
\n
195933.409400<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
730<\/p>\n<\/td>\n
\n
137213.485128<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
732<\/p>\n<\/td>\n
\n
89224.500000<\/p>\n<\/td>\n
\n
ML Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
733<\/p>\n<\/td>\n
\n
32120.820000<\/p>\n<\/td>\n
\n
ML Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
736<\/p>\n<\/td>\n
\n
45164.684600<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
738<\/p>\n<\/td>\n
\n
299595.522966<\/p>\n<\/td>\n
\n
LL Road Frame<\/p>\n<\/td>\n<\/tr>\n
\n
739<\/p>\n<\/td>\n
\n
269874.009600<\/p>\n<\/td>\n
\n
HL Mountain Frame<\/p>\n<\/td>\n<\/tr>\n
\n
741<\/p>\n<\/td>\n
\n
141635.100000<\/p>\n<\/td>\n
\n
HL Mountain Frame<\/p>\n<\/td>\n<\/tr>\n
\n
742<\/p>\n<\/td>\n
\n
499556.572400<\/p>\n<\/td>\n
\n
HL Mountain Frame<\/p>\n<\/td>\n<\/tr>\n
\n
743<\/p>\n<\/td>\n
\n
901590.233600<\/p>\n<\/td>\n
\n
HL Mountain Frame<\/p>\n<\/td>\n<\/tr>\n
\n
744<\/p>\n<\/td>\n
\n
13765.920000<\/p>\n<\/td>\n
\n
HL Mountain Frame<\/p>\n<\/td>\n<\/tr>\n
\n
745<\/p>\n<\/td>\n
\n
106078.560000<\/p>\n<\/td>\n
\n
HL Mountain Frame<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
One thing you might notice about the results shown here, compared to the results shown in the preceding example, is that the function returns only one row for each product. Because a product is associated with only one product model in the SalesOrderDetail table, there will never be more than one row per product.<\/p>\n
Using the OUTER APPLY Operator<\/h1>\n
In the examples above, the CROSS APPLY operator returns the rows in the outer table for which the table value function returns data. As it turns out, the functions used in both examples return data for all rows in the outer tables. However, in some cases, the function will not return data for a specific row. If you still want the row from the outer table to be included in the result set, you should use the OUTER APPLY operator.<\/p>\n
The OUTER APPLY operator returns all rows from the outer table, whether or not the function returns data for a specific row. You use the OUTER APPLY operator just as you would CROSS APPLY; the difference is in the results. Let’s look at an example that demonstrates how this works.<\/p>\n
In the following statement, I create a function that returns data about product inventory:<\/p>\n
USE AdventureWorks2008\nGO\nIF OBJECT_ID (N'fn_inventory', N'IF') IS NOT NULL\n  DROP FUNCTION dbo.fn_inventory\nGO\nCREATE FUNCTION fn_inventory (@ProductID int)\nRETURNS TABLE\nAS\nRETURN\n(\n  SELECT\n    ProductID,\n    LocationID,\n    Quantity\n  FROM\n    Production.ProductInventory\n  WHERE\n    ProductID = @ProductID\n)\nGO\n<\/pre>\n
This statement, just like the CREATE FUNCTION statements used in the preceding examples, creates a table-valued function. The function returns three columns: ProductID, LocationID, and Quantity. I verified the function by using the following SELECT statement:<\/p>\n
SELECT LocationID, Quantity\nFROM fn_inventory(915)\n<\/pre>\n
In this case, I specified the product ID of 915 as the function’s parameter. The following table shows the results returned by the statement.<\/p>\n\n\n\n\n\n\n
\n
LocationID<\/b><\/p>\n<\/td>\n
\n
Quantity<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
6<\/p>\n<\/td>\n
\n
161<\/p>\n<\/td>\n<\/tr>\n
\n
50<\/p>\n<\/td>\n
\n
83<\/p>\n<\/td>\n<\/tr>\n
\n
60<\/p>\n<\/td>\n
\n
158<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
As you can see, product inventory exists in three locations, with different quantities at each location. Once the function has been verified, you can use it with a CROSS APPLY operator, as shown in the following statement:<\/p>\n
SELECT\n  p.ProductID,\n  p.Name,\n  fn.Quantity\nFROM\n  Production.Product AS p\nCROSS APPLY\n  fn_inventory(p.ProductID) AS fn\nORDER BY p.ProductID\n<\/pre>\n
Notice that this statement is similar to the previous examples. I’ve simply used the CROSS APPLY operator to join the Product table to the fn_inventory function. The following table shows part of the data returned by the statement. As the results indicate, there are multiple rows for each product, one for each quantity.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
ProductID<\/b><\/p>\n<\/td>\n
\n
Name<\/b><\/p>\n<\/td>\n
\n
Quantity<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
915<\/p>\n<\/td>\n
\n
ML Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
161<\/p>\n<\/td>\n<\/tr>\n
\n
915<\/p>\n<\/td>\n
\n
ML Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
83<\/p>\n<\/td>\n<\/tr>\n
\n
915<\/p>\n<\/td>\n
\n
ML Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
158<\/p>\n<\/td>\n<\/tr>\n
\n
916<\/p>\n<\/td>\n
\n
HL Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
425<\/p>\n<\/td>\n<\/tr>\n
\n
916<\/p>\n<\/td>\n
\n
HL Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
288<\/p>\n<\/td>\n<\/tr>\n
\n
916<\/p>\n<\/td>\n
\n
HL Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
276<\/p>\n<\/td>\n<\/tr>\n
\n
921<\/p>\n<\/td>\n
\n
Mountain Tire Tube<\/p>\n<\/td>\n
\n
286<\/p>\n<\/td>\n<\/tr>\n
\n
921<\/p>\n<\/td>\n
\n
Mountain Tire Tube<\/p>\n<\/td>\n
\n
243<\/p>\n<\/td>\n<\/tr>\n
\n
922<\/p>\n<\/td>\n
\n
Road Tire Tube<\/p>\n<\/td>\n
\n
264<\/p>\n<\/td>\n<\/tr>\n
\n
922<\/p>\n<\/td>\n
\n
Road Tire Tube<\/p>\n<\/td>\n
\n
241<\/p>\n<\/td>\n<\/tr>\n
\n
923<\/p>\n<\/td>\n
\n
Touring Tire Tube<\/p>\n<\/td>\n
\n
262<\/p>\n<\/td>\n<\/tr>\n
\n
923<\/p>\n<\/td>\n
\n
Touring Tire Tube<\/p>\n<\/td>\n
\n
240<\/p>\n<\/td>\n<\/tr>\n
\n
928<\/p>\n<\/td>\n
\n
LL Mountain Tire<\/p>\n<\/td>\n
\n
240<\/p>\n<\/td>\n<\/tr>\n
\n
928<\/p>\n<\/td>\n
\n
LL Mountain Tire<\/p>\n<\/td>\n
\n
369<\/p>\n<\/td>\n<\/tr>\n
\n
929<\/p>\n<\/td>\n
\n
ML Mountain Tire<\/p>\n<\/td>\n
\n
385<\/p>\n<\/td>\n<\/tr>\n
\n
929<\/p>\n<\/td>\n
\n
ML Mountain Tire<\/p>\n<\/td>\n
\n
284<\/p>\n<\/td>\n<\/tr>\n
\n
930<\/p>\n<\/td>\n
\n
HL Mountain Tire<\/p>\n<\/td>\n
\n
267<\/p>\n<\/td>\n<\/tr>\n
\n
930<\/p>\n<\/td>\n
\n
HL Mountain Tire<\/p>\n<\/td>\n
\n
232<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The results shown in the table are those returned for product IDs 915 through 930. As you would expect, each row includes data returned from both the table and the function. Now let’s look at an example that uses OUTER APPLY, rather than CROSS APPLY:<\/p>\n
SELECT\n  p.ProductID,\n  p.Name,\n  fn.Quantity\nFROM\n  Production.Product AS p\nOUTER APPLY\n  fn_inventory(p.ProductID) AS fn\nORDER BY p.ProductID\n<\/pre>\n
This statement is exactly like the previous one, except for the APPLY operator. However, the results are slightly different, as shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
ProductID<\/b><\/p>\n<\/td>\n
\n
Name<\/b><\/p>\n<\/td>\n
\n
Quantity<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
915<\/p>\n<\/td>\n
\n
ML Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
161<\/p>\n<\/td>\n<\/tr>\n
\n
915<\/p>\n<\/td>\n
\n
ML Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
83<\/p>\n<\/td>\n<\/tr>\n
\n
915<\/p>\n<\/td>\n
\n
ML Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
158<\/p>\n<\/td>\n<\/tr>\n
\n
916<\/p>\n<\/td>\n
\n
HL Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
425<\/p>\n<\/td>\n<\/tr>\n
\n
916<\/p>\n<\/td>\n
\n
HL Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
288<\/p>\n<\/td>\n<\/tr>\n
\n
916<\/p>\n<\/td>\n
\n
HL Touring Seat\/Saddle<\/p>\n<\/td>\n
\n
276<\/p>\n<\/td>\n<\/tr>\n
\n
917<\/p>\n<\/td>\n
\n
LL Mountain Frame – Silver, 42<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
918<\/p>\n<\/td>\n
\n
LL Mountain Frame – Silver, 44<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
919<\/p>\n<\/td>\n
\n
LL Mountain Frame – Silver, 48<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
920<\/p>\n<\/td>\n
\n
LL Mountain Frame – Silver, 52<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
921<\/p>\n<\/td>\n
\n
Mountain Tire Tube<\/p>\n<\/td>\n
\n
286<\/p>\n<\/td>\n<\/tr>\n
\n
921<\/p>\n<\/td>\n
\n
Mountain Tire Tube<\/p>\n<\/td>\n
\n
243<\/p>\n<\/td>\n<\/tr>\n
\n
922<\/p>\n<\/td>\n
\n
Road Tire Tube<\/p>\n<\/td>\n
\n
264<\/p>\n<\/td>\n<\/tr>\n
\n
922<\/p>\n<\/td>\n
\n
Road Tire Tube<\/p>\n<\/td>\n
\n
241<\/p>\n<\/td>\n<\/tr>\n
\n
923<\/p>\n<\/td>\n
\n
Touring Tire Tube<\/p>\n<\/td>\n
\n
262<\/p>\n<\/td>\n<\/tr>\n
\n
923<\/p>\n<\/td>\n
\n
Touring Tire Tube<\/p>\n<\/td>\n
\n
240<\/p>\n<\/td>\n<\/tr>\n
\n
924<\/p>\n<\/td>\n
\n
LL Mountain Frame – Black, 42<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
925<\/p>\n<\/td>\n
\n
LL Mountain Frame – Black, 44<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
926<\/p>\n<\/td>\n
\n
LL Mountain Frame – Black, 48<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
927<\/p>\n<\/td>\n
\n
LL Mountain Frame – Black, 52<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
928<\/p>\n<\/td>\n
\n
LL Mountain Tire<\/p>\n<\/td>\n
\n
240<\/p>\n<\/td>\n<\/tr>\n
\n
928<\/p>\n<\/td>\n
\n
LL Mountain Tire<\/p>\n<\/td>\n
\n
369<\/p>\n<\/td>\n<\/tr>\n
\n
929<\/p>\n<\/td>\n
\n
ML Mountain Tire<\/p>\n<\/td>\n
\n
385<\/p>\n<\/td>\n<\/tr>\n
\n
929<\/p>\n<\/td>\n
\n
ML Mountain Tire<\/p>\n<\/td>\n
\n
284<\/p>\n<\/td>\n<\/tr>\n
\n
930<\/p>\n<\/td>\n
\n
HL Mountain Tire<\/p>\n<\/td>\n
\n
267<\/p>\n<\/td>\n<\/tr>\n
\n
930<\/p>\n<\/td>\n
\n
HL Mountain Tire<\/p>\n<\/td>\n
\n
232<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The result set now includes rows for products 917 through 920 and products 924 through 927, which were not included in the previous results. Notice that the Quantity column shows a NULL value for each of the new rows. By using the OUTER APPLY operator, we’re able to return all rows from the outer table, whether or not the function returns any rows.<\/p>\n
As you can see, the APPLY operator can be a useful tool when you want to evoke a table-valued function for each row returned by a table expression (the outer table). You simply use the operator to join the outer table to the function. If you want to include only those rows from the outer table for which the function returns data, use the CROSS APPLY operator. If you want to return all rows from the outer table, regardless of whether or not the function returns data for a row, use the OUTER APPLY operator. For more details about either form of the APPLY operator and to see additional examples, check out the topic “Using APPLY” in SQL Server Books Online<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
One of the most interesting additions to SQL Server syntax in SQL Server 2005 was the APPLY operator. It allows several queries that were previously impossible. It is surprisingly difficult to find a simple explanation of what APPLY actually does. Rob Sheldon is the specialist in simple explanations, so we asked him.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,5018,4150,5134,4151,5195,4183,4252],"coauthors":[6779],"class_list":["post-891","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-robert-sheldon","tag-sql","tag-sql-prompt","tag-sql-server","tag-sql-server-apply-basics","tag-t-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/891","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=891"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/891\/revisions"}],"predecessor-version":[{"id":40331,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/891\/revisions\/40331"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=891"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=891"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}