• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL Help please

Ruprect

Super poster
Joined
Jun 21, 2006
Messages
4,195
Likes received
11
Thanks received
1
Location
London
I have 2 tables, e.g. customers and orders. Foreign key between is customerID. I want to know which customers have zero orders.

What SQL will achieve this?

Thanks
 
something like

select c.field1, c.field2
from customers c
left join orders o
on c.prikey = o.forkey
where o.somefield is null


off the top of me head like.
 
Have you not heard of EXISTS or IN ?

select * from customers where customer_id not in (select customer_id from orders)

BTW: you don't say what dbms you're using Oracle, SQL Server, Access, MySQL, etc...
 
Using a subquery is a poor idea. That's what joins are for. You obviously have never worked on databases that get big.
 
Using a subquery is a poor idea. That's what joins are for. You obviously have never worked on databases that get big.

Are you sure? Seen the Execution Plan have we? Thought not.
 
Thanks dp, gives me what I need I think.
 
Are you sure? Seen the Execution Plan have we? Thought not.


Don't need to. 9 times out of 10 a join on two columns with appropriate indexes will generate a better execution plan than using IN, which ends up using a hash/match algorithm.
 
Don't need to. 9 times out of 10 a join on two columns with appropriate indexes will generate a better execution plan than using IN, which ends up using a hash/match algorithm.

agree. also avoids unneccessary table scans.
 
SP, if anyone else disagrees with me, can you please ban them?

:wink
 

Advertisers

Back
Top