SQL - Cross Apply

 
SELECT * FROM [dbo].[Employee]

SELECT * FROM [dbo].[Department]

 
SELECT * FROM [dbo].[Employee]
CROSS APPLY [dbo].[Department]

 
SELECT * FROM [dbo].[Employee] e
CROSS APPLY [dbo].[Department] D
WHERE E.DeptID = D.DeptID

 
--OR
 
SELECT * FROM [dbo].[Employee] e
CROSS APPLY (SELECT * FROM [dbo].[Department] d
             WHERE e.DeptID = d.DeptID) A