What is WITH CTE in SQL?

Question

Can anyone tell me what is WITH CTE in SQL?

Answer 1

A CTE (Common Table Expression) is used to define a temporary result set which you can use in a SELECT statement.  It provides a convenient way to manage complicated queries.

CTE is defined within the statement using the WITH operator.  You can define one or more common table expressions like this.

Here is an example of one CTE:

WITH Employee_CTE (EmployeeNumber, Title)

AS

(SELECT NationalIDNumber, JobTitle

 FROM   HumanResources.Employee)

SELECT EmployeeNumber, Title

FROM   Employee_CTE

If you want to learn SQL technical skills, check out this SQL Training course by Intellipaat.

Also, watch this video on SQL to understand more: