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