39 votes
Accepted

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would ...
Evan Carroll's user avatar
  • 63.2k
31 votes
Accepted

What's the difference between WITH CTE & WITH CTE (<column_names>)?

You nearly have the answer for one of your questions already. In the MSDN page, there is a line directly after your quote that explains this: The basic syntax structure for a CTE is: WITH ...
Shaneis's user avatar
  • 1,803
30 votes

CTE: Get all parents and all children in one statement

If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple UNION: WITH RECURSIVE -- descendants rec_d (id, name) AS ( ...
ypercubeᵀᴹ's user avatar
30 votes

If a CTE is defined in a query and is never used, does it make a sound?

+1 to Erik, but wanted to add two things (which did not work well in a comment): You don't even need to look at execution plans to see that they are ignored when not used. The following should ...
Solomon Rutzky's user avatar
28 votes
Accepted

How does SQL recursion actually work?

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows: Split the CTE expression into anchor and recursive members. Run the anchor member(s) creating ...
Martin Smith's user avatar
  • 84.7k
27 votes
Accepted

Why can't rows inserted in a CTE be updated in the same statement?

All sub-statements of a query with CTEs happen virtually at the same time. I.e., they are based on the same snapshot of the database. The UPDATE sees the same state of the underlying table as the ...
Erwin Brandstetter's user avatar
25 votes
Accepted

Nested CTEs Returning Incorrect Results in Certain Situations

The problem isn't incorrect results, but rather non-deterministic ordering of the ROW_NUMBER window function. I'm going to reference Itzik Ben-Gan's great article, definitely check it out in full when ...
Josh Darnell's user avatar
  • 29.3k
22 votes
Accepted

If a CTE is defined in a query and is never used, does it make a sound?

It doesn't appear that they do, but this really only applies to nested CTEs. Create two temp tables: CREATE TABLE #t1 (id INT); INSERT #t1 ( id ) VALUES ( 1 ); CREATE TABLE #t2 (id INT); INSERT #t2 ...
Erik Darling's user avatar
  • 40.8k
20 votes

Why can't rows inserted in a CTE be updated in the same statement?

This is an implementation decision. It is described in Postgres documentation, WITH Queries (Common Table Expressions). There are two paragraphs related to the issue. First, the reason for the ...
ypercubeᵀᴹ's user avatar
19 votes
Accepted

Generate Dates between Date Ranges in mysql

I tried this solution : WITH recursive Date_Ranges AS ( select '2018-11-30' as Date union all select Date + interval 1 day from Date_Ranges where Date < '2018-12-31') select * from ...
preeti tank's user avatar
17 votes
Accepted

Recursive CTE to find Total for all children

This recursive CTE (SQL Fiddle) should work with your sample: WITH cte(ParentID) AS( SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet' UNION ALL SELECT i.ParentID FROM cte c ...
Julien Vavasseur's user avatar
16 votes
Accepted

Why am I getting nondeterministic results with the deterministic function STDEV()?

row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values). Any of the tied values might end up with a PartitionRowId of 1 which would ...
Justin Cave's user avatar
  • 20.3k
13 votes
Accepted

What Rules Determine When SQL Server Use a CTE as an "Optimization Fence"?

...list of known cases where SQL Server will reliably honor the CTE as an optimization fence Any such list would rely on observed behaviour, with no guarantee of reliability. The SQL Server query ...
Paul White's user avatar
  • 84.3k
12 votes

What's the difference between WITH CTE & WITH CTE (<column_names>)?

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents. Take for instance ...
Hannah Vernon's user avatar
  • 70.1k
12 votes
Accepted

PostgreSQL function not executed when called from inside CTE

That's kind of expected behaviour. CTEs are materialized but there is an exception. If a CTE is not referenced in the parent query then it is not materialized at all. You can try this for example and ...
ypercubeᵀᴹ's user avatar
12 votes
Accepted

List all parents/ascendants of all nodes

You were on the right path and almost had it. In the select of the second section the parent column should come from the cte instead of S2 and also in the second section the join was backwards (S2.id ...
indiri's user avatar
  • 2,211
12 votes
Accepted

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case ...
Paul White's user avatar
  • 84.3k
12 votes

Nested CTEs Returning Incorrect Results in Certain Situations

How do you expect deterministic results when the first cte (cte1) uses: ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RegIndex and then that Regindex column is used in the following CTEs? OVER (ORDER ...
11 votes
Accepted

Insert into multiple tables with one query

I think you can easily pile the CTEs on top of each other, like so: WITH ins AS ( INSERT INTO core.adr (street, "number", postal_code, city) VALUES ('test 1', '25a', '00912', 'villageman')...
mustaccio's user avatar
  • 26k
11 votes

Difference between inline view and WITH clause?

Other answers cover the syntax differences pretty well so I won't go into that. Instead this answer will just cover performance in Oracle. The Oracle optimizer may choose to materialize the results ...
Joe Obbish's user avatar
  • 32.2k
10 votes
Accepted

Difference between inline view and WITH clause?

There are some important differences between inline views (derived tables) and WITH clause(CTE) in Oracle. Some of them are quite universal, i.e. are applicable to other RDBMS. WITH can be used to ...
a1ex07's user avatar
  • 9,010
10 votes
Accepted

Simple SQL CTE update

To expand on MguerraTorres' answer: (Updated with the info from your secondary query) In your first query UPDATE cte says to update the table from the CTE. FROM cte as a says to refer to the table ...
RDFozz's user avatar
  • 11.6k
10 votes
Accepted

Caching intermediary CTEs for multiple uses

No, there is no way to cache a CTE; it will typically be executed multiple times if it is referenced multiple times. If you want to avoid this, you can cache the results using a #temp table instead. ...
Aaron Bertrand's user avatar
10 votes

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

Though at the moment I don't have the title of the actual hotfix, the better query plan will be used when enabling the query optimizer hotfixes on your version (SQL Server 2012). Some other methods ...
Randi Vertongen's user avatar
10 votes
Accepted

Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?

Postgres uses a multiversion model (Multiversion Concurrency Control, MVCC). In default READ COMMITTED isolation level, each separate query effectively sees a snapshot of the database as of the ...
Erwin Brandstetter's user avatar
9 votes
Accepted

CTE column caused an overflow - Order By only!

SQL Server does not guarantee the timing or number of evaluations for scalar expressions. This means that a query that might throw an error depending on the order of operations in an execution plan ...
Paul White's user avatar
  • 84.3k
9 votes
Accepted

Top Level Union Error With CTE

The error is due to this CTE: WITH Sixer As ( Select DISTINCT(Doctor) As Doctor ,Practice ,AnnRev FROM Sixer -- the error occurs here ) You have a table named Sixer and ...
ypercubeᵀᴹ's user avatar
9 votes

Simple SQL CTE update

Simple mistake in aliasing cte with "a" You should update "a" instead of updating "cte" DECLARE @a TABLE (ID int, Value int); DECLARE @b TABLE (ID int, Value int); INSERT @a VALUES (1, 10), (2, ...
SQLDevDBA's user avatar
  • 2,254
9 votes
Accepted

Conditional INSERT with a nested CTE?

For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense. You cannot nest a data-modifying CTE like you tried (as you ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible