SQL Help please

[Ruprect]

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

 

[DimPrawn]

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.

 

[lilelvis2000]

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...

 

[DimPrawn]

Using a subquery is a poor idea. That's what joins are for. You obviously have never worked on databases that get big.

 

[richard-af]

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.

 

[Ruprect]

Thanks dp, gives me what I need I think.

 

[DimPrawn]

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.

 

[scotspine]

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.

 

[richard-af]

agree. also avoids unneccessary table scans.

9 times out of 10? Unnecessary table scans??!!!

I weep for you both. Amateurs.

 

[DimPrawn]

SP, if anyone else disagrees with me, can you please ban them?