October 28, 2009 at 6:39 am
Apologies if this is a silly question, but I was wondering if it was possible to convert a subquery that uses an IN clause to a query that uses a join.
The following doesn't really work, as it gives different results:
create table #table1 (int colA, int colB)
create table #table2 (int colA)
insert into #table1 values (1, 567)
insert into #table2 values (1)
insert into #table2 values (1)
insert into #table2 values (1)
insert into #table2 values (1)
select t1.colB from #table1 t1
where t1.colA
in (select t2.colA from #table2 where t2.colA=t1.colA)
select t1.colB from #table1 t1
inner join #table2 t2 on t1.colA=t2.colA
The first query will return 4 results, while the second query returns only one result.
Any ideas how you would convert this into a query with a join?
October 28, 2009 at 7:03 am
Hi
One way would be DISTINCT:
select DISTINCT t1.colB from #table1 t1
inner join #table2 t2 on t1.colA=t2.colA
Greets
Flo
October 28, 2009 at 7:08 am
October 28, 2009 at 7:51 am
First let me correct your sample SQL
create table #table1 (colA INT, colB INT)
create table #table2 (colA INT)
insert into #table1 values (1, 567)
insert into #table2 values (1)
insert into #table2 values (1)
insert into #table2 values (1)
insert into #table2 values (1)
select t1.colB from #table1 t1
where t1.colA
in (select t2.colA from #table2 t2 where t2.colA=t1.colA)
select t1.colB from #table1 t1
inner join #table2 t2 on t1.colA=t2.colA
drop table #table1
drop table #table2
T-SQL is not C++/C#/Java. You have to write the data-types after you define the column names. 😉
Er... Execute the statement and you will see, the JOIN returns four rows and the sub-query returns only one.
Greets
Flo
October 28, 2009 at 8:04 am
Florian Reischl (10/28/2009)
First let me correct your sample SQL
create table #table1 (colA INT, colB INT)create table #table2 (colA INT)
insert into #table1 values (1, 567)
insert into #table2 values (1)
insert into #table2 values (1)
insert into #table2 values (1)
insert into #table2 values (1)
select t1.colB from #table1 t1
where t1.colA
in (select t2.colA from #table2 t2 where t2.colA=t1.colA)
select t1.colB from #table1 t1
inner join #table2 t2 on t1.colA=t2.colA
drop table #table1
drop table #table2
T-SQL is not C++/C#/Java. You have to write the data-types after you define the column names. 😉
Er... Execute the statement and you will see, the JOIN returns four rows and the sub-query returns only one.
Greets
Flo
Whoops... sorry about that.
Let me try that out 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply