Hi guys, hope y’all doing good. Now that everything has started to become normal and the way how it was actually, there are still a few areas that are not sorted out and remain a mess.
Yes, I am talking about your SQL database. You have no idea how to maintain your data & make it more readable? Do not worry, I am here for the rescue!
In this blog, I am going to share some interesting thoughts on CTE (Common Table Expression) in SQL like how CTE helps in querying hierarchical data and keeps our query more readable.
Common Table Expression is a temporary result set that exists for one query. CTE is defined/ initiated using the ‘WITH’ keyword followed by a name for the table expression and an actual base query called the anchor.
WITH cte_name AS ( SELECT columns FROM table WHERE conditions )
Let’s take a simple company and dealer table in which the company has many relations with dealers as an example. Table company holds the list of car companies and dealers with the list of dealer names and respective sales count.
With the above setup, we will try to fetch a list of company names and dealers with the highest sales count accordingly.
In this scenario, ranking dealers with the highest sales count for a particular company would be our base result set, with that we can easily form our main query as shown below.
WITH ranked_dealers AS ( SELECT *, RANK( ) OVER (PARTITION BY cmpy_id ORDER BY sales_count desc) as rank FROM dealers ) SELECT cmpy.name AS company_name , rd.name AS dealer_name, rd.sales_count FROM company AS cmpy LEFT JOIN ranked_dealers AS rd ON cmpy.id = rd.cmpy_id WHERE rd.rank = 1;
Now we have the best dealers for the corresponding companies. By this simple example, we get a basic understanding of how CTE works. The same can be achieved using a subquery.
But for complex queries, we can have multiple table expressions with different names to use in the main query which helps improve readability and easy debugging.
CTE WITH clause runs once and generates a temporary copy table, this is referred to as MATERIALIZE. It helps in query performance. However, this comes with disadvantages such as all columns are computed even if they are not used in the main query and mainly indexes won’t work well when used with WHERE or JOIN clauses.
Prior to POSTGRES 12 all CTE’s are materialized. By default, Later versions materialize only if CTE is referenced more than once, and use inline if referenced only once which has a huge impact on query execution time. We can change the default behavior via code as shown below.
WITH cte_name AS MATERIALIZED
WITH cte_name AS NOT MATERIALIZED
Using CTE with Rails
CTE can be implemented on Rails using ActiveRecordExtended gem. The above query can be translated into ActiveRecord as,
Company.with( ranked_dealers: “ SELECT *, RANK( ) OVER ( PARTITION BY cmpy_id ORDER BY sales_count desc ) as rank FROM dealers ” ) .joins( “ LEFT JOIN ranked_dealers ON ranked_dealers.cmpy_id = company.id ” ) .where( “ ranked_dealers.rank = 1 ” ) .select( “ company.name AS company_name , ranked_dealers.name AS dealer_name, ranked_dealers.sales_count” )
Does this blog on CTE sound captivating!!! Hope you have got an idea of how to have your data sorted out. Do let me know your thoughts in the comments.
Let us all soon meet in my next blog. Until then, it’s a wrap!