Questions tagged [cte]

Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.

A CTE can be thought of as a derived table, similar to a subquery or a view. It can use indexes and statistics of underlying objects and is only visible in the context of the current query.

A recursive CTE (rCTE) is a special application of the feature that can refer to itself in the second part of a UNION statement, like so:

WITH cte AS (
   SELECT person_id, parent_id, 1 AS level
   FROM   person
   WHERE  parent_id IS NULL  -- has no parent

   UNION ALL
   SELECT p.person_id, p.parent_id, c.level + 1 AS level
   FROM   cte AS c 
   JOIN   person AS p ON p.parent_id = c.person_id
)
SELECT * FROM cte
ORDER  BY level

CTEs are used in SQL Server 2005 and later.
Postgres introduced CTEs with version 8.4 and data-modifying CTEs with version 9.1. Also called "WITH Queries" here.
Oracle documentation prefers the name "Subquery Factoring" over CTE.
Hierarchical queries often use CTEs. An alternative is the CONNECT BY clause existing since version 2 (1977). Comparing CONNECT BY and CTEs in Oracle.

412 questions

192

votes

7 answers

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, Column3 FROM SomeTable ) SELECT * FROM…

sql-server cte temporary-tables

asked Feb 15 '12 at 16:47

Rachel

8,547
20
51
74

votes

2 answers

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

Do unused CTEs in queries affect performance and / or alter the generated query plan?

sql-server performance execution-plan cte query-performance

asked Jun 14 '18 at 20:25

J.D.

40,776
12
62
141

votes

1 answer

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

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this.. WITH w AS ( SELECT * …

postgresql cte syntax sql-standard ctas

asked Feb 02 '17 at 03:13

Evan Carroll

65,432
50
254
507

votes

2 answers

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

In PostgreSQL 9.5, given a simple table created with: create table tbl ( id serial primary key, val integer ); I run SQL to INSERT a value, then UPDATE it in the same statement: WITH newval AS ( INSERT INTO tbl(val) VALUES (1) RETURNING…

postgresql cte

asked Oct 01 '16 at 02:55

Jeff Turner

votes

4 answers

How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: CREATE TABLE #NUMS (N BIGINT); INSERT INTO…

sql-server cte recursive

asked Jan 11 '19 at 19:49

UnLogicGuys

votes

1 answer

CTE: Get all parents and all children in one statement

I have this working CTE example. I can select all grand-parents and all children. But how can I select all grand-parents and all children in one statement? In this example I want Grandfather, Father, Son as output if I give "Father" as input. I use…

postgresql cte recursive

asked Jun 09 '17 at 09:01

guettli

1,591
5
25
51

votes

3 answers

Common Table Expression (CTE) benefits?

From msdn : Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them. If I reference a CTE multiple…

sql-server cte

asked Mar 06 '12 at 16:19

Royi Namir

1,233
3
13
26

votes

1 answer

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

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It represents a standard node-like tree structure with root…

postgresql order-by cte recursive tree

asked Apr 14 '14 at 12:37

JohnCand

votes

6 answers

Create a plan guide to cache (lazy spool) CTE result

I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the query is not exactly the same. What is the right…

sql-server t-sql cte

asked Oct 27 '12 at 01:07

孔夫子

4,330
3
30
50

votes

2 answers

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if I search for 'Rivet', I want to know the total…

sql-server t-sql cte recursive

asked May 18 '16 at 20:39

markokstate

votes

3 answers

Why should a CTE start with a semi-colon?

I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does the first line of the CTE begin with a semi-colon? …

sql-server t-sql cte syntax

asked Aug 29 '12 at 18:23

Hannah Vernon

70,928
22
177
323

votes

2 answers

PostgreSQL function not executed when called from inside CTE

Just hoping to confirm my observation and get an explanation about why this is happening. I have a function defined as: CREATE OR REPLACE FUNCTION "public"."__post_users_id_coin" ("coins" integer, "userid" integer) RETURNS TABLE (id integer) AS…

postgresql cte

asked Oct 27 '16 at 08:23

Andy

votes

2 answers

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO P_OUTCOUNT FROM T; END; I want to select some data…

oracle select cte

asked Oct 04 '11 at 17:33

BrunoLM

3,533
7
28
22

votes

3 answers

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, and on the whole it works beautifully, and as…

sql-server sql-server-2012 optimization cte

asked Nov 14 '13 at 22:10

liver.larson

votes

2 answers

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH RECURSIVE query running with PostgreSQL 9.4. I do…

postgresql update cte recursive

asked Jan 12 '16 at 05:08

Diggity

2 3

…

27 28 Next