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

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.

USE AdventureWorks GO

WITH EmployeeDepartment_CTE AS (

SELECT Employee ID, Department ID,Shift ID

FROM HumanResources.EmployeeDepartmentHistory

)

SELECT ecte.Employeeld,ed.Departments, ed.Name,ecte.ShiftlD FROM HumanResources.Department ed

INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.Department

ID

GO