WHERE or JOIN

[RichardF]

Hi,

This is something that i've always wondered about.

I always use where clauses to join queries together. Heres an example:

select orders.order_id, orderitems.order_item_id
from orders, orderitems
where orders.order_id = orderitems.order_id

Obviously I could do the same query using JOIN syntax.

The question I have is whats the difference (apart from LEFT,RIGHT,OUTER joins) . Is there an optimization if I use JOIN syntax. Is JOIN syntax ANSI compliant ?


Thanks.
Rich.


Programmers are tools for converting caffeine into code

 

[PHV]

Is JOIN syntax ANSI compliant ?
JOIN syntax IS the actual ANSI standard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

apart from OUTER joins, what's the difference?

clarity and maintainability

imagine a query with seventeen tables, and all the join conditions are jumbled together in the WHERE clause, versus a query with seventeen tables, and all the join conditions are separated into sixteen different ON clauses



r937.com | rudy.ca

 

[RichardF]

Personally, i prefer the WHERE method, i think it has better clarity. But maybe thats because i'm used to using it more often.

Rich.

 

[juliane26]

depending on the DBMS you are using the optimizer has more options using the WHERE clause, especially if more than two tables are involved.
It used to be that with the Join syntax you force the optimizer to join e.g. first table A and B and then C, but first B and C and then A might be better for performance. This freedom comes in only with WHERE. Still - with a lousy statistic this could be your right choice.
Remember: this depends on the DBMS implementation.


Juliane