SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Outer Join Mystery


Outer Join Mystery

Author
Message
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94033 Visits: 33010
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gFritchey/outerjoinmystery.asp

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Amit Garg
Amit  Garg
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 12
This was really good one. Request you to simplify naming convention and data taken in future articles.
Auke Teeninga
Auke Teeninga
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 16

The explanation is really simple:

With the old syntax it's impossible to determine what part of the where statement is used to join the tables and which part is used to filter the records, so the database server has to guess.

With the proper syntax the part of the join which is used to join the table is after the ON and the part to filter the records is after the WHERE.

Hope it makes sense,

Auke


Auke Teeninga
Auke Teeninga
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 16

So it actually reads the old statement as

SELECT
S.IIATransactionId,
substring(rats_filename, 1+patindex('%{________-____-____-____-____________}%', rats_filename), 36) AS OracleTransactionId
FROM
iiafeedtransaction S
LEFT OUTER JOIN
ratsiiafeedtransaction o
ON
S.IIATransactionId = substring(rats_filename, 1+patindex('%{________-____-____-____-____________}%', rats_filename), 36)
and
o.Rats_filename IS NULL
ORDER BY
S.IIATransactionId


David in .AU
David in .AU
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 561
Actually, your logic is incorrect.
What are you basing your where statement on? It is where rats_filename is null.
Look at your ratsiiafeedtransaction table again.. there are no null fields in this table.
so something like the below (and this is quick and nasty) works:
select a, oracletransactionid
from (
SELECT S.IIATransactionId as a, substring(o.rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId
FROM iiafeedtransaction S, ratsiiafeedtransaction o
WHERE S.IIATransactionId *=
substring(o.rats_filename,1+patindex('%{________-____-____-____-____________}%',o.rats_filename),36)
) data
where oracletransactionid is null
order by a
FYI: SQL 2005 does not allow this style of code by default, you have to override the database to do it. And it is claimed that future version wont support it at all. even in backward compat. mode.

Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5163 Visits: 3889

Here's the official info from BOL:

Transact-SQL Joins

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either the FROM or WHERE clause. Inner joins specified in the WHERE clause do not have the same problems with ambiguity as the Transact-SQL outer join syntax.



Best Regards,

Chris Büttner
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6403 Visits: 699

As said above, this is an old 'caveat' with the legacy outer join syntax compared to ANSI joining, that has been around since day one ANSI joins was available.

The primary difference lies in how the query is resolved.
The legacy syntax has only one WHERE clause to put both join criteria and filtering criteria, where with ANSI syntax, you have a distinct section in the ON clause for the actual join, and the filtering goes into the WHERE clause.

This difference makes such questions as 'what rows exists in table A but not in table B' simply impossible to write correctly with the old legacy *= syntax, since what we really ask for in these cases are nulls that are a result of the join itself.
There are many reasons to switch habits from legacy join syntax to ANSI style, these kinds of queries is one of them.

/Kenneth





Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94033 Visits: 33010

Sorry about that. This article wrote itself since I just copied & pasted the code. Usually I'd write the code for an article seperately. Again, sorry for the confusion.



----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94033 Visits: 33010

Couldn't agree more. I personally haven't written queries using the old syntax for something like 10 years. I had just assumed during all that time, that those people, either obstinant or ignorant, writing in the old syntax were getting good data from their queries. This doesn't even address the fact that with the current syntax we can do things that could never have been done in the old:
SELECT ....
FROM Version V
INNER JOIN x
ON x.Id = V.Id
INNER JOIN y
ON x.Id = y.Id
AND y.Version = (SELECT MAX(Version)
FROM Y AS y2
WHERE y2.VersionId <= v.VersionId
AND y2.Id = y.Id)

This little dive through the old methods was an eye opener that I thought I'd share just in case others were as uninformed. There really are still a lot of people who are using the old approach.



----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94033 Visits: 33010
Because of the left join, you would get values that are null, despite the fact that there were no null values in the table. It's not dissimilar to the query you wrote to determine what's in one table, but not the other. When you join with the proper syntax it works just fine. That was the surprise.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search