SELECT E.emp_id AS [Employee ID],ED.emp_name AS [Employee Name],E.emp_salary AS [Salary]
FROM employee E
JOIN employeeDetails ED
ON E.emp_id = ED.emp_id
SELECT * FROM EmployeeCTE
Here employee details will be retrieved and stored as temporary result set in the EmployeeCTE.
We can use the result to query like a table/view.