top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How can we rewrite sub-queries into simple select statements or with joins?

0 votes
389 views
How can we rewrite sub-queries into simple select statements or with joins?
posted Nov 25, 2014 by Vinitha

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
E.g.

USE AdventureWorks
GO
WITH EmployeeDepartment_CTE AS (
SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory
)
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID =ed.DepartmentID
GO
answer Nov 25, 2014 by Manikandan J
...