SQL Help please

[richard-af]

Originally Posted by King Cnvt

Couldn't you just ring up all the customers and ask them if they have any orders? FFS, kids of today....

Generous of spirit and magnanimous in defeat. I salute you, sir.

SQL, eh? It can be tricky, can't it?

[TheFaQQer]

If only Milan were around to help you guys out, now that he's a DBA.

I think I would do it with a "where not exists" clause, but haven't looked at the plan for it.

[Gonzo]

I am not a coder but I have come across the phenomenon that some versions of some databases will do a tablescan when you are not expecting it, so you are earning your money by knowing when that will happen.

I would vote for the subquery option because it is the one that I would write, but I can accept that in the real world that could trigger a tablescan when it shouldn't. The foreign key should be indexed in both tables otherwise the DBA should be shot.

IMO

EDIT - even if all the fields are indexed that is not always enough to stop a tablescan.

[richard-af]

Originally Posted by Gonzo

I am not a coder but I have come across the phenomenon that some versions of some databases will do a tablescan when you are not expecting it, so you are earning your money by knowing when that will happen.

I would vote for the subquery option because it is the one that I would write, but I can accept that in the real world that could trigger a tablescan when it shouldn't. The foreign key should be indexed in both tables otherwise the DBA should be shot.

IMO

EDIT - even if all the fields are indexed that is not always enough to stop a tablescan.

What ARE you on about? Full Table Scans, Fast Full Scans, etc. are not associated, in particular, with either Joins or Subqueries. The CBO decides HOW it will gather the data. The only way to be sure is to run it and see. Then tweaks-tests-tweaks-tests, etc. Then you know. No need to go through it all again, unless the stats become significantly out-of-step with reality, or an index is added, a table is partitioned, etc., etc.


Incidentally, a Full Table Scan can be the right thing to do.

There are books on all this, as well as bizillions of lovely web sites.

That old copy of "Oracle 7: Ye Goode Guyde To The Magicks and Mysteryes" is a bit redundant.

[DimPrawn]

A left join usually sorts it all out.

[richard-af]

Originally Posted by DimPrawn

A left join usually sorts it all out.

Aaaagggghhhh!!!!! Sob! <clinking of gin bottle against glass>

[ElectricChair]

Originally Posted by DimPrawn

A left join usually sorts it all out.

whats wrong with a right join?

[richard-af]

Numpties.

[xoggoth]

Customers, orders. Dull. If I ever designed a new language I would only sell it with a condition "Not to be used in boring commercial apps"

[gables]

Originally Posted by 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

On a more philosophical note can you have a customer with zero orders?