Database consultancy: CROSS APPLY

This article is dedicated to the operator APPLY, and more specifically its type CROSS APPLY. This statement was first introduced in SQL Server 2005, but unfortunately many have not learned how to use it, maybe it’s because of the fact that the BOL (SQL ServerBooksOnline) this operator is not well and has a very “dry” examples of its use. In this article, I’ll show some interesting demonstrations, where the operator can come in handy.

The main feature of the operator is that APPLY allows you to invoke a table function for each row returned by the outer table expression query. It is this example is in BOL.

CROSS APPLY operator returns only the rows from the outer table that creates a result set from a table-valued function. OUTER APPLY operator and returns the lines that form the result set, and the lines that do not, with NULL values ??in the columns created a table-valued function.

To demonstrate some of fitch operator APPLY, create a test database and a couple of tables

-- USE master
GO

-- Create test db
IFDB_ID( 'CrossApplyTest') IS NOT NULL
BEGIN
DROP DATABASE CrossApplyTest
END
GO

-- CREATE DATABASE CrossApplyTest
GO

-- USE CrossApplyTest
GO

-- Create test table of countries
IFOBJECT_ID( 'dbo.Countries', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Countries
END
GO

CREATE TABLE dbo.Countries (CountryID int, Country nvarchar(255) )
GO

-- Add a few counties into table Countries
INSERT INTO dbo.Countries (CountryID, Country)
VALUES( 1, N'USA'), ( 2, N'Canada'), ( 3, N'France')
GO

--Create test table of cities
IFOBJECT_ID( 'dbo.Cities', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Cities
END
GO

CREATE TABLEdbo.Cities (CityID int, CountryID int, City nvarchar(255))
GO

--Add a few cities into table
INSERT INTOdbo.Cities (CityID, CountryID, City)
VALUES( 1, 1, N'New York'), ( 2, 1, N'Miami'), ( 3, 1, N'Chicago')
, ( 4, 2, N'Toronto'), ( 5, 2, N'Ottawa'), ( 6, 2, N'Montreal')
, ( 7, 3, N'Paris'), ( 8, 3, N'Lyon'), ( 9, 3, N'Marseille')
GO

The result of the function call is as follows:

SELECT * FROM dbo.GetCities (3)

7   Paris
8   Lyon
9   Marseille

Now, using the APPLY operator with a list of cities for each country from the table Countries:

SELECT gc.CityID, gc.City, c.Country
FROM dbo.Countries c
CROSS APPLY dbo.GetCities (c.CountryID) gc

1   New York   USA
2   Miami   USA
3   Chicago   USA
4   Toronto   Canada
5   Ottawa   Canada
6   Montreal   Canada
7   Paris   France
8   Lyon   France
9   Marseille   France

So, very convenient, but a similar example is described in BOL. Next you`ll see how can still use the operator CROSS APPLY. For example, how to withdraw from 3 cities in each country, sorted alphabetically? Using APPLY operator is relatively easy:

SELECT gc.City, c.Country
FROM dbo.Countries c
CROSS APPLY (SELECT TOP 1 City
FROM dbo.Cities
WHERE CountryID = c.CountryID
ORDER BY City
) gc

Chicago  USA
Montreal  Canada
Lyon  France 

Now try to further complicate our request. We derive the first letter of each of the 3 cities in each country and the total number of characters of any city in the current country:

SELECT gc.City, c.Country, lt.*
FROM dbo.Countries c
CROSS APPLY (SELECT TOP 1 City
FROM dbo.Cities
WHERE CountryID = c.CountryID
ORDER BY City
) gc
CROSS APPLY (SELECT l 'Letter'
FROM
(SELECT LEFT(gc.City, 1) l
FROM dbo.Cities WHERE CountryID = c.CountryID
) t
GROUP BY l
) lt

Chicago   USA   C
Montreal   Canada   M
Lyon   France   L 

As you can see the operator CROSS APPLY very useful