Common Table Expressions (i.e. CTEs)
Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal result sets, which can be referenced as a table within your SQL. CTEs are available in many common database engines and are available in latest versions of all of the support grammars.
CTEs come in two basic types:
Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.
Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.
While all of the grammars currently support CTEs, there is enough difference between the various databases implementations of CTEs that unless your CTEs are fairly basic, using CTEs within your project will most likely tie your project to a specific database, unless you account for the differences in your code.
However, CTEs are can be extremely useful to solve certain use cases.
To add CTEs to your queries, you have two methods available:
with()— Allows you to define a non-recursive CTE.withRecursive()— Allows you to define a recursive CTE.
with
name
string
true
The name of the CTE.
input
QueryBuilder | Function
true
Either a QueryBuilder instance or a function to define the derived query.
columns
Array<String>
false
[]
An optional array containing the columns to include in the CTE.
recursive
boolean
false
false
Determines if the CTE statement should be a recursive CTE. Passing this as an argument is discouraged. Use the dedicated withRecursive where possible.
You can build a CTE using a function:
// qb
query.with( "UserCTE", function ( q ) {
q
.select( [ "fName as firstName", "lName as lastName" ] )
.from( "users" )
.where( "disabled", 0 );
} )
.from( "UserCTE" )
.get();Alternatively, you can use a QueryBuilder instance instead of a function:
A single query can reference multiple CTEs:
withRecursive
name
string
true
The name of the CTE.
input
QueryBuilder | Function
true
Either a QueryBuilder instance or a function to define the derived query.
columns
Array<String>
false
[]
An optional array containing the columns to include in the CTE.
Here is an example of building a recursive CTE using SQL Server which would return all parent/child rows and show their generation/level depth:
Last updated
Was this helpful?