A short focus on Common Table Expressions

Common Table Expressions aka CTEs are one of Postgresql's most mysterious feature for SQL newcomers. CTE are introduced by the standard ISO SQL99 and are supported by Postgres since version 8.4. To be short, they replace in a more convenient way nested SELECT statements. This example is about employees of a company divided in departments. If you want to get your hands dirty with this one, here is the SQL file you should use to create a ready to go database :

The previous article was about window functions so let's now take the example where we need to fetch all the data related to one given employee augmented with the employee_id of the next employee older than him and the same with the just younger so we can provide links to them on the page showing all the attributes of an employee. The first tempting query is:

The reason is simple: the data window exists within the rows filtered by the where condition. In this case, there is only one row remaining, hence the NULL in older and younger. If you are a web developer, your mind is probably blocking the only solution available: we need to nest a subselect so the window can apply on all employees. Subselects are so tedious to write and to debug, web developers avoid them at all costs. Fortunately, CTE are here for that. Their goal is to define and name prepared sets so they can be used in other statements as they were simple tables:

It is exactly what SQL has been made for, defining sets that can be joined together again and again. As a first step, let's write the query that fetch all employees from a given user's department with their younger and older workmate

It is disgraceful to use twice the same parameter employee_id = 27. This may be an indication something is not done in the right order. What if swapping both statements, first fetching employee's information then join his workmates ?

Copyright Grégoire HUBERT 2014 - 2015. All contents on this site are under the CC by-sa licence.CSS nicely adapted by Mikael Paris.Free tools for a free network. This site has been made and is powered using open source and free softwares only.