Vivek johari is currently a Analyst and
have more that 5.5 yeras of experience in database. He has Master
degree in Computer and also he is Microsoft certified Sql DBA
(MCTS)& Microsoft certified SQl BI professional(MCTS). He is also
Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has
the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work
basically involved designing and optimization of the Database.He has
also published many database articles on his blog Technologies with Vivek Johari.

Common table expression (CTE):- Common table expression or CTE can be described as the temporary, named record set return by the execution of the query (insert, delete, update, select or update view statement). It is not stored as an object in the database and it last only till the execution of the SQL Query.It also can reference itself. It can be referenced multiple times. The SQL Scripts of creation of all the tables and their insert data statement is given below:-

Query for creation of the database used in this article and then using it.

Insert into Project (Projectname) Select 'Project1' Union all Select 'Project2' union all Select 'Project3'Query for inserting the data in the table Empproj

Insert into Empproj (Employeeid, Projectid) Select 1,1 Union all select 1, 2 Union all select 1,3 Union all select 2,1 Union all Select 2,2 Union all Select 3,1 Union all Select 4,2 The Common table expression has the following syntax:

With Expressionname (Column list)AS( CTE query definition)

Here, Expressionname is the name of the CTE, Column list is the name of the unique column names which are return as the record set through the execution of the CTE query and CTE query definition is the SQL query which we are going to use in the CTE.

Suppose, we want to get the recordset which contains the details of the employees who are attacted to more than 1 projects then we can use the CTE as given below:-

With CTE_Projinfo (employeeid, total_projects) As( select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1)