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.
- Implicit Join Method
- Explicit Join Method
- 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.) 1st Method : Implicit Join Notation
SELECT * FROM Department D, Employee E where D.DepartmentID = E.DepartmentID
2.) 2nd Method : Explicit Join Notation
SELECT * FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID
3.) 3rd Method : Cross Apply
SELECT * FROM Department D CROSS APPLY(SELECT * FROM Employee EWHERE E.DepartmentID = D.DepartmentID) A
Result:
There will not be any performance changes on any way. All
the way will give same performance impact...
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757