Common Table Expression (CTE)

I am not a professional writer but my wish is to show you something interesting in the world of SQL !!!!

I assume all of us know about SQL. It does not matter how modern we become in the IT industry, we need SQL to fetch data from RDBMS. Presumably every one of us is familiar with ‘SELECT - FROM - WHERE’ phrases.

But have you ever heard of CTE? I was also surprised when I started to work as a DBA and heard about CTE from my mentor. He was so positive about the benefits of CTE which made me curious to learn about it and use it in our day to day life. Literally, after 6 months I was in love with it. The best part of using CTE for me was debugging. It does not matter how complex the SQL query is, CTE makes it agile and composed. The biggest strength of CTE is lying within its simplicity. You can just add steps after steps but still it offers the easiest way of debugging any number of pages of SQL.

CTE is like a temporary view that is defined and used to simplify complicated and large SQL statements. The top-down approach makes it reader friendly and easy to walk through.

Imagine the below table model : 

Now let me ask you few questions !!🙂🙂🙂

?        How do you like the query?

?        Is it readable enough?

?        Can you predict the result?

?        Do you know if the access path is a table scan or index scan?

?        Are you sure the appropriate columns are been picked up?

?        Are you sure the query is going to give correct result?

?        How easy is the debugging?

I am sure many of us need much time to understand the query..

Now let’s write it in CTE : 

Observations after I converted the SQL to CTE :

• Initial SQL was wrong  because of the incorrect and/or condition “and (a.first_name = 'Payam' or a.first_name = 'Julia' )”
• Whenever I wish to debug the query, I simply keep on changing the last ‘SELECT’ clause from v1 to v5 and i know exactly which view is incorrect
• I am going to one table at a time which helps me to use proper indexing/filters and hence proper access path
• I am using indentation to make it a good read
• I can keep on adding new tables afterwards easily if i need to modify the query 

What to focus on while writing a CTE :

• Each CTE must have a unique name and be defined only once
• Follow indentation to make it easy to walk through a CTE
• Use one alias say ‘a’ for pointing to a table and use it in every CTE
• Use Join clauses and not cartesian product
• The other name of CTE is simplicity which has to be followed to get the best out of it. 

So in short, what are the benefits of CTE

Simple / Structured / Spilt selection and join predicates / Readable / Analyzable / Split in data collection and data presentation / Compatibe with DB2 z/OS, DB2 LUW, Oracle DB, MySQL, SQL Server and many traditional and modern databases 

I am sure you will love it once you realise the benefit and for that you must practice CTE and use it every time be it a small or a large query.

Thanks for reading my article. Have a good day and happy learning !!