Menu
How we can use a CTE in a subquery in SQL Server?
like:
SELECT id (I want to use CTE here), name FROM table_name
853
¶ A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE. The CTE defines the temporary view's name, an optional list of column names, and a query expression (i.e. a SELECT statement).
CTE or COMMON TABLE EXPRESSION — a type of temporary data source that houses the results of a query. CTEs are only stored for the duration of a query. SUBQUERY — just like CTEs and temp tables, a subquery is a way to generate a temporary result set to use in a main query.
As for your question. The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times.
Just define your CTE on top and access it in the subquery?
WITH YourCTE(blubb) AS ( SELECT 'Blubb' ) SELECT id, (SELECT blubb FROM YourCTE), name FROM table_name It doesn't work:
select id (I want to use CTE here), name from table_name It's not possible to use CTE in sub queries.
You can realize it as a work around:
CREATE VIEW MyCTEView AS ..here comes your CTE-Statement. Then you are able to do this:
select id (select id from MyCTEView), name from table_name If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
