Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Outer Join Mystery Expand / Collapse
Author
Message
Posted Friday, April 14, 2006 6:17 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #273440
Posted Sunday, May 14, 2006 11:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 14, 2012 9:17 PM
Points: 15, Visits: 12
This was really good one. Request you to simplify naming convention and data taken in future articles.
Post #279895
Posted Monday, May 15, 2006 12:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 6:38 AM
Points: 29, 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

Post #279897
Posted Monday, May 15, 2006 12:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 6:38 AM
Points: 29, 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

Post #279898
Posted Monday, May 15, 2006 12:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, August 31, 2014 6:29 PM
Points: 522, Visits: 555
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.
 
Post #279899
Posted Monday, May 15, 2006 2:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 AM
Points: 2,842, Visits: 3,875

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
Post #279907
Posted Monday, May 15, 2006 5:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

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

 




Post #279925
Posted Monday, May 15, 2006 5:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #279928
Posted Monday, May 15, 2006 6:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #279932
Posted Monday, May 15, 2006 6:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #279934
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse