Creating some temporary objects
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 ,
) ON [PRIMARY]
GO
INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (1, N'Sendhil', N'Kumar', 1 )
INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (2, N'Arun', N'Kumar', 2 )
INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (3, N'Vivek', N'Ram', 3 )
INSERT ##Employee ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (4, N'Bill', N'Gates', 3 )
CROSS APPLY and INNER JOIN
SELECT * FROM ##Department D
CROSS APPLY
(
SELECT * FROM ##Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT * FROM ##Department D
INNER JOIN ##Employee E ON D.DepartmentID = E.DepartmentID
GO
OUTER APPLY and LEFT OUTER JOIN
SELECT * FROM ##Department D
OUTER APPLY
(
SELECT * FROM ##Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT * FROM ##Department D
LEFT OUTER JOIN ##Employee E ON D.DepartmentID = E.DepartmentID
GO
