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 Tuesday, May 16, 2006 10:39 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:09 PM
Points: 91, Visits: 198

Very interesting discussion, thanks. I left the old-style syntax long ago because of potential for uninteneded results.

Just curious, does Oracle *STILL* not support the ANSI-92 JOIN syntax in its current version, or did they finally come around?

Best regards,
SteveR




Post #280441
Posted Tuesday, May 16, 2006 11:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 10:14 AM
Points: 45, Visits: 12

I think in that case the problem is not the old or the new syntax I think it was an error in how the query was written.

Just write the following and it will give yoy the same result:

(note that I don't use the outer join symbol, it is not necessary here!).

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

Post #280464
Posted Wednesday, May 17, 2006 6:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 14,004, Visits: 28,385

Hmmm... Interesting.

A couple of problems though. I wanted to get a list of values that exist in one table and not the other. I also tried running it with the value set to 'IS NULL' and got no results. I then took the 'IS NOT NULL' completely out of the query and it still returned the results below. Also, when you look at the query plan generated between the left outer join & your query, yours results in a hash table for the join which is going to seriously impact performance on large scale queries (which, admittedly, the sample data in the article is teeny-tiny, but the real data is several hundred thousand rows, again, not big, but big enough to notice a hash join).

When I ran your query, I got this:

IIATransactionId                                       OracleTransactionId
------------------------------------                 ------------------------------------
365A0FD8-5042-4297-A082-8F5B11450AF4 365A0FD8-5042-4297-A082-8F5B11450AF4
16706611-C94D-4FBC-8F4E-9077C3B9E697 16706611-C94D-4FBC-8F4E-9077C3B9E697
55DD6703-9693-45E2-A339-987066EA2864 55DD6703-9693-45E2-A339-987066EA2864
F4849137-6454-46E5-9811-A6985A189249 F4849137-6454-46E5-9811-A6985A189249

instead of this:

IIATransactionId                                      OracleTransactionId
------------------------------------                ------------------------------------
9B33A776-408B-4928-AE2A-0FF43995DE12 NULL
8EEA51CE-D87A-4F76-B9C1-7AD11532D444 NULL
5D31FD56-7C0B-408B-83A5-864B7BD35ADC NULL
A721BD38-9C76-4C3A-A7C5-9CC0227F90F4 NULL

 

 



----------------------------------------------------
"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
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 #280666
Posted Wednesday, May 17, 2006 2:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 10:14 AM
Points: 45, Visits: 12

I am sorry I misunderstood the whole thing and I sent you the opposite answer.

This is my query then:

SELECT S.IIATransactionId
 ,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId
FROM iiafeedtransaction S,
ratsiiafeedtransaction o
WHERE S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)
AND  o.Rats_filename IS NULL
AND S.IIATransactionId Not In  (SELECT substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) FROM ratsiiafeedtransaction)
ORDER BY S.IIATransactionId

which I am pretty sure you figured it out.

 

Post #280867
Posted Wednesday, May 17, 2006 3:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 18, 2006 12:33 PM
Points: 2, Visits: 1

Here is just another way to do the query...  I'm also someone with a heavy Oracle background that has been working with SQL.

I ran it through the execution plan and it seems to run pretty efficiently.  Since the only thing that was really wanted out of the query was the records that didn't exist in the ratsiiafeedtransation table, this would also work.

select IIATransactionId
from iiafeedtransaction
where not exists (select * from ratsiiafeedtransaction
                  where IIATransactionId = substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36))
ORDER BY IIATransactionId

The reason for adding the where clause to the subquery is so that the subquery only scans for what is needed instead of a full table scan.  If this table had an index on the field being queried it would be even more efficient.

Post #280873
Posted Wednesday, May 17, 2006 10:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Someone earlier asked a good question - does Oracle support the newer syntax?  (I might have to extract data from an Oracle system soon - would hate to have to write in different syntax for something as simple as joins!).  Also, how did Oracle get the correct results from the possibly ambiguous query - does it make a better guess about join conditions vs filtering conditions?


Post #280941
Posted Thursday, May 18, 2006 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

You're absolutely correct, a not exists construct will also produce the same (correct) result for the question 'which rows in A doesn't exist in B'.

There are truly many ways to skin a cat with SQL, but since this isn't an outer join, it sort of falls outside the discussion.

/Kenneth




Post #280974
Posted Thursday, May 18, 2006 5:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 14,004, Visits: 28,385

Second question first. How did Oracle process the query correctly? I haven't a clue. It's optimizer must work in a different way, logically, than SQL Servers. As to the first quest, yes, Oracle has not only introduced ANSI 92 joins, but they're pushing total ANSI 99 (?) compliance. They're finally threatening what MS has been threatening for years, no support for the old standard in the next release. While the majority of SQL Server TSQL developers are going to shrug their shoulders if it goes away, the Oracle PLSQL crowd has been having a hissy fit. Our local DM people aren't happy about it. Especially when I constantly tell them "Hey, it'll make it easier when we switch the whole warehouse over to SQL Server." I love watching their blood pressure go up.

For the others who've offered alternatives, 'IN' and 'EXISTS' in the where clause are inherently less performant than a proper JOIN, INNER OR OUTER, in most cases. If I had to pick one alternative to using the JOIN, I'd go with the 'EXISTS' clause.



----------------------------------------------------
"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
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 #281003
Posted Thursday, May 18, 2006 6:33 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:09 PM
Points: 91, Visits: 198

>>the Oracle PLSQL crowd has been having a hissy fit. <<

I love it when that happens!

Thanks for your reply, Grant!

-- SteveR




Post #281023
Posted Thursday, May 18, 2006 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 18, 2006 12:33 PM
Points: 2, Visits: 1

Ok, here goes.... Some people felt and probably correctly that I did not fully explain what when wrong with the 1st 2 queries in the discussion.

 

Query1:

 

SELECT S.IIATransactionId,

       substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId

FROM   iiafeedtransaction S, ratsiiafeedtransaction o

WHERE  S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)

aND    o.Rats_filename is not NULL

ORDER BY S.IIATransactionId

 

 

Did not return the expected results because there o.rats_filename column does not contain nulls.  The nulls are returned as a result of the outer join.  So it correctly returned information based solely of the data in the tables being evaluated.

 

Query 2:

 

SELECT S.IIATransactionId

 ,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId

FROM iiafeedtransaction S,

ratsiiafeedtransaction o

WHERE S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)

AND  o.Rats_filename IS NULL

ORDER BY S.IIATransactionId

 

 

Returned with all the second column values being set to NULL because again there are no NULL's in the o.rats_filename column.  This meant that there where no matches to the Outer Join since it was limited to only the NULL values in the table.

 

Query 3:

 

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)

WHERE o.Rats_filename IS  NULL

ORDER BY S.IIATransactionId 

 

 

Although, it did supply the desired results was not as efficient as the query I posted.  I was only trying in my posting to supply another example of how it could be done.

 

Some of the postings seemed to be getting lost in the "substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)" and the examples posted where overly complicated which would burn up processing time.  I realize that in an age where processors are very quick and memory seems hard to exhaust.  But, If a Query is overly complicated and you are trying to process Thousands of records... Every little piece of time can add up.

 

I hope this helps...

 

Post #281049
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse