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?
>

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...
> > 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?
> >
> >
>|||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...
> > 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?
> >
> >
>|||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?
>

Frequency of joins between two tables in a database.

Hi All,
Is there anyway to tell the frequency of joins between two tables in a
database?. I need help on this one.
Thanks.
Message posted via http://www.webservertalk.comI don't know of any way to profile this, but what you could do
reasonably quickly is build up a quick structure mapping queries to the
tables they join, then run a profiler, log all the queries run into a
table, and thus be able to interrogate this. However that will assume
that your queries always join the same tables.|||Hi
Can you say what you need this for? If you are looking at de-normalisation
then you may want to base that on actual performance.
If you script your code you may get some idea of how often a table is joined
by using test searches.
John
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef609f8fb256@.uwe...
> Hi All,
> Is there anyway to tell the frequency of joins between two tables in a
> database?. I need help on this one.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||The reason to get this information is, I'm trying to split a large DB( 700gb
)
to group the join tables on one drive for performance purposes.
Thanks.
John Bell wrote:
>Hi
>Can you say what you need this for? If you are looking at de-normalisation
>then you may want to base that on actual performance.
>If you script your code you may get some idea of how often a table is joine
d
>by using test searches.
>John
>
Message posted via http://www.webservertalk.com|||I could be wrong, but won't it be better to have the joined tables on
different drives, so multiple IO paths can be used at the same time? Or are
we talking about moving certain data to faster drives and leaving other data
on slower drives?
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef800aece75b@.uwe...
> The reason to get this information is, I'm trying to split a large DB(
700gb)
> to group the join tables on one drive for performance purposes.
> Thanks.
> John Bell wrote:
de-normalisation
joined
> --
> Message posted via http://www.webservertalk.com|||Hi
You can look profile and look at high reads/writes as well as duration, that
will hopefully give some indication where you can split things up. You may
also want to split indexes onto different spindles, possibly looking at ITW
to show you some indication of what indexes are frequently used.
Also you can try creating multiple files on different drives that belong to
the same filegroup. The other option is to add extra discs to an existing
array so splitting I/O over more spindles.
John
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef800aece75b@.uwe...
> The reason to get this information is, I'm trying to split a large DB(
> 700gb)
> to group the join tables on one drive for performance purposes.
> Thanks.
> John Bell wrote:
> --
> Message posted via http://www.webservertalk.com|||And... don't forget move tempdb onto it's own spindles, possibly splitting
it into multiple files as well as moving the system databases into their own
discs.
John
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef800aece75b@.uwe...
> The reason to get this information is, I'm trying to split a large DB(
> 700gb)
> to group the join tables on one drive for performance purposes.
> Thanks.
> John Bell wrote:
> --
> Message posted via http://www.webservertalk.com|||Hi Jim
I had mis-read this... yes you would want to split I/O. I prefer a more
quantative approach i.e.target what is bad!
John
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uz60VkwYGHA.3684@.TK2MSFTNGP05.phx.gbl...
>I could be wrong, but won't it be better to have the joined tables on
> different drives, so multiple IO paths can be used at the same time? Or
> are
> we talking about moving certain data to faster drives and leaving other
> data
> on slower drives?
>
> "Naana via webservertalk.com" <u14055@.uwe> wrote in message
> news:5ef800aece75b@.uwe...
> 700gb)
> de-normalisation
> joined
>|||We are thinking of moving certain large tables that are frequently access to
faster drives and leaving other data on slower drives.
Jim Underwood wrote:
>I could be wrong, but won't it be better to have the joined tables on
>different drives, so multiple IO paths can be used at the same time? Or ar
e
>we talking about moving certain data to faster drives and leaving other dat
a
>on slower drives?
>
>[quoted text clipped - 17 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1

FreeTextTable linked to multiple joins

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help.
Chris,
It's un-clear to me from the information that you've provided below whether
or not "category", "SubCategory" are separate tables or are columns in the
same table. Could you provide more specific information? Specifically, the
output of sp_help <table_name(s)> ?
If what you are looking for is how to search multiple columns in one table
using FREETEXTTABLE, please review this T-SQL Query example using the
Northwind database table employees:
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, that the same table (Employees) is use with multiple columns (Notes
and Title) from this table.
Hopefully, this is what you're looking for.
Regards,
John
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
>
|||No I was looking for searches across multiple tables.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Chris,
> It's un-clear to me from the information that you've provided below
whether
> or not "category", "SubCategory" are separate tables or are columns in the
> same table. Could you provide more specific information? Specifically, the
> output of sp_help <table_name(s)> ?
> If what you are looking for is how to search multiple columns in one table
> using FREETEXTTABLE, please review this T-SQL Query example using the
> Northwind database table employees:
> use Northwind
> go
> SELECT e.LastName, e.FirstName, e.Title, e.Notes
> from Employees AS e,
> freetexttable(Employees, Notes, 'BA') as A,
> freetexttable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and
> B.[KEY] = e.EmployeeID
> Note, that the same table (Employees) is use with multiple columns (Notes
> and Title) from this table.
> Hopefully, this is what you're looking for.
> Regards,
> John
>
>
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
>
|||Chris,
Ok, try this...
SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryID
from Employees AS e, EmployeeTerritories t,
containstable(Employees, Notes, 'BA') as A,
containstable(EmployeeTerritories, TerritoryID, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, in order for this example to work, you must alter the table
EmployeeTerritories and add a single non-null column in order to use as the
FT-Index key.
Regards,
John
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:Ok5aIorIEHA.964@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> No I was looking for searches across multiple tables.
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> whether
the[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]
(Notes[vbcol=seagreen]
as[vbcol=seagreen]
and[vbcol=seagreen]
category[vbcol=seagreen]
I
>