Stack Overflow
Stack Overflow

Questions tagged [common-table-expression]

A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE or DELETE statement. Formally named , according to the ISO SQL standard.

A Common Table Expression (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. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query (use the tag in that case).
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.
4144 questions
295
votes
4 answers

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

In this excellent SO question, differences between CTE and sub-queries were discussed. I would like to specifically ask: In what circumstance is each of the following more efficient/faster? CTE Sub-Query Temporary Table Table…
whytheq
  • 34,466
  • 65
  • 172
  • 267
260
votes
9 answers

When to use Common Table Expression (CTE)

I have begun reading about Common Table Expression and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well?…
imak
  • 6,489
  • 7
  • 50
  • 73
243
votes
8 answers

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

I have a MS SQL CTE query from which I want to create a temporary table. I am not sure how to do it as it gives an Invalid Object name error. Below is the whole query for reference SELECT * INTO TEMPBLOCKEDDATES FROM ;with Calendar as ( select…
Learning
  • 19,469
  • 39
  • 180
  • 373
231
votes
2 answers

Keeping it simple and how to do multiple CTE in a query

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in…
John Leidegren
  • 59,920
  • 20
  • 131
  • 152
226
votes
7 answers

Can you create nested WITH clauses for Common Table Expressions?

WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work.
Joe Phillips
  • 49,743
  • 32
  • 103
  • 159
204
votes
4 answers

Combining INSERT INTO and WITH/CTE

I have a very complex CTE and I would like to insert the result into a physical table. Is the following valid? INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos ( BatchID, AccountNo, APartyNo, SourceRowID ) WITH tab ( --…
dcpartners
  • 5,176
  • 13
  • 50
  • 73
187
votes
10 answers

Difference between CTE and SubQuery?

From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem. Now then, what is the advantage of using a CTE (Common Table…
dance2die
  • 35,807
  • 39
  • 131
  • 194
185
votes
2 answers

How to use multiple WITH statements in one PostgreSQL query?

I would like to "declare" what are effectively multiple TEMP tables using the WITH statement. The query I am trying to execute is along the lines of: WITH table_1 AS ( SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS…
Greg
  • 8,175
  • 16
  • 72
  • 125
175
votes
2 answers

The maximum recursion 100 has been exhausted before statement completion

I keep getting a max recursion error with this query. At first I thought it was because a null was being returned and then it would try and match the null values causing the error however, I rewrote my query so nulls aren't returned and the error…
HELP_ME
  • 2,619
  • 3
  • 24
  • 31
174
votes
12 answers

Which are more performant, CTE or temporary tables?

Which are more performant, CTE or Temporary Tables?
Blankman
  • 259,732
  • 324
  • 769
  • 1,199
156
votes
7 answers

How do you use the "WITH" clause in MySQL?

I am converting all my SQL Server queries to MySQL and my queries that have WITH in them are all failing. Here's an example: WITH t1 AS ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON…
user161433
  • 4,419
  • 5
  • 32
  • 55
125
votes
4 answers

SQL Server CTE and recursion example

I never use CTE with recursion. I was just reading an article on it. This article shows employee info with the help of Sql server CTE and recursion. It is basically showing employees and their manager info. I am not able to understand how this query…
Thomas
  • 33,544
  • 126
  • 357
  • 626
118
votes
4 answers

Insert data in 3 tables at a time using Postgres

I want to insert data into 3 tables with a single query. My tables looks like below: CREATE TABLE sample ( id bigserial PRIMARY KEY, lastname varchar(20), firstname varchar(20) ); CREATE TABLE sample1( user_id bigserial…
Faisal
  • 1,469
  • 2
  • 13
  • 25
115
votes
8 answers

MySQL "WITH" clause

I'm trying to use MySQL to create a view with the "WITH" clause WITH authorRating(aname, rating) AS SELECT aname, AVG(quantity) FROM book GROUP BY aname But it doesn't seem like MySQL supports this. I thought this was pretty standard and…
Bill Collins
113
votes
4 answers

How to use multiple CTEs in a single SQL query?

Is it possible to combine multiple CTEs in single query? I am looking for way to get result like this: WITH cte1 AS ( ... ), WITH RECURSIVE cte2 AS ( ... ), WITH cte3 AS ( ... ) SELECT ... FROM cte3 WHERE ... As you can see, I have one recursive…
axvm
  • 1,876
  • 2
  • 11
  • 19
1
2 3
…
99 100