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 ...
- 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 ...
- 65.6k
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 ...
- 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 ...
- 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
(
...
- 92.7k
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 ...
- 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 ...
- 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 ...
- 150k
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 ...
- 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 ...
- 92.7k
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 ...
- 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
...
- 9,978
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 ...
- 150k
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 ...
- 92.7k
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 ...
- 20k
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 ...
- 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 ...
- 176k
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,
...
- 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 ...
- 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 ...
- 92.7k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 16k
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
...
- 176k
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 ...
- 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 ...
- 126
Only top scored, non community-wiki answers of a minimum length are eligible