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

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

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:

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

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.

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.

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

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 Oracle PLSQL crowd has been having a hissy fit. <<

I love it when that happens!

Thanks for your reply, Grant!

-- SteveR

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