Using INNER JOIN vs WHERE ...

[EdAROC]

Is there any difference between using the INNER JOIN clause versus including the match criteria in the WHERE clause?

I am revising existing code and was wondering what the advantages/disadvantes, pros/cons or performance differences are for:

INNER JOIN MACHINES ON ORD_MACH_OPS.MACH_NO = MACHINES.MACH_NO
or
WHERE ORD_MACH_OPS.MACH_NO = MACHINES.MACH_NO

The only thing I can see is reading this at a later date and being able to more quickly recognize that this is a join.

 

[r937]

JOIN syntax is also a lot easier to understand if there are many tables involved, and many join columns

performance should be the same

r937.com | rudy.ca

 

[lespaul]

read the Join link below

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[EdAROC]

Thanks to both of you for your replies.
I have worked with relational databases for decades and one thing learned in those "early" days (80's) was that when there's more than 1 way to set up something there was usually a trade off in performance or another aspect. That's why I asked the question. And performance mattered - a lot! So, I still naturally check.

r937 - Thanks for confirming what I thought.

Leslie - I'm still trying to get my arms around the SQL command structure. The article gave me some new insight.

Thanks again to both of you.

Ed

 

[r937]

when there's more than 1 way to set up something there was usually a trade off in performance or another aspect

very well stated


sometimes the neglected aspect is understandability

it's tough if you can't understand the nuances of query, especially if you wrote it



r937.com | rudy.ca

 

[Golom]

... it's tough if you can't understand the nuances of query, especially if you wrote it


Or even worse ... when you are writing it.

 

[EdAROC]

"Or even worse ... when you are writing it."

LOL - Been there, done that (more than once).