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.
| Author |
Topic |
|
ankurgupta26
Starting Member
32 Posts |
 Posted - 2002-09-30 : 09:25:04
|
| I have three table t1, t2 & t3. All tables have a single column "col1" on integer data type. They have data as follows:t1 t2 t31 1 12 2 23 3 44 5I want to find the value from t1 that is not present in t1 AND t2. i.e. the query should return me 5. I have following querries: 1) using outer join 2) using not in 3) using old join style with a virtual table. 1) works fast, 2) takes some more time & 3) gives me wrong result.1)select t1.col1 from t1 left outer join t2 on t1.col1 = t2.col1left outer join t3 on t1.col1 = t3.col1 where t2.col1 is null and t3.col1 is null2)select t1.col1 from t1 where t1.col1 not in --takes more time(select col1 from t2 ) and t1.col1 not in (select col1 from t3)3)select r.t1c from -- gives wrong results ??(select t1.col1 as t1c from t1, t2select t1.col1 from t1, t2where t1.col1 *= t2.col1 AND t2.col1 is null) as rwhere r.t2c is null and r.t3c is null My questions:1) is subquery always slower that a join2) why does the query no. 3 give worng resultsThanks !AnkuR.Edited by - ankurgupta26 on 09/30/2002 09:27:14Edited by - ankurgupta26 on 09/30/2002 09:28:14 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-30 : 09:42:34
|
| 1) no. join can use an index but subquery is sometimes faster - depends on amount of data and the query. If the subquery is faster then it is likely that both will be fast and the saving minimal2) select t1.col1 from t1, t2 where t1.col1 *= t2.col1 AND t2.col1 is null this will give all rows from t1 and none (nulls) from t2 as you can never fulfill t1.col1 = t2.col1 with t2.col1 being null.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Old Join v/s New Join Syntax