3 Ways of Inner Join

An 'Inner Join' is used to join operation in an applications, which creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of A and B are combined into a result row.

We can create inner Join through various method in application. I want to share 3 ways which I used in various application.

1. Implicit Join Method
2. Explicit Join Method
3. Cross Apply Method

Sample Data

CREATE TABLE [Department]( 

   [DepartmentID] [int] NOT NULL PRIMARY KEY, 

   [Name] VARCHAR(250) NOT NULL, 

) ON [PRIMARY] 

INSERT [Department] ([DepartmentID], [Name])  

VALUES (1, N'Engineering') 

INSERT [Department] ([DepartmentID], [Name])  

VALUES (2, N'Administration') 

INSERT [Department] ([DepartmentID], [Name])  

VALUES (3, N'Sales') 

INSERT [Department] ([DepartmentID], [Name])  

VALUES (4, N'Marketing') 

INSERT [Department] ([DepartmentID], [Name])  

VALUES (5, N'Finance') 

GO 

CREATE TABLE [Employee]( 

   [EmployeeID] [int] NOT NULL PRIMARY KEY, 

   [FirstName] VARCHAR(250) NOT NULL, 

   [LastName] VARCHAR(250) NOT NULL, 

   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID), 

) ON [PRIMARY] 

GO 

INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (1, N'Ajay', N'Verma', 1 ) 

INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (2, N'Sujit', N'Khanna', 2 ) 

INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (3, N'Deelip', N'Jha', 3 ) 

INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 

VALUES (4, N'Rajesh', N'Kumar', 3 ) 

     1.) 1^st Method : Implicit Join Notation

SELECT * FROM Department D, Employee E where  D.DepartmentID = E.DepartmentID

     2.) 2^nd Method : Explicit Join Notation

SELECT * FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID 

3.) 3^rd Method : Cross Apply

SELECT * FROM Department D CROSS APPLY

   (

   SELECT * FROM Employee E

   WHERE E.DepartmentID = D.DepartmentID

   ) A

Result:

There will not be any performance changes on any way. All the way will give same performance impact...