Outer Join Mystery

Question

Outer Join Mystery

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Steve Rosenbach SSCrazy Points: 2181 More actions · Answer 1

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

Eduardo Padilla SSC Enthusiast Points: 192 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 398782 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eduardo Padilla SSC Enthusiast Points: 192 More actions · Answer 4

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.

daniele Berker SSC Journeyman Points: 90 More actions · Answer 5

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.

Ian Yates SSCoach Points: 19738 More actions · Answer 6

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?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

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

Grant Fritchey SSC Guru Points: 398782 More actions · Answer 8

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Rosenbach SSCrazy Points: 2181 More actions · Answer 9

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

I love it when that happens!

Thanks for your reply, Grant!

-- SteveR

daniele Berker SSC Journeyman Points: 90 More actions · Answer 10

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