SQL: Common Table Expression called CTE

SQL-Tables
SQL-Tables

SQL: Common Table Expression called CTE

Welcome to CodeSpread!

I remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact behavior of CTE.Then i went my usual way, which is try to understand the dictionary meaning of CTE or Common Table Expression and voila! half the battle is over.

If we are able to represent a table/temporary-table or a result of a query/sub-query, using a common expression then we can assume the whole operation as Common Table Expression.

This expression is created ONCE and can be used by used by other queries within the scope. The advantage is that there is no need to write the table/temporary-table or a result of a query/sub-query again and again. This enhances readability and eases out the maintenance of complex queries

What are the prerequisites?

CTE plays the role of alternatives to table/temporary-table, views,result of a query/sub-query, and inline user-defined functions.

So, we need at least one of the above said items. For example,

lets take an instance where we need results from two tables employee and department.


select e.empname, e.age, e.income,d.deptname from

employee e

join

department d

on e.deptid=d.deptid

Now we need to run some filters on the output of above mentioned query to get the desired results. for example,

  • All the employees having age>50
  • All the employees having income>10000
  • All the employees belonging to department ‘Administration’.

select * from (

select e.empname, e.age, e.income,d.deptname from

employee e

join

department d

on e.deptid=d.deptid) R

where R.age>50

********************************************************************************

select * from (

select e.empname, e.age, e.income,d.deptname from

employee e

join

department d

on e.deptid=d.deptid) R

where R.income>10000

********************************************************************************

select * from (

select e.empname, e.age, e.income,d.deptname from

employee e

join

department d

on e.deptid=d.deptid) R

where R.deptname= ‘Administration’

To run these filters, we need 3 queries in which we have to join the tables again and apply filter.  OR  CTE can rescue us by removing the need to write the join query again.

Lets see,


With R(name,age,income,deptname)

AS

(

select e.empname, e.age, e.income,d.deptname from

employee e

join

department d

on e.deptid=d.deptid

)

select * from R where R.age>50

select * from R where R.income>10000

select * from R where R.deptname= ‘Administration’

What is the syntax?

From MSDN


WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The most important part of the above syntax is

WITH expression_name : This expression will be used for further operations like


SELECT <column_list>

FROM expression_name where <condition>;

Self referencing CTE

Self-referencing CTE is a very powerful method of implementing recursion in SQL queries. We have a basic knowledge of CTE and also have clear concepts of recursion, we just have to merge them together.

Example from MSDN: An example of recursive query computing the factorial of numbers from 0 to 9 is the following


WITH RECURSIVE temp (n, fact)

AS

(SELECT 0, 1 -- Initial Subquery

UNION ALL

SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery

WHERE n < 9)

SELECT * FROM temp;

Similar way, CTE can be used to implement various logic and algorithms. Try once!

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More from CodeSpread:

  • SQL: Common Table Expression called CTE SlideShow and ScreenCastSQL: Common Table Expression called CTE SlideShow and ScreenCastHere is a slide show and  screencast of Common Table Expression called CTE . This slide show is also available for download. You can also check the screencast on our youtube channel. http...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • SQL : Transactions and ACID properties.SQL : Transactions and ACID properties.There are always two point of views of transactions. First, the user point of view, for example, customer went to an ATM to retrieve some amount. For him, the transaction might look like, Fo...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...