Are you familiar with Common Table Expressions (CTE)? Common Table Expressions are widely used to simplify queries, make them more readable and maintainable. For instance, when I want to check how many orders there are for each clients in my database, I can use:
WITH ocnt AS ( SELECT clientId, count(id) AS cnt FROM testOrders GROUP BY clientId ) SELECT c.id, c.clientName, ocnt.cnt FROM testClients AS c LEFT OUTER JOIN ocnt ON c.id = ocnt.clientId
The definition of CTE starts with “WITH” and creates a data source for the main query. In the example above, the “ocnt” is simply a query that counts the number of orders per client Id. In the main query, I simply used the “ocnt” data as any other table in the database.
Common Table Expressions can be stacked together, using one another:
WITH ocnt AS ( SELECT clientId, count(id) AS cnt FROM testOrders GROUP BY clientId ), clicnt AS ( SELECT c.id, c.clientName, ocnt.cnt FROM testClients AS c LEFT OUTER JOIN ocnt ON c.id = ocnt.clientId ) SELECT * FROM clicnt
The above examples are simple, but you should now find use cases in your code. Now, let’s try something less obvious.
Complex updates using CTE
It is not as widely known, but we can also use Common Table Expressions to create update statements that are more complex – for instance – calculating results from one table and place them in another. Let’s say that we want to calculate the average order amount per client and place it into the client’s table. With CTE it is rather easy. First, we create CTE with all the data we need, and then we are updating it as we would do inside the same table. To make things more clear, let’s start with this select-based query:
WITH avgOrd AS ( SELECT clientId, avg(amountPaid) AS avge FROM testOrders GROUP BY clientId ) SELECT c.id, c.average, avgOrd.avge FROM testClients AS c LEFT OUTER JOIN avgOrd ON c.id = avgOrd.clientId
As the result, we have the table that contains Client Id, “old” average, and the average calculated from current orders. Now we can encapsulate it in the CTE and perform an update of this “table”:
WITH avgOrd AS ( SELECT clientId, avg(amountPaid) AS avge FROM testOrders GROUP BY clientId ), cliAvg AS ( SELECT c.id, c.average, avgOrd.avge FROM testClients AS c LEFT OUTER JOIN avgOrd ON c.id = avgOrd.clientId ) UPDATE cliAvg SET average = avge
Another interesting example is the query that calculates the sort order of elements based on the row number.
WITH rowsOrdered AS ( SELECT id, sortOrder, ROW_NUMBER() OVER (ORDER BY myDesiredOrderColumn ASC) AS desiredOrder FROM tableToSort ) UPDATE rowsOrdered SET sortOrder = desiredOrder
The desired order column can be anything. Date, value, calculated value – whatever you need to set the proper order of elements.
As you can see from the examples above, CTE is a powerful tool. And we don’t even go that far. In practice, you can update multiple tables at the same time, insert values (yes, insert is also possible), and – surprisingly – delete is possible too.