Joins vs Where

Question

Post reply

Joins vs Where

Barry Ferrell

SSC Journeyman

Points: 80
More actions
August 24, 2006 at 11:38 am

#94530

Can someone tell me the advantages/disadvantages of using joins vs just using where clauses?

Thanks,
Barry

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

Joins are normally faster.

Kindest Regards,

Amit Lohia

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Also, it is the new norm in the sql world. Not to mention that it's much less confusing... both for developpers and the server himself.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

JOIN and WHERE clause cannot be compared because the WHERE clause is a filter while JOIN is used to generate resultset from one or more tables. JOIN is compared to UNION and Subquery, in ANSI SQL JOIN and UNION is part of the idempotent law which means the operation will not change the data. That is not true with the RDBMS(relational database management systems) vendors version of the language because you can update JOINs in T-SQL and your can update UNOIN ALL views with INSTEAD of Trigger. Performance INNER JOIN, Subquery are the same, then OUTER JOIN and the UNION is the slowest the reason ANSI SQL experts tell you to rewrite old JOINs with the UNION ALL syntax. Hope this helps.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Also try to put as many comparisons at the JOIN level instead of WHERE level.

As Gift Peddie writes, that produces smaller resultsets to be joined, and the query will be faster.

N 56°04'39.16"
E 12°55'05.25"

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 5

also for readability of (outer) joins, using the JOIN-syntax delivers more clear separation of join-predicate and filter-predicate.

The optimizer mostly optimises to a fairly equal plan

We push for usage of the join-syntax be able to support the apps/sql by # peaple.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me