convert subquery to join statement

Question

Post reply

convert subquery to join statement

Amrita bhatia

Right there with Babe

Points: 718
More actions
July 29, 2005 at 12:37 am

#165560

Hi
Is there a way to get all the items present in one table and not in other table

So if we take query in northwind database in sql server

Select * from orders where orderid not in (select orderid from [order details])

But can above query be converted to join statement?

Thanks
Amrita

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

Try

Select * from orders o left join [order details] od

on o.orderid = od.orderid

where od.orderid is null

venku009 Mr or Mrs. 500 Points: 503 More actions · Answer 2

Select a.*,b.OrderId

From Orders a(NoLock)

Left Outer Join [Order Details] b(NoLock) On (a.OrderId = b.OrderId)

Where b.OrderId Is Null

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 3

Hi venku,

Just a comment here, y to use NOLOCK hint, i think sql Query Optimizer will choose the best hints so y to override the default behaviour and by the way NOLOCK hint means "dirty reads" are possible.

Correct me if I'm wrong..

Regards,

Dilip

venku009 Mr or Mrs. 500 Points: 503 More actions · Answer 4

Sorry for the use of NoLock, currently I am working on a reporting system and addicted to it.

Yeah, you can avoid using NoLock.

Regards,

Venku

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 5

hey it's ok buddy..sometimes same happens to me too 😉

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 6

thnx for the answers

another question here, which one is better in terms of performance

ie subquery or left outer join

i have read subqueries are slow but in this case left outer join is show in execution plan

ny ideas??

amrita

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 7

Hi Amrita,

Say, ur orders and orderdetail tables grow tremendously in that case anytimes joins would be better than subqueries..Becoz for a subquery for each outer query it will execute the inner query that many times whether you like it or not, e,g. your Orders table have say 1000 records and OrderDetails have 20000 recs. so for each outer query it will execute the innerquery having 20000 recs...can u just paste the execution plans results out here so that we can have a look?

Regards,

Dilip