Do CROSS
APPLY and INNER JOIN produces the same result set?
I have been
talking with many of my DB friends, and they have come up with a question of
comparing the CROSS APPLY and INNER JOIN. The APPLY is an SQL Server operator
that was introduced in 2005 for comparing the expressions on table A column with
the expressions on the table B column. I have come up with a scenario of usage of both CROSS APPLY and INNER JOIN to make the comparison and understanding easier.
CROSS APPLY
The usage
of CROSS APPLY is very simple, which returns the expressions that are matched between the left table and the right table or vice versa.
INNER JOIN
The usage
of INNER JOIN is simpler than that of a CROSS APPLY, where it returns the same
result set as that of a CROSS APPLY. The result set is nothing but the matching
expressions between the left table and the right table or vice versa.
Let’s take
an example of CROSS APPLY and INNER JOIN for comparison
1. Create a new table as Hospital
CREATE TABLE [Hospital](
[HospitalID] [int] NOT NULL PRIMARY KEY,
[Name] VARCHAR(250) NOT NULL,
)
ON [PRIMARY]
2. Create a new table as Doctors
CREATE TABLE [Doctors](
[DocID] [int] NOT NULL PRIMARY KEY,
[FName] VARCHAR(250) NOT NULL,
[LName] VARCHAR(250) NOT NULL,
[HospitalID] [int] NOT NULL REFERENCES [Hospital](HospitalID),
)
ON [PRIMARY]
3. Insert values into Hospital table
INSERT [Hospital] ([HospitalID], [Name])
VALUES (1, N'Hospital1')
INSERT [Hospital] ([HospitalID], [Name])
VALUES (2, N'Hospital2')
INSERT [Hospital] ([HospitalID], [Name])
VALUES (3, N'Hospital3')
INSERT [Hospital] ([HospitalID], [Name])
VALUES (4, N'Hospital4')
INSERT [Hospital] ([HospitalID], [Name])
VALUES (5, N'Hospital5')
INSERT [Hospital] ([HospitalID], [Name])
VALUES (6, N'Hospital6')
GO
4. Insert values into Doctors table
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (1, N'Pradeep', N'TS', 1 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (2, N'Johnson', N'Hall', 2 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (3, N'Tom', N'Krueger', 3 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (4, N'Bill', N'Hokkins', 3 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (5, N'Kim', N'Bills', 3 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (6, N'James', N'Clark', 3 )
Comparing
CROSS APPLY and INNER JOIN
We’ll try
to implement the CROSS APPLY operator between the Hospital table and the Doctors
table and check the results with the execution plan.
SELECT * FROM Hospital H
CROSS APPLY
(
SELECT * FROM DOCTORS D
WHERE D.HospitalID=H.HOSPITALID
)B
GO
Now, let’s
try to implement the INNER JOIN operator between the Hospital table and the Doctors
table and check the results with the execution plan.
SELECT * FROM HOSPITAL H
INNER JOIN DOCTORS D
ON
H.HOSPITALID=D.HOSPITALID
RESULT: The
CROSS APPLY and INNER JOIN produces the same result sets with the same execution
plan.
Question: Where
does CROSS APPLY works efficiently and outcomes the results of an INNER JOIN?
Just imagine
if we would require just the top 1 result from table B verse the matching
records on table A, then the CROSS APPLY is worthy for us. This condition cannot
be achieved just using an INNER JOIN operator in SQL Server, which would require
an additional CTE concept implementation along with INNER JOIN logic.