What's the proper way to format an SQL statement?

[mrpaulwass]

Let say I have the following tables

Order header (10K rows) table name=ORDHDR
Order detail (100K rows) table name=ORDDTL
Item info (1K rows) table name=ITEMS
Customer info (2K rows) tabls name=CUSTS

and I want to write an SQL which will give me a list of all orders delivered today for a given customer.

SELECT CUSTS.NAME,
ORDHDR.ORDERNO,
ORDDTL.ITEMNO,
ITEMS.ITEMDESC
FROM CUSTS,
ORDHDR,
ORDDTL,
ITEMS
WHERE CUSTS.CUSTNO = ORDHDR.CUSTNO
AND ORDHDR.ORDERNO = ORDDTL.ORDERNO
AND ORDDTL.ITEMNO = ITEMS.ITEMNO;

What is the general rule for setting up the above SQL?
Should the order of my FROM clause match the order in my WHERE clause (or vice-versa)?
In what order should the WHERE clause be set? Tables with most rows at the top? or the bottom?
Does it matter what order columns are selected in?

I've been writing SQL's for years, and I've never really known what the general rule of thumb is.

Thanks.

P

[stecal]

http://asktom.oracle.com/pls/ask/f?p...:192812348072,

[DaPi]

With the CBO, unless you use a /*+ ORDERED */ hint, I don't think any of the above matter (RBO is different). Go for whatever will read best when you look at it again in a year's time - for me that implies:
- ordering tables as they would appear if you could stretch the db model of these tables into a linear chain (kind of)
- putting the conditions in that order too (some people like to group the joining conditions before the "restrictive" conditions - even so, keep some order in the sub-groups)

Weeeelll that's what I set out doing . . . .