Hot answers tagged

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 ...
user avatar
  • 1,793
29 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 ...
user avatar
27 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 ...
user avatar
  • 77.9k
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 ...
user avatar
  • 27.1k
24 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 ( ...
user avatar
23 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 ...
user avatar
  • 55.7k
22 votes
Accepted

No advantage of using Cross Apply or CTE over inline sub-query

But the execution plan for both is same as shown below: The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics ...
user avatar
  • 69.2k
22 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 ...
user avatar
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 ...
user avatar
  • 33.4k
19 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 ...
user avatar
17 votes
Accepted

Why is CTE open to lost updates?

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore. The wCTE based query doesn't really solve the ...
user avatar
  • 51.8k
16 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 ...
user avatar
15 votes
Accepted

How do I sort the results of a recursive query in an expanded tree-like fashion?

You can order by the array representing the path from the root up to the leaf: To keep it simple I left out LIMIT (so we also don't need extra parentheses) and WHERE of your original query. Those can ...
user avatar
15 votes
Accepted

PostgreSQL Recursive Descendant Depth

The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have: INNER JOIN descendants d ON d.parent_id = o.object_id It should be the other ...
user avatar
15 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 ...
user avatar
13 votes
Accepted

Simplifying Function with Recursive CTE and/or Window Function

You need an extra column to carry along the running total (fiddle). In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is ...
user avatar
  • 77.9k
12 votes

CTE Error (nvarchar to numeric)

You can't easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is ...
user avatar
12 votes
Accepted

The equivalent of Common Table Expressions (CTEs) in MySQL

In this particular case the CTE could be replaced with a normal subselect (a derived table): SELECT tbl_notifications.module_name, tbl_notifications.message, tbl_notifications.staff_username, ...
user avatar
  • 21.1k
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 ...
user avatar
  • 67.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 ...
user avatar
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 ...
user avatar
  • 69.2k
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

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 ...
user avatar
  • 8,661
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 ...
user avatar
  • 30.3k
11 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 ...
user avatar
  • 2,191
11 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 ...
user avatar
  • 69.2k
10 votes
Accepted

Check if any of the values is in a subquery result

Try: where exists (select * .... where Customers.orderId = ... or Customers.secondaryId = ... ) Eg, if you were planning on: where orderId in (select value from ...) or ...
user avatar
10 votes
Accepted

Using a CTE in IF EXISTS Query

A CTE can't be used as a subquery. One workaround would be: IF EXISTS ( SELECT 1 FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn FROM table ) AS DATA ...
user avatar
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 ...
user avatar
  • 11.4k
10 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 ...
user avatar

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