How to Use CTE function in Where Clause or Where In Clause

Question

Hi,

How to Use CTE function in Where Clause or Where In Clause?

Thanks in Advance

Kalees

Answer 1

;with T (id) as (
	select 1
	union all
	select 2
)
select 100
where 2 in (select [id] from T)

---

Serg

Answer 2

Kalees, Your question is not so clear but Serg has provided a good example here. Hope that answers you.

For CTE's, you might consider reading this blog post from pinal.

http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

You can find internal links from there to view further more articles and examples for how CTE's work.

To add to the topic,

Apparently, CTE is a subquery kind of, but it has it's own syntax and own internal working methodology and provides faster results. It does even support multiple cte's and recursive cte's.

Hope this helps.

---

Welcome to MSDN Forums.
Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

You Value Community's efforts and Community values your faith.
- Arun Kumar Allu

Answer 3

hi,

thanks for your reply. 

i need to implement   select * from table where id in(wit cte funtion). but i am getting error.

Answer 4

hi,

thanks for your reply. 

i need to implement   select * from table where id in(wit cte funtion). but i am getting error.

Then Serg's sample is the way to go:

;with T (id) as (
	select 1
	union all
	select 2
)
select t1.col1, t1.col2
FROM t1
where t1.col3 in (select [id] from T);

As a side note: I recommend to use qualified column names instead of SELECT *. Otherwise you might face some "interesting issues" if there's a need to add another column to the table...

Answer 5

hi,

thanks for your reply. 

i need to implement   select * from table where id in(wit cte funtion). but i am getting error.

If you are using sub-query then this works fine, but in CTE the syntax is different.

-- This wont work, CTE's stay on top.
select * from table where id in(wit cte funtion)

-- CTE's are in in this format
With cteTbale AS
( your select sub query)
Select * from cteTable -- and what ever operation/joining/filtering you are performing
The link I provided above, shows a sample example for CTE.
Also check this: http://blog.sqlauthority.com/2012/02/08/sql-server-convert-subquery-to-cte-sql-in-sixty-seconds-001-video/
I haven't checked it myself but Pinal does gr8 job in explaining.
Hope this helps.

---

Welcome to MSDN Forums.
Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

You Value Community's efforts and Community values your faith.
- Arun Kumar Allu

Answer 6

hi,

thanks for your reply. 

i need to implement   select * from table where id in(wit cte funtion). but i am getting error.

You can not define a CTE in the where clause of a select stmt.

You have to define a CTE first and use it in the subsequent select statement.Like

;with cte

as

(

select .....define cte here

)

select * from table where id in(select id from cte);

Refer to guidelines for created common table expression in the bol..

http://msdn.microsoft.com/en-us/library/ms175972.aspx

---

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer