What are SQL Cross Apply and Outer Apply in SQL Server

In this article, we will learn into the “APPLY” operator and its variation- SQL Cross apply and outer apply in SQL Server along with an example of How they can be used. therefore, the SQL server supports table-valued functions, let’s know what are the functions that return data in the form of a table.
so as you know JOIN operations are used in SQL Server to join two or more tables, furthermore, however, JOIN operations cannot be used to join a table with the output of a table-valued function.
therefore, APPLY operation is used for these purposes.
So, in this article, we will learn about SQL CROSS APPLY and SQL OUTER APPLY operators, and also we will see how they are implemented by practically with the help of some examples, and also we will discuss how they differ from each other.

There are two types of APPLY operators::
SQL CROSS APPLY
SQL OUTER APPLY

When Should We Use The CROSS APPLY Statement

Therefore, the SQL CROSS APPLY statement behaves as a similar fashion to a correlated subquery but allows us to use ORDER BY statements within the subquery. furthermore, it is very useful where we wish to extract the top record from a subquery to use in an outer subquery.

The SQL CROSS APPLY is also used when we wish to pass values into a User Defined Table-Valued Function.

SQL CROSS APPLY

Therefore, The CROSS APPLY operator is semantically similar to INNER JOIN, furthermore, it retrieves those records from the table-valued function, and therefore the table being joined, in that case when it finds matching rows between the two.
Therefore we can say that the SQL CROSS APPLY returns from the outer table (i.e. table on the left of the Apply operator) that produces matching values from the table-valued function (i.e which is on the right side of the operator).
The SQL server Cross Apply is equivalent or similar to Inner Join but it works with a table-valued function.

Example:

let’s see the working view of the SQL Cross Apply operator, first, we shall create two tables namely TeacherDeatils and TechSalary.
there are the given below is the schema and create a query for TeacherDetails.

CREATE TABLE TeacherDetails (
      TechId int PRIMARY KEY,
      TechFirstName VARCHAR(60),
      TechLastName VARCHAR(60),
      Subject VARCHAR(60),
      SubjectID INT
    )

CREATE TABLE TeacherDetails (

TechId int PRIMARY KEY,

TechFirstName VARCHAR(60),

TechLastName VARCHAR(60),

Subject VARCHAR(60),

SubjectID INT

)

The data in the TeacherDetails> table is provided below:-

	TechId	TechFirstName	TechLastName	Subject	SubjectID
1	101	Ronan	Brayden	Computer Science	11
2	102	Hugo	Diego	Computer Science	11
3	103	Antonio	Bryant	Account	13
4	104	Steffan	Mark	HR	12
5	105	Devin	Pollo	Mathematics	17
6	106	Ethan	Sam	Mathematics	17
7	107	Aaron	Joshi	Science	19
8	108	Nilesh	Mathur	IT	16
9	109	Aman	Chaudhary	Biology	14
10	110	Soumya	Tripathi	English	15
11	111	Sachin	Shukla	History	22
12	112	Anoop	Dubey	Sanskrit	21
13	113	Ritu	Thappa	social science	25
14	114	Nilesh	Arya	Data Structure	23
15	115	Shyam	Singh	Yoga	24

Now Create another table TechSalary

CREATE TABLE TechSalary (
      TechID INT,
      TechFullName VARCHAR(80),
      TechSalary INT,
      TechWorkingYears INT,
      SubjectID INT
    )

CREATE TABLE TechSalary (

TechID INT,

TechFullName VARCHAR(80),

TechSalary INT,

TechWorkingYears INT,

SubjectID INT

)

	TechId	TechFullName	TechSalary	TechWorkingYears	SubjectID
1	101	Ronan Brayden	35000	4	11
2	102	Hugo Diego	32000	3	11
3	103	Antonio Bryant	25000	3	13
4	104	Steffan Mark	45000	8	12
5	105	Devin Pollo	40000	4	5
6	106	Ethan Sam	25000	2	17
7	107	Aaron Joshi	28000	4	19
8	108	Nilesh Mathur	34000	6	16
9	109	Aman Chaudhary	41000	7	14
10	110	Soumya Tripathi	55000	9	15
11	111	Sachin Shukla	60000	11	22
12	112	Anoop Dubey	32000	8	21
13	113	Ritu Thappa	45000	6	25
14	114	Nilesh Arya	29000	2	23
15	115	Shyam Singh	22000	1	24

Therefore, we will create a user-defined function of SQL server with the name fn_Salaryinc, so, these function returns the output with increased salary by Rs.3000 on the basis of SubjectID column.

SQL Query

CREATE FUNCTION fn_Salaryinc (@SubjectID int)
    RETURNS TABLE
    AS
    RETURN
    (
      SELECT 
        TechID, TechFullName,
        TechSalary+3000 AS Salaryinc
      FROM
        Techsalary
      WHERE
       SubjectID = @SubjectID 
  )
    GO

CREATE FUNCTION fn_Salaryinc (@SubjectID int)

RETURNS TABLE

RETURN

(

SELECT

TechID, TechFullName,

TechSalary+3000 AS Salaryinc

FROM

Techsalary

WHERE

SubjectID = @SubjectID

)

Furthermore, the use function fn_Salaryinc to get an increased salary.

SELECT TechID, Salaryinc FROM fn_Salaryinc(11)

1	SELECT TechID, Salaryinc FROM fn_Salaryinc(11)

so, after applying the above function the salary got increased by 3000. furthermore, the given below table shows the output for the function fn_Salaryinc

	TechId	Salaryinc
1	101	38000
1	101	35000

furthermore, the function is giving the desired outputs we will write a Cross apply Query

SELECT
      e.TechFirstName,
      e.TechLastName,
      f.Salaryinc
    FROM
      TeacherDetails AS e
    CROSS APPLY
      fn_Salaryinc  (e.SubjectID) AS f

SELECT

e.TechFirstName,

e.TechLastName,

f.Salaryinc

FROM

TeacherDetails AS e

CROSS APPLY

fn_Salaryinc (e.SubjectID) AS f

SQL CROSS APPLY output

SQL OUTER APPLY

on another side, OUTER APPLY retrieves all the records from both table-valued functions and tables irrespective of the matched. Let’s
understand another word, The OUTER APPLY resembles LIFT JOIN but has an ability to join-evaluated functions with SQL the tables, therefore the SQL OUTER APPLY final output contains all records from the left-side table or table-evaluated function, so even if they do not match with the records in the table-valued function or right tables.
The SQL OUTER APPLY is equivalent to left Outer JOin, but the condition is that it works with a table-valued function.

Example:

Let us first create a table TEacherDetails with the given below query.

CREATE TABLE TeacherDetails (
      TechId int PRIMARY KEY,
      TechFirstName VARCHAR(60),
      TechLastName VARCHAR(60),
      Subject VARCHAR(60),
      SubjectID INT
    )

CREATE TABLE TeacherDetails (

TechId int PRIMARY KEY,

TechFirstName VARCHAR(60),

TechLastName VARCHAR(60),

Subject VARCHAR(60),

SubjectID INT

)

therefore, the given below table data is the TeacherDetails

	TechId	TechFirstName	TechLastName	Subject	SubjectID
1	101	Ronan	Brayden	Computer Science	11
2	102	Hugo	Diego	Computer Science	11
3	103	Antonio	Bryant	Account	13
4	104	Steffan	Mark	HR	12
5	105	Devin	Pollo	Mathematics	17
6	106	Ethan	Sam	Mathematics	17
7	107	Aaron	Joshi	Science	19
8	108	Nilesh	Mathur	IT	16
9	109	Aman	Chaudhary	Biology	14
10	110	Soumya	Tripathi	English	15
11	111	Sachin	Shukla	History	22
12	112	Anoop	Dubey	Sanskrit	21
13	113	Ritu	Thappa	social science	25
14	114	Nilesh	Arya	Data Structure	23
15	115	Shyam	Singh	Yoga	24

Now, Create another table TeacherProject

CREATE TABLE TeacherProject (
          TechID INT,
          SubjectName VARCHAR(90),
          SubjectID INT,
          ProjectName VARCHAR(90),
          ProjectID VARCHAR(60)
    )

CREATE TABLE TeacherProject (

TechID INT,

SubjectName VARCHAR(90),

SubjectID INT,

ProjectName VARCHAR(90),

ProjectID VARCHAR(60)

)

Therefore, The given below table for TeacherProject data

	TechId	SubjectName	SubjectID	ProjectName	ProjectID
1	101	Computer Science	11	SQL	CS01
2	102	Computer Science	11	PHP	IT02
3	103	Account	13	Financial Report	AC04
4	104	HR	12	Interview Process	HR06
5	105	Mathematics	17	Vector Sequence	M007
6	106	Mathematics	17	Time and Distance	M009
7	107	Science	19	Chemistry	S010
8	108	IT	16	Logical Operator	IT11
9	109	Biology	14	Human Body	B013
10	110	English	15	Tense	E014
11	111	History	22	Battle of place War	H117
12	112	Sanskrit	21	Essay	S118
13	113	Social Science	25	Nature Environment	SS21
14	114	Data structure	23	Complete Graph	DS25
15	115	Yoga	24	Stomach Yog	Y126

Furthermore, let us create a function named fn_Projectppt, the function returns the column from TeacherProject table after producing SubjectID as a parameter.

SQL Query

CREATE FUNCTION fn_Projectppt(@SubjectID INT)
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT
            TechID,
            SubjectName,
            ProjectName
        FROM
            TeacherProject
        WHERE
            SubjectID = @SubjectID
    )
    GO

CREATE FUNCTION fn_Projectppt(@SubjectID INT)

RETURNS TABLE

RETURN

(

SELECT

TechID,

SubjectName,

ProjectName

FROM

TeacherProject

WHERE

SubjectID = @SubjectID

)

Therefore, let’s run the given below query to verify the results

SELECT  * FROM fn_Projectppt(11)

1	SELECT * FROM fn_Projectppt(11)

	TechID	SubjectName	ProjectName
1	101	Computer Science	SQL
2	102	Computer Science	PHP

Furthermore, We will write an SQL OUTER APPLY Query in order to check the functioning and the Output of the operator.

SELECT 
        e.TechID, 
        e.TechFirstName ,
        fn.SubjectName, 
        fn.ProjectName 
    FROM
 	    TeacherDetails e 
    OUTER APPLY
  	    fn_Projectppt (e.SubjectID) AS fn

SELECT

e.TechID,

e.TechFirstName ,

fn.SubjectName,

fn.ProjectName

FROM

TeacherDetails e

OUTER APPLY

fn_Projectppt (e.SubjectID) AS fn

outer apply sql output

Preparing Dummy Database and Records

Therefore, for preparing dummy databases and data first, we create a database with dummy records in it. we will use these dummy database to perform the different operations.
furthermore, let’s execute the following script:

CREATE DATABASE CarShowroom
 
GO 
 
USE CarShowroom;
 
CREATE TABLE CarBrand
(
    id INT PRIMARY KEY,
    CarBrand_name VARCHAR(60) NOT NULL,
    
 )
 
CREATE TABLE Car
(
    id INT PRIMARY KEY,
    Car_name VARCHAR(50) NOT NULL,
    price INT NOT NULL,
    CarBrand_id INT NOT NULL
   
 )
 
USE CarShowroom;
 
 INSERT INTO CarBrand 
 
VALUES
(1, 'Maruti Suzuki'),
(2, 'Hyundai India'),
(3, 'Tata Motos'),
(4, 'Toyota'),
(5, 'Renault'),
(6, 'Nissan'),
(7, 'Volkswagen')
 
 
INSERT INTO Car 
 
VALUES
(1, 'Vitara Brezza',110000, 1),
(2, 'Hyundai Creta',900000, 2),
(3, 'EVision Electric',250000, 3),
(4, 'Innova Crysta',150000, 4),
(5, 'Ertiga',800000, 1),
(6, 'Toyota Glanza',120000, 3)

CREATE DATABASE CarShowroom

USE CarShowroom;

CREATE TABLE CarBrand

(

id INT PRIMARY KEY,

CarBrand_name VARCHAR(60) NOT NULL,

)

CREATE TABLE Car

(

id INT PRIMARY KEY,

Car_name VARCHAR(50) NOT NULL,

price INT NOT NULL,

CarBrand_id INT NOT NULL

)

USE CarShowroom;

INSERT INTO CarBrand

VALUES

(1, 'Maruti Suzuki'),

(2, 'Hyundai India'),

(3, 'Tata Motos'),

(4, 'Toyota'),

(5, 'Renault'),

(6, 'Nissan'),

(7, 'Volkswagen')

INSERT INTO Car

VALUES

(1, 'Vitara Brezza',110000, 1),

(2, 'Hyundai Creta',900000, 2),

(3, 'EVision Electric',250000, 3),

(4, 'Innova Crysta',150000, 4),

(5, 'Ertiga',800000, 1),

(6, 'Toyota Glanza',120000, 3)

therefore, in this script, which has created a database named carShowroom. The database has two tables such as CarBrand and car. the car has a CarBrand_id column which contains values from the id column of the carBrand Table. therefore, this means that there is a one to many relationships between the CarBrand and Car columns.

1. Joining tables using JOIN operators

Therefore, Let’s first we will use the INNER JOIN operator to retrieve matching rows from both of the tables.
Execute the following script:

SELECT A.CarBrand_name, B.id, B.Car_name, B.price
FROM CarBrand A
INNER JOIN Car B
ON A.id = B.CarBrand_id

SELECT A.CarBrand_name, B.id, B.Car_name, B.price

FROM CarBrand A

INNER JOIN Car B

ON A.id = B.CarBrand_id

Inner Join operator
furthermore, as you can see that only those records have been selected from the CarBrand table where there is a matching row in the Car table. so, to retrieve all those records from CarBrandr table LEFT JOIN can be used.

SELECT A.CarBrand_name, B.id, B.Car_name, B.price
FROM CarBrand A
LEFT JOIN Car B
ON A.id = B.CarBrand_id

SELECT A.CarBrand_name, B.id, B.Car_name, B.price

FROM CarBrand A

LEFT JOIN Car B

ON A.id = B.CarBrand_id

so, the output of the given above query, which looks like as:

SQL CROSS APPLY
Therefore, you will see that all the records are retrieved from the CarBrand table, therefore, the irrespective of there being any matching rows in the Car table.

2. Joining table-valued functions with tables using APPLY operators

here, we will see that, how JOIN Operators join the results from the two tables, furthermore, However, as mentioned above they can’t be used to join a table-valued function with a table. Therefore, we can say that a table-valued function is a function that returns records in the form of a table.
let’s first we write a simple table-valued function that accepts CarBrand id as a parameter and also returns all the cars which are available in the car showroom.
therefore, execute the given below the following script.

CREATE FUNCTION fnGetcarsByCarBrandId(@CarBrandId int)
RETURNS TABLE
AS
RETURN
( 
SELECT * FROM Car
WHERE CarBrand_id = @CarBrandId
)

CREATE FUNCTION fnGetcarsByCarBrandId(@CarBrandId int)

RETURNS TABLE

RETURN

(

SELECT * FROM Car

WHERE CarBrand_id = @CarBrandId

)

furthermore, let’s us test the above-given function, here we will pass 1 as the CarBrand id to the fnGetcarsByCarBrandId function. so, let’s us execute the following the script:

SELECT * FROM fnGetcarsByCarBrandId(1)

1	SELECT * FROM fnGetcarsByCarBrandId(1)

Table valued function CRoss apply
Let’s us try to use an SQL INNER JOIN operator to join the CarBrand table with the table valued function fnGetcarsByCarBrandId.
so, let’s take a look at the given below following script:

SELECT A.CarBrand_name, B.id, B.Car_name, B.price
FROM CarBrand A
INNER JOIN fnGetcarsByCarBrandId(A.Id) B
ON A.id = B.CarBrand_id

SELECT A.CarBrand_name, B.id, B.Car_name, B.price

FROM CarBrand A

INNER JOIN fnGetcarsByCarBrandId(A.Id) B

ON A.id = B.CarBrand_id

therefore, here we are using the SQL INNER JOIN operator to join a CarBrand (physical table) with a table-valued function fnGetcarsByCarBrandId. furthermore, then all the ids from the CarBrand table are passed to the function. so, however above given script, givens an error, which looks like this:

3. Joining table and table-valued function using SQL CROSS APPLY

Let us see how to use the SQL CROSS Apply operator to join the CarBrand table with the table-valued function fnGetcarsByCarBrandId. therefore, the CROSS APPLY operator is semantically similar to INNER JOIN. it retrieves all the records from the table where are corresponding matching rows in the output returned by the table-valued function.
take a look given below the following script:

SELECT A.CarBrand_name, B.id, B.Car_name, B.price
FROM CarBrand A
CROSS APPLY fnGetcarsByCarBrandId(A.Id) B

SELECT A.CarBrand_name, B.id, B.Car_name, B.price

FROM CarBrand A

CROSS APPLY fnGetcarsByCarBrandId(A.Id) B

Therefore, as given above in the script, all the ids from the CarBrand table are being passed to fnGetcarsByCarBrandId function. furthermore, for each id in the CarBrand table, so, the function returns corresponding records data from the Car table. The result of this table-valued function is being joined with table CarBrand.
The output comes as the given above script looks like this:

Table-Valued Function using Cross apply output
Therefore the SQL CROSS APPLY returns only those records from the CarBrand (physical) table, where there are matching rows in the output of the TVF (Table-Valued Function), so we can say that this is similar to the INNER JOIN operation.

4.Joing table and Table-valued Functions using OUTER APPLY

In Joining table and table-valued functions using SQL OUTER APPLY, when to retrieve all the rows from both the physical table and the output of the table-valued function, OUTER APPLY is used. furthermore, we can say that the OUTER APPLY is semantically similar to the OUTER JOIN operation.

Take a look at the following given below script to see OUTER APPLY in action.

SELECT A.CarBrand_name, B.id, B.Car_name, B.price
FROM CarBrand A
OUTER APPLY fnGetcarsByCarBrandId(A.Id) B

SELECT A.CarBrand_name, B.id, B.Car_name, B.price

FROM CarBrand A

OUTER APPLY fnGetcarsByCarBrandId(A.Id) B

outer apply operation
therefore, we can see that all the records from the CarBrand table have been retrieved irrespective of the matching rows in the output from the table valued function fnGetcarsByCarBrandId.

Conclusion

In this article, we can learn what the SQL CROSS APPLY and SQL OUTER APPLY functions and how they can be used to perform SQL JOIN operations between a physical table and table-valued function.
furthermore, we first used to JOIN operators to join two physical tables. so, in this, article we then explained how JOIN operators can be replaced by APPLY operators in order to achieve the same results by joining a physical table with the output of a table-valued function.

What are SQL Cross Apply and Outer Apply in SQL Server

When Should We Use The CROSS APPLY Statement

SQL CROSS APPLY

Example:

SQL Query

SQL OUTER APPLY

Example:

SQL Query

Preparing Dummy Database and Records

1. Joining tables using JOIN operators

2. Joining table-valued functions with tables using APPLY operators

3. Joining table and table-valued function using SQL CROSS APPLY

4.Joing table and Table-valued Functions using OUTER APPLY

Conclusion

Related Posts

About Author

admin