October 2, 2008 at 9:44 am
I am working with CTE and sub-queries my rise one doubt... Whats difference between CTE And Subqueries? however they make are same.
October 2, 2008 at 9:46 am
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
October 2, 2008 at 11:21 am
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
October 2, 2008 at 11:33 am
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'
October 2, 2008 at 11:44 am
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
October 2, 2008 at 11:46 am
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)
)
😎
October 3, 2008 at 2:09 pm
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