Commom Table Expressions VS Sub-queries

  • I am working with CTE and sub-queries my rise one doubt... Whats difference between CTE And Subqueries? however they make are same.

  • Very little.

    A CTE can be considered a 'named' subquery. It means if you need the subquery in more than one place (say in select and where), you can define it as a CTE and reference by name instead of copying the entire definition.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The difference is that a CTE can be used multiple times, and can be used in later CTEs in the same query, while a sub-query (derived table) can't do either of those things.

    Plus, for me at least, I find CTEs easier to read than derived tables in the From clause. That's a perspective thing, though.

    If, for example, you need to join a derived table to itself, in a query, in order to chain some aggregate, you have to build it twice. With a CTE, you build it once and join it to itself.

    CTEs can also be recursive, which has a number of uses mainly associated to hierarchical data.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I would like to know whether in a CTE is possible define sub-queries?

    for example:WITH CTE1 as ( Select AddressID from Person.Address

    where AddressiD in ( select ProductID from Production.Product)

    Error:

    Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'Select'

  • Syntax error in your statement:

    use adventureworks

    ;WITH CTE1 as ( Select AddressID from Person.Address

    where AddressiD in (select ProductID from Production.Product))

    Select * from cte1

  • lucassouzace (10/2/2008)


    I would like to know whether in a CTE is possible define sub-queries?

    for example:WITH CTE1 as ( Select AddressID from Person.Address

    where AddressiD in ( select ProductID from Production.Product)

    Error:

    Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'Select'

    Yes. In the above snippet, you are missing a second paren at the end. It should look like this:

    WITH CTE1 as (

    Select

    AddressID

    from

    Person.Address

    where

    AddressiD in (select ProductID from Production.Product)

    )

    😎

  • You can also do chained CTEs:

    ;WITH

    CTE1 as

    (select ProductID

    from Production.Product),

    CTE2 as

    (Select AddressID

    from Person.Address

    where AddressiD in

    (select ProductID

    from CTE1))

    select *

    from CTE2

    Not particularly impressive in particular query, but can be very useful if the inner (first CTE) does aggregations or is otherwise complicated.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply