Subqueries - Common Table Expressions (Part 2)

Good afternoon everyone!

Today's blog post is a continuation of the blog I started yesterday on Subqueries (an Introduction) which I would advise on reading before you continue reading this blog post. I am going to discuss Derived Tables and Common Table Expressions and show how important they are in creating more complex database queries.

What is a Derived Table? 

A Derived Table is a locally named table expression (which means that it can only be visible by the statement that created it in the first place). Derived Tables can be used as a substitute to creating temporary tables and views as they can be created on the fly and can also be reused. This is the more preferred method to having to define all new tables and populate them, select all the data contents and then have to repeat for every instance that the view or table needs to be used. An example of a Derived Table in action is below:

The equivalent of this using a Temporary Table is shown below:

Also you need to remember to DROP your temporary table after you've finished using it as, although it won't appear in the Table List in the Object Explorer, it will still exist in the database with an old snapshot of data from when it was created:

So as you can tell, Derived tables aid code re-usability, plus they can provide better performance than Temporary Tables.

What is a Common Table Expression? 

A Common Table Expression is also a locally named table expression of 3 main parts, however these parts occur in a different order to a Derived Table. The main benefit of a CTE is that it can be reused over and over again. They are also much more readable than derived tables, making them much easier to debug when you come across a problem. An example of a Common Table Expression is shown below, which should produce exactly the same results as the other 2 examples of code I have included here: 

Next...

As you can tell, this is a very basic overview to cement my understanding. As time goes on, I may come back to this post and expand further but for now, an awareness of what they are and how they can be used is probably all I will need to know. Within my next few blog posts, I will be discussing recursive queries and how they can be used within your database solutions. Again, I just want to reiterate my thanks for the continuing support of my blog and my studies. If you have any comments, questions, suggestions or feedback please don't hesitate to post in the comment box below or like my page SQL Genius on Facebook.