From Joins Versus Where Clause Joins

Which is faster/better...'from joins' versus 'where clause joins'?
As In:
SELECT fname FROM Employees INNER JOIN Users ON
Employees.UserID=Users.UserID
Vs.
SELECT fname FROM Employees, Users WHERE Employees.UserID=Users.UserID
Is one significantly worse then the other in certain situations?
Exactly the same. Second one is just older syntax.
"John Smith" <js@.no.com> wrote in message
news:%234soeu%23nEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Which is faster/better...'from joins' versus 'where clause joins'?
> As In:
> SELECT fname FROM Employees INNER JOIN Users ON
> Employees.UserID=Users.UserID
> Vs.
> SELECT fname FROM Employees, Users WHERE Employees.UserID=Users.UserID
>
> Is one significantly worse then the other in certain situations?
>
|||They perform the same. However, placing filter criteria in the ON clause
vs. the WHERE clause can give you different results in LEFT JOIN's. From
Northwind:
select
*
from
Customers c
left join
Orders o on o.CustomerID = c.CustomerID
and o.ShipCountry = 'Germany'
select
*
from
Customers c
left join
Orders o on o.CustomerID = c.CustomerID
where
o.ShipCountry = 'Germany'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Smith" <js@.no.com> wrote in message
news:%234soeu%23nEHA.2764@.TK2MSFTNGP11.phx.gbl...
Which is faster/better...'from joins' versus 'where clause joins'?
As In:
SELECT fname FROM Employees INNER JOIN Users ON
Employees.UserID=Users.UserID
Vs.
SELECT fname FROM Employees, Users WHERE Employees.UserID=Users.UserID
Is one significantly worse then the other in certain situations?
|||Neither is faster (you can test this yourself, time it, view execution plan,
etc).
As for better, I prefer INNER JOIN because it allows you to separate the
join criteria from the filter criteria. It is also the only way to reliably
construct outer joins (the old *= syntax is deprecated and has some serious
issues).
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:#4soeu#nEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Which is faster/better...'from joins' versus 'where clause joins'?
> As In:
> SELECT fname FROM Employees INNER JOIN Users ON
> Employees.UserID=Users.UserID
> Vs.
> SELECT fname FROM Employees, Users WHERE Employees.UserID=Users.UserID
>
> Is one significantly worse then the other in certain situations?
>
|||Ahh...I didn't know that. Thank you...Very important info.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uh7HB5#nEHA.3396@.tk2msftngp13.phx.gbl...
> Neither is faster (you can test this yourself, time it, view execution
plan,
> etc).
> As for better, I prefer INNER JOIN because it allows you to separate the
> join criteria from the filter criteria. It is also the only way to
reliably
> construct outer joins (the old *= syntax is deprecated and has some
serious
> issues).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "John Smith" <js@.no.com> wrote in message
> news:#4soeu#nEHA.2764@.TK2MSFTNGP11.phx.gbl...
>
|||Woah...Never knew that. Crap that's dangerous. Hope I haven't screwed up
any sql statements in the past expecting different behavior.
I'm still trying to figure out everything thats happening there.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uSgNI4#nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> They perform the same. However, placing filter criteria in the ON clause
> vs. the WHERE clause can give you different results in LEFT JOIN's. From
> Northwind:
> select
> *
> from
> Customers c
> left join
> Orders o on o.CustomerID = c.CustomerID
> and o.ShipCountry = 'Germany'
> select
> *
> from
> Customers c
> left join
> Orders o on o.CustomerID = c.CustomerID
> where
> o.ShipCountry = 'Germany'
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "John Smith" <js@.no.com> wrote in message
> news:%234soeu%23nEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Which is faster/better...'from joins' versus 'where clause joins'?
> As In:
> SELECT fname FROM Employees INNER JOIN Users ON
> Employees.UserID=Users.UserID
> Vs.
> SELECT fname FROM Employees, Users WHERE Employees.UserID=Users.UserID
>
> Is one significantly worse then the other in certain situations?
>
|||Thanks!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eKOcD3#nEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Exactly the same. Second one is just older syntax.
>
> "John Smith" <js@.no.com> wrote in message
> news:%234soeu%23nEHA.2764@.TK2MSFTNGP11.phx.gbl...
>
|||When you put the filter criteria in the JOIN clause, it filters first and
then does the join. When you put the filter criteria in the WHERE clause,
it joins first and then filters, essentially converting the LEFT JOIN to an
INNER JOIN in many cases.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Smith" <js@.no.com> wrote in message
news:eHFM9J$nEHA.1608@.TK2MSFTNGP15.phx.gbl...
Woah...Never knew that. Crap that's dangerous. Hope I haven't screwed up
any sql statements in the past expecting different behavior.
I'm still trying to figure out everything thats happening there.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uSgNI4#nEHA.3668@.TK2MSFTNGP15.phx.gbl...
> They perform the same. However, placing filter criteria in the ON clause
> vs. the WHERE clause can give you different results in LEFT JOIN's. From
> Northwind:
> select
> *
> from
> Customers c
> left join
> Orders o on o.CustomerID = c.CustomerID
> and o.ShipCountry = 'Germany'
> select
> *
> from
> Customers c
> left join
> Orders o on o.CustomerID = c.CustomerID
> where
> o.ShipCountry = 'Germany'
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "John Smith" <js@.no.com> wrote in message
> news:%234soeu%23nEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Which is faster/better...'from joins' versus 'where clause joins'?
> As In:
> SELECT fname FROM Employees INNER JOIN Users ON
> Employees.UserID=Users.UserID
> Vs.
> SELECT fname FROM Employees, Users WHERE Employees.UserID=Users.UserID
>
> Is one significantly worse then the other in certain situations?
>