Common Table Expression

CTE

In addition to subquery, we can use Common Table Expression (CTE) with the same result and also neater code.

CREATE TABLE #temp
(sales INT)

INSERT INTO #temp
VALUES
(100),(200),(300),(400),(500)

SELECT * FROM #temp

CREATE TABLE #temp

(sales INT)

INSERT INTO #temp

VALUES

(100),(200),(300),(400),(500)

SELECT * FROM #temp

sales
-----------
100
200
300
400
500

sales

-----------

100

200

300

400

500

WITH cte_table AS
(
select * from #temp where sales >= 200
)

select * from cte_table

WITH cte_table AS

(

select * from #temp where sales >= 200

)

select * from cte_table

sales
-----------
200
300
400
500

sales

-----------

200

300

400

500

cte_table is unlike #temp in which you can recall anytime. With cte_table , the query that refers to it must be run at the same time as its creation. But… it does look better than subquery.

select *
from
(
   select * from #temp where sales > = 200
) as temp

select *

from

(

select * from #temp where sales > = 200

) as temp