Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

CTEs

From Wiki

Jump to: navigation, search

CTEs (common table expressions) are a new way to do complex queries in SQL Server 2005 and up.

A basic CTE follows this structure:

  1. ;with cte_name as
  2. (
  3.      select *
  4.      from tablea
  5. )
  6. select columnA
  7. from cte_name

In the bottom select statement, you can actually join the declared cte to another table.

You can also declare multiple ctes.

  1. ;with cte_a as
  2. (
  3.      select *
  4.      from tablea
  5. ),
  6. cte_b as
  7. (
  8.      select *
  9.      from tableb
  10. )
  11. select *
  12. from cte_a
  13. inner join cte_b
  14. on column_a=column_b


So for the following example:

  1. declare @temp table (ID int identity(1,1), Name varchar(50))
  2.  
  3. insert into @temp (Name)
  4. select 'David'
  5. union all select 'Denis'
  6. union all select 'Alex'
  7. union all select 'Tim'
  8. union all select 'George'
  9. union all select 'Naomi'
  10.  
  11.  
  12. ;with cte_name as
  13. (
  14.     select Name
  15.     from @temp
  16.     where ID>=4
  17. )
  18. select *
  19. from cte_name

you get the following result set:

results2.JPG


Another cool thing with CTEs is that you can reference previously declared CTEs in laters CTEs. Take the following example: We have a list of names. For this list, we want to mark all names that start with D with the class "D". For all names marked D that have an ID larger than 5 we want to mark the class as "Large D". All other names will be marked as normal. The following is an example of how someone might accomplish this with CTEs.


  1. DECLARE @temp TABLE (ID INT IDENTITY(1,1), Name VARCHAR(50))
  2.  
  3. INSERT INTO @temp (Name)
  4. SELECT 'David'
  5. UNION all SELECT 'Denis'
  6. UNION all SELECT 'Alex'
  7. UNION all SELECT 'Tim'
  8. UNION all SELECT 'George'
  9. UNION all SELECT 'Naomi'
  10. UNION all SELECT 'Allan'
  11. UNION all SELECT 'William'
  12. UNION all SELECT 'Doug'
  13. UNION all SELECT 'Darrell'
  14. UNION all SELECT 'Nick'
  15. UNION all SELECT 'Pam'
  16. UNION all SELECT 'Judy'
  17.  
  18. ;with d (ID, Name, Class) as
  19. (
  20.     select ID, Name, 'D'
  21.     from @temp
  22.     where Name like 'D%'
  23. ),
  24. large (ID, Name, Class) as
  25. (
  26.     select ID, Name, 'Large D'
  27.     from d
  28.     where ID>5
  29. )
  30. select
  31. t.ID,
  32. t.Name,
  33. coalesce(large.Class, d.Class, 'Normal') as Class
  34. from @temp t
  35.     left outer join d
  36.         on t.ID=d.ID
  37.     left outer join large
  38.         on t.ID=large.ID

691 Rating: 2.3/5 (59 votes cast)