Need help to query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to query

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-04 : 06:45:06

my table and row as follow,
declare @t1 table
(
idx smallint,
desn varchar(50)
)
insert into @t1 values(2,'gopeng');
insert into @t1 values(2,'sungai rokam');
declare @t2 table
(
idx smallint,
desn varchar(50)
)
insert into @t2 values(2,'hpt');
insert into @t2 values(2,'sungai rokam');
insert into @t2 values(2,'slim river');

how the SQL look's like to display record in @t2 but not in the @t1?

So, the resultset as follow
idx | desn
----------------
2 | hpt
2 | slim river

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-04 : 06:58:44

SELECT * FROM @t2
EXCEPT
SELECT * FROM @t1

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-04 : 07:01:05

tq sir

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-04 : 07:30:25

check these methods also


Method 1: 
select t2.*
from @t2 t2
left join @t1 t1 on t1.desn = t2.desn
where t1.desn is null

Method 2:
select * from @t2 where desn not in ( select desn from @t1 )

Method 3:
select t2.* from @t2 t2 where not exists ( select desn from @t1 t1 where t1.desn = t2.desn )

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 07:34:55

quote:

Originally posted by raky

check these methods also


Method 1: 
select t2.*
from @t2 t2
left join @t1 t1 on t1.desn = t2.desn
where t1.desn is null

Method 2:
select * from @t2 where desn not in ( select desn from @t1 )

Method 3:
select t2.* from @t2 t2 where not exists ( select desn from @t1 t1 where t1.desn = t2.desn )

Could i ask,
Ofcourse..what is the best approach in terms of Large set of data?could you please tell me..

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-04 : 08:53:48

I Feel My Third Method is the best approach

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 09:50:19

quote:
Originally posted by raky

I Feel My Third Method is the best approach

how other experts are saying about on this?iam eager to know please

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-04 : 10:38:34

I would of thought option 1 to be the best, I was under the assumption that subselects were more taxing.

I would set your statistics on and run each method and look at the number of calculations done with each (overall time may not matter as much since the query may take hints from the first couple runs) Its more about transactions. Take a large subset of your overall data and give it a go :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources