Welcome to CODE SPREAD Simple Concepts of Coding | Programming

1. Welcome toCODE SPREADSimple Concepts of Coding | Programming SQL : Common Table Expression or CTE

2. What is CTE? • CTE is Common Table Expression. • It is an operation which helps in representing a table/temporary-table or a result of a query/sub-query, using a common expression. • This expression is created ONCE and can be used by used by other queries within the scope.

3. What are the advantages of CTE? • One of 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. • Enhanced readability. • Ease in maintenance of complex queries.

4. 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.

5. We need to try an example to understand CTE. Example: First, create a query to perform join operation on 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

6. 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’.

7. All the employees having 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.age>50

8. All the employees having 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.income>10000

9. 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.deptname= ‘Administration’

10. Above, we wrote 3 queries in which we joined the tables again and applied filters. We can take help from CTE and avoid writing the join query again and again. Lets see, in the next slide.

11. 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’

12. 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>;

13. 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.

14. Self referencing CTE (contd.) • 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;

15. Similar way, CTE can be used to implement various logic and algorithms. Try once! Tip: Try to understand the dictionary meaning of CTE or Common Table Expression and voila! half the battle is over.

16. Thanks!!CODE SPREADSimple Concepts of Coding | Programming Next in Line SQL : Indexes If you have any feedback or interesting points which can be added to this article, please share at admin@codespread.com