Sunday, 29 December 2019

Comparing CROSS APPLY and INNER JOIN in SQL Server


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. 

0 comments: