In SQL Server, we can join two tables with JOIN operators. You can read the “Join Types in SQL Server” article.
With the CROSS APPLY and OUTER APPLY operators, we can join a table and a result set returned by a table-valued function.
Difference Between CROSS APPLY and OUTER APPLY:
In the CROSS APPLY operator, only the matching records between the two sources(a table and a table valued function) returns. That is, like INNER JOIN.
In the OUTER APPLY operator, all records in the table returns as a result even if they do not match the result set of the table valued function. That is, like LEFT OUTER JOIN.
To better understand the CROSS APPLY and OUTER APPLY operators, let’s first create two sample tables and insert a few records into these tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE City( ID int, CityName varchar(100) ) CREATE TABLE FamousFood( ID int, CityID int, FoodName varchar(100) ) INSERT INTO [dbo].[City]([ID],[CityName])VALUES (1,'Newyork') INSERT INTO [dbo].[City]([ID],[CityName])VALUES (42,'Roma') INSERT INTO [dbo].[City]([ID],[CityName])VALUES (3,'Paris') GO INSERT INTO [dbo].[FamousFood]([ID],[CityID],[FoodName])VALUES (1,1,'x') INSERT INTO [dbo].[FamousFood]([ID],[CityID],[FoodName])VALUES (2,42,'y') INSERT INTO [dbo].[FamousFood]([ID],[CityID],[FoodName])VALUES (3,3,'z') |
Now let’s create a table-valued function that reads records from the FamousFood table according to the CityID column. We will use this table valued function in CROSS APPLY and OUTER APPLY examples.
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION Get_Famous_Food_Using_CityID(@CityID int) RETURNS TABLE AS RETURN ( SELECT * FROM [dbo].[FamousFood] WHERE CityID = @CityID ) |
Get_Famous_Food_Using_CityID table valued fonksiyonunu kullanarak CityID değeri 42 olan kayıtları listeleyelim.
Let’s list the records with the CityID value “42” using the Get_Famous_Food_Using_CityID table valued function.
|
1 |
SELECT * FROM Get_Famous_Food_Using_CityID(42) |
CROSS APPLY and INNER JOIN:
First, let’s join these two tables with the inner join method. A result returns as follows.
|
1 2 3 4 |
SELECT c.ID as CityID,c.CityName, ff.id FamousFoodID, ff.FoodName FROM [dbo].[City] c INNER JOIN [dbo].[FamousFood] ff ON c.ID = ff.CityID |
Then use the CROSS APPLY operator to join the City table and the Get_Famous_Food_Using_CityID table valued function.
|
1 2 3 |
SELECT c.ID as CityID,c.CityName, ff.id FamousFoodID, ff.FoodName FROM [dbo].[City] c CROSS APPLY Get_Famous_Food_Using_CityID(c.ID) ff |
As you can see, we get the same result as the join of the City and FamousFood tables, by joining the City table and the Get_Famous_Food_Using_CityID table valued function with the CROSS APPLY operator.
LEFT OUTER JOIN and OUTER APPLY:
First, we add a record to City and FamousFood tables that are not related to each other. Thus, we aim to have mismatched records.
|
1 2 |
INSERT INTO [dbo].[City]([ID],[CityName])VALUES (5,'Luxemburg') INSERT INTO [dbo].[FamousFood]([ID],[CityID],[FoodName])VALUES (4,37,'k') |
And we join the two tables with the FULL OUTER JOIN method as follows.
|
1 2 3 4 |
SELECT c.ID as CityID,c.CityName, ff.id FamousFoodID, ff.FoodName FROM [dbo].[City] c FULL OUTER JOIN [dbo].[FamousFood] ff ON c.ID = ff.CityID |
As you can see, when we join the two tables with the FULL OUTER JOIN method, all records in the two tables returned as a result, although the records did not match.
When we join two tables with the LEFT OUTER JOIN method, as you can see below, all records in the City table return as a result even if they don’t match the records in the FamousFood table. However, records in the FamousFood table that does not match the records in the City table does not return as a result.
|
1 2 3 4 |
SELECT c.ID as CityID,c.CityName, ff.id FamousFoodID, ff.FoodName FROM [dbo].[City] c LEFT OUTER JOIN [dbo].[FamousFood] ff ON c.ID = ff.CityID |
We can do the same by using the OUTER APPLY operator as follows.
|
1 2 3 |
SELECT c.ID as CityID,c.CityName, ff.id FamousFoodID, ff.FoodName FROM [dbo].[City] c OUTER APPLY Get_Famous_Food_Using_CityID(c.ID) ff |
