CTE

How it is used

CTEs make long queries readable by splitting them into named steps. A recursive CTE (WITH RECURSIVE in PostgreSQL and MySQL) can walk tree data such as category hierarchies or org charts. Developers often say 'pull that out into a CTE'.

Example sentences

1. I moved the monthly totals into a CTE so the final SELECT is easier to read.

2. A recursive CTE returns the whole category tree in one query.

Common collocations

• WITH clause
• recursive CTE
• use a CTE
• chain CTEs

Related terms

• subqueryA query written inside another query, in brackets, whose result is used by the outer query, for example as a list of values to filter by.
• window functionA SQL function that calculates a value across a set of related rows, such as a running total or a rank, while still returning every individual row.
• SELECT statementThe SQL statement that reads data from one or more tables and returns the matching rows without changing anything in the database.
• queryA request sent to a database to read or change data, usually written in SQL. As a verb, to query means to ask a database for data.

This word in other fields

The same English word can have a different meaning in another field:

• CTE · Software Engineering
• CTE · Data Science & Analytics

More terms: SQL Statements and Queries

• SQL
• WHERE clause
• join
• INSERT statement
• UPDATE statement
• DELETE statement
• ORDER BY
• GROUP BY
• aggregate function
• inner join
• left join
• HAVING clause