August 15, 2010 at 10:42 am
Good morning,
I have questions about JOINS in SQL Server.
This is how I have been writing my joins:
select A.LastName, A.FirstName, B.JobTitle
from people A
inner join employees B
on A.userID = B.userID
An Oracle DBA told me that she does it like this instead:
select A.LastName, A.FirstName, B.JobTitle
from people A, employees B
where B.userID = B.userID
I asked her how she would account for outer joins using her method and she told me that in Oracle, there is a (+) symbol that goes in the where clause to indicate an outer join. The (+) did not work in the SQL Server query that I wrote. Is there another character to indicate an outer join when using her method in SQL Server?
When writing joins, it seems to me that the second method, using where instead of join, allows more flexibility. For example this below does not work because the order of the joins is wrong. DL.ServerID cannot yet = SL.ServerID because DL (DatabaseList) has not yet been joined.
select DS.SupporterID, S.LastName, S.FirstName, SL.LocationID
from DatabaseSupporter DS
inner join ServerList SL
on DL.ServerID = SL.ServerID
inner join Supporter S
on DS.SupporterID = S.SupporterID
inner join DatabaseList DL
on DL.ServerDatabaseID = DS.ServerDatabaseID
This however accomplishes the same thing and allows me to specify the connections in any order since every table is represented prior to the where clause.
select DS.SupporterID, S.LastName, S.FirstName, SL.Name
from ServerList SL, Supporter S, DatabaseList DL, DatabaseSupporter DS
where DL.ServerDatabaseID = DS.ServerDatabaseID
and DS.SupporterID = S.SupporterID
and DL.ServerID = SL.ServerID
Does that make sense? Are there advantages to using INNER JOIN instead of where?
Thanks for any help.
Howard
August 15, 2010 at 2:12 pm
PHXHoward (8/15/2010)
This is how I have been writing my joins:
select A.LastName, A.FirstName, B.JobTitle
from people A
inner join employees B
on A.userID = B.userID
An Oracle DBA told me that she does it like this instead:
select A.LastName, A.FirstName, B.JobTitle
from people A, employees B
where B.userID = B.userID
That may be the preferred way in Oracle, it is not the preferred way in SQL. Stick to joins in the FROM clause with an explicit join predicate.
I asked her how she would account for outer joins using her method and she told me that in Oracle, there is a (+) symbol that goes in the where clause to indicate an outer join. The (+) did not work in the SQL Server query that I wrote. Is there another character to indicate an outer join when using her method in SQL Server?
It used to be *=, but that was deprecated and removed a couple versions ago. The replacement is to join in the FROM clause, using INNER and OUTER JOIN statements as in your first example.
When writing joins, it seems to me that the second method, using where instead of join, allows more flexibility. For example this below does not work because the order of the joins is wrong. DL.ServerID cannot yet = SL.ServerID because DL (DatabaseList) has not yet been joined.
select DS.SupporterID, S.LastName, S.FirstName, SL.LocationID
from DatabaseSupporter DS
inner join ServerList SL
on DL.ServerID = SL.ServerID
inner join Supporter S
on DS.SupporterID = S.SupporterID
inner join DatabaseList DL
on DL.ServerDatabaseID = DS.ServerDatabaseID
So switch the table order around so that it does work. It doesn't make sense to join to table that's not referenced yet.
select DS.SupporterID, S.LastName, S.FirstName, SL.LocationID
from DatabaseList DL
inner join ServerList SL on DL.ServerID = SL.ServerID
inner join DatabaseSupporter DS on DL.ServerDatabaseID = DS.ServerDatabaseID
inner join Supporter S on DS.SupporterID = S.SupporterID
Equivalent query, does work, joins in the order that makes sense.
Does that make sense? Are there advantages to using INNER JOIN instead of where?
Easier to read. Easier to tell what's joined to what and how. Much less chance of an accidental cross join because of an omitted join clause. Supported and recommended in SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2010 at 5:03 pm
Thanks for responding Gail. I'll do it the preferred way. Just looking to gain knowledge.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply