SQLServerCentral / Discuss Content Posted by Grant Fritchey / Article Discussions / Article Discussions by Author / Outer Join Mystery / Latest Posts

RE: Outer Join Mystery

daniele Berker — Thu, 18 May 2006 07:35:00 GMT

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

RE: Outer Join Mystery

Steve Rosenbach — Thu, 18 May 2006 06:33:00 GMT

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

I love it when that happens!

Thanks for your reply, Grant!

-- SteveR

RE: Outer Join Mystery

Grant Fritchey — Thu, 18 May 2006 05:14:00 GMT

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.

RE: Outer Join Mystery

Kenneth Wilhelmsson — Thu, 18 May 2006 02:14:00 GMT

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

RE: Outer Join Mystery

Ian Yates — Wed, 17 May 2006 22:22:00 GMT

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?

RE: Outer Join Mystery

daniele Berker — Wed, 17 May 2006 15:01:00 GMT

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

RE: Outer Join Mystery

Eduardo Padilla — Wed, 17 May 2006 14:41:00 GMT

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 OracleTransactionIdFROM iiafeedtransaction S,ratsiiafeedtransaction oWHERE S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)AND o.Rats_filename IS NULLAND 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.

RE: Outer Join Mystery

Grant Fritchey — Wed, 17 May 2006 06:47:00 GMT

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-8F5B11450AF416706611-C94D-4FBC-8F4E-9077C3B9E697 16706611-C94D-4FBC-8F4E-9077C3B9E69755DD6703-9693-45E2-A339-987066EA2864 55DD6703-9693-45E2-A339-987066EA2864F4849137-6454-46E5-9811-A6985A189249 F4849137-6454-46E5-9811-A6985A189249

instead of this:

IIATransactionId OracleTransactionId------------------------------------ ------------------------------------9B33A776-408B-4928-AE2A-0FF43995DE12 NULL8EEA51CE-D87A-4F76-B9C1-7AD11532D444 NULL5D31FD56-7C0B-408B-83A5-864B7BD35ADC NULLA721BD38-9C76-4C3A-A7C5-9CC0227F90F4 NULL

RE: Outer Join Mystery

Eduardo Padilla — Tue, 16 May 2006 11:18:00 GMT

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

RE: Outer Join Mystery

Steve Rosenbach — Tue, 16 May 2006 10:39:00 GMT

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

RE: Outer Join Mystery

Dennis D. Allen — Tue, 16 May 2006 10:17:00 GMT

There was a post that hit the nail on the head. Your where clause that stipulates that RAT_FILENAME IS NOT NULL does not limit results due to there being no NULL RAT_FILENAME columns... You want to look for NULLs after the join is complete.More to the point would be something like;/* return all IIAFeedTransaction.IIATransactionId values that are * not used in the RATSIIAFeedTransaction.RAT_FILENAME values */SELECT IIATransactionIdFROM dbo.IIAFeedTransactionWHERE IIATransactionId IN ( SELECT SubString(RATS_FILENAME,1+PatIndex('%{________-____-____-____-____________}%',RATS_FILENAME),36) FROM dbo.RATSIIAFeedTransaction )

RE: Outer Join Mystery

Vinay Singhania — Tue, 16 May 2006 06:41:00 GMT

Hi,I had the same issue couple of days back and I solved it by same way (removing and clause and putting where clause).VS

RE: Outer Join Mystery

Grant Fritchey — Tue, 16 May 2006 04:45:00 GMT

Amen on the aliasing brother.

We've been reviewing some code from developers that not only didn't use aliasing, but used table names instead. Unfortunately they didn't even do that consistently so we end up with

SELECT table.column, dbo.table.column, columnFROM...

We're getting out the hickory stick for the next meeting with this dev team...

RE: Outer Join Mystery

Grant Fritchey — Tue, 16 May 2006 04:31:00 GMT

That's how I "solved" the problem when it was first presented to me. I couldn't read it in a way that made sense with the old syntax so I rewrote it and it was suddenly working.

My Oracle-centric coworker held a gun to my head until I tried it several times both ways. It was just a hoot watching the data changing based on syntax only.

RE: Outer Join Mystery

Grant Fritchey — Tue, 16 May 2006 04:27:00 GMT

Doggone it. I was looking through Itzik's stuff for answer. I guess I didn't dig deep enough. Thanks for posting that.

Itzik's one of the minor dieties around my shop ever since we spent a week with him doing advanced TSQL training.

RE: Outer Join Mystery

Kenneth Wilhelmsson — Tue, 16 May 2006 01:35:00 GMT

As with most things, it's a matter of habit.Things we know are 'easy', things we don't know are 'difficult'.There's no rocketscience about that.

I started out writing T-SQL the old way, and switched to ANSI style sometime around y2k.At that time ANSI was hard, difficult, weird, strange, awkward... take your pick.Of course that was because in the beginning it was something that I didn't knew too well.But with practice comes perfect, and once you get used to it there are no regrets.

ANSI is way more clear, concise and easy to write and moreover, to read. There's no doubt when you read a query what the author intended with it when it was written (assuming it was written correctly ofc) The major point is that there's no ambiguity for outer joins - a quite tricky area to 'get right'.

I should add as a final note a plug for aliasing. Always alias everything. ANSI outer joins with aliases on all columns and tables is pretty much self-documenting code.

/Kenneth

RE: Outer Join Mystery

Ian Yates — Mon, 15 May 2006 21:36:00 GMT

I've always used the newer syntax as it made things obvious and clear to me... I suppose if I had grown up using the old syntax I might think differently - but I also shudder to think of the difficulties I would've found trying to express what I needed in a query using the old syntax when the new syntax is just so easy and straightforward to both write, debug and explain to others. Yay for new syntax - or, as far as I was concerned, the only syntax anyway

RE: Outer Join Mystery

S P-328547 — Mon, 15 May 2006 18:44:00 GMT

SELECT *FROM ( SELECT 'Naughty Optimizer; Real developers use parenthesis to clarify default behavior AND SPECIFY REQUIRED BEHAVIOR.' AS 'My two cents', 'Using SELECT ColTitle = Expr is nice for swapping UPDATE for SELECT when Expr is a col name in UPDATE target, but this is legacy syntax' AS 'Farewell Old Friend' WHERE 1 = 1 AS Moral_of_Story__Use_The_New_Syntax

RE: Outer Join Mystery

Scott Coleman — Mon, 15 May 2006 14:43:00 GMT

You might think "there should be no problem with what to join together" because you put parentheses around the derived table, but the query optimizer has its own agenda sometimes. What did the execution plan look like? You have got to give the query compiler credit, it's not easy to fool it with an extra level of parenthesis.

A working hypothesis might be that in SQL Server once you use an old=style join then all WHERE clauses that refer to tables A or B (either singly or together) are interpreted as join conditions. I don't know this to be true, but it explains the examples you've presented. The difference in Oracle may be that it only uses conditions on both tables for joining, while conditions on only one table are used for filtering.

RE: Outer Join Mystery

Skip Hansen — Mon, 15 May 2006 13:23:00 GMT

Interesting article. I'm glad I always use the newer syntax. To the poster who said the problem was that the problem was not knowing which part of the where to apply, I would have to disagree. It is something more than that, although I don't know what. For example, I tried the following:

SELECT testnull = CASE WHEN tmp.Rats_filename IS NULL THEN 'NULL ' WHEN tmp.Rats_filename IS NOT NULL THEN 'NOT NULL' ELSE 'OTHER' END , *FROM (SELECT * FROM UUG_APP_DVLP.dbo.[IIAFeedTransaction] s , UUG_APP_DVLP.dbo.[RATSIIAFeedTransaction] o WHERE S.IIATransactionId *= substring(rats_filename,1+ patindex ('%{________-____-____-____- ____________}%',rats_filename),36) )TMPWHERE tmp.Rats_filename IS NOT NULL ORDER BY 2

For the above, there should be no problem with what to join together. The second WHERE should work properly, and I would expect it to work the same as the CASE statement. But it doesn't!!!. See results below:

Testnull	IIATransactionId		RATS_FILENAME	DATE_ENTERED
NULL	9B33A776-408B-4928-AE2A-0FF43995DE12	1	NULL	NULL
NULL	8EEA51CE-D87A-4F76-B9C1-7AD11532D444	0	NULL	NULL
NULL	5D31FD56-7C0B-408B-83A5-864B7BD35ADC	1	NULL	NULL
NOT NULL	365A0FD8-5042-4297-A082-8F5B11450AF4	1	CISF-{260021}-{365A0FD8-5042-4297-A082-8F5B11450AF4}-{7A050246-59F3-4E72-BFD2-40EB183B7D3C}	2006-05-03 13:46:21.470
NOT NULL	16706611-C94D-4FBC-8F4E-9077C3B9E697	1	CISF-{260346}-{16706611-C94D-4FBC-8F4E-9077C3B9E697}-{73916523-EC71-4989-BE99-1EDB507D67DF}	2006-05-03 13:46:21.470
NOT NULL	55DD6703-9693-45E2-A339-987066EA2864	2	RA-1-{259590}-{55DD6703-9693-45E2-A339-987066EA2864}-{EF41CFCE-20BA-4D2D-A4C8-121BFC7DAEE3}	2006-05-03 13:46:21.470
NULL	A721BD38-9C76-4C3A-A7C5-9CC0227F90F4	0	NULL	NULL
NOT NULL	F4849137-6454-46E5-9811-A6985A189249	1	RA-2-{219960}-{F4849137-6454-46E5-9811-A6985A189249}-{F7267BD6-62CB-4379-8533-C413DD5EA402}	2006-05-03 13:46:21.470

Moral of this story is: Stay with the new syntax.

RE: Outer Join Mystery

Jeff B. — Mon, 15 May 2006 13:04:00 GMT

Itzik Ben-Gan wrote an article that explains this very clearly in SQL Magazine in the October 2004 issue. The InstantDoc ID is #43681.

The problem is that in Old Style Joins, you can't separate the join clause and the Where Clause so the result may include extraneous NULL values in your result set.

Here are two simple queries you can run in PUBS to see this:

-- Old Style Join-- 13 Valid Entries, 6 Invalid Entriesselect o.name, i.name,i.indidfrom sysobjects o, sysindexes iwhere o.id *= i.id ando.type = 'S' andi.indid = 1

-- New Style Join-- 13 Valid Entries, 0 Invalid Entriesselect o.name, i.name,i.indidfrom sysobjects o left outer join sysindexes i on o.id = i.idwhere o.type = 'S' andi.indid = 1

RE: Outer Join Mystery

Grant Fritchey — Mon, 15 May 2006 13:04:00 GMT

Thanks. That makes more sense than any other explanation I've been able to come up with.

RE: Outer Join Mystery

Grant Fritchey — Mon, 15 May 2006 13:00:00 GMT

It simply returns the anticipated results, which is only those rows that don't match. Precisely how... you need to ask someone that understands Oracle. I think Scott Coleman's answers have been more precise than anything I'm going to offer up.

RE: Outer Join Mystery

Scott Coleman — Mon, 15 May 2006 12:45:00 GMT

The use of the old-style syntax promoted the entire WHERE clause to the ON clause. You get the same results as the "wrong" query if you used "LEFT JOIN ... ON S. = O. AND o.rats_filename IS NOT NULL". The IS NOT NULL condition is being used as a a join condition instead of a filter condition and, since none of the fields are null, has no effect.

The only mystery to me is how Oracle differentiates join conditions and filter conditions. Maybe only logical comparisons involving both tables are considered join conditions?

RE: Outer Join Mystery

Scott Strickland — Mon, 15 May 2006 12:25:00 GMT

So, how does Oracle return the "right" results?

RE: Outer Join Mystery

R Tierney — Mon, 15 May 2006 11:30:00 GMT

Because you dont have control over the order of WHERE clause execution, the WHERE condition can occur before the JOIN condition, meaning that the WHERE occurs on the *Cartesian Product* of the two tables, and therefore doesnt filter rows properly.

If you think about this closely enough, it makes sense.

RE: Outer Join Mystery

Mark_Horton — Mon, 15 May 2006 10:31:00 GMT

To get the Execution plans to match between the old and new syntax, the "old style" join query needs to look something like this:

SELECTS.IIATransactionId ,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionIdFROM iiafeedtransaction S,ratsiiafeedtransaction oWHERE S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)GROUP BY S.IIATransactionId,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)HAVING substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) IS NULLORDER BY S.IIATransactionId

Using the GROUP BY and the HAVING clauses puts the filter back into the execution plan and the correct results are seen.

Far easier to just use the new style of joins

Regards,

Mark Horton.

RE: Outer Join Mystery

amit gurjar — Mon, 15 May 2006 09:31:00 GMT

Hi,

This article & discussion is very interesting and it helped me a lot in understanding the Outer Join Query plus the old vs. new syntax.

Thanks once again

Regards

Amit Gurjar

RE: Outer Join Mystery

Grant Fritchey — Mon, 15 May 2006 07:58:00 GMT

A lot of it, unfortunately not all of it, comes from Oracle people. But even Oracle is starting down the path of eliminating the old style syntax. If you go out & search on the Oracle blogs & newsgroups, it's a big deal that lots of them are very unhappy about.

RE: Outer Join Mystery

David in .AU — Mon, 15 May 2006 07:38:00 GMT

True and correct. I just put in the example code for the old style. I had actually noticed the execution plans were pretty much identical.

How people can still write in that scares me

RE: Outer Join Mystery

Grant Fritchey — Mon, 15 May 2006 06:11:00 GMT

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.

RE: Outer Join Mystery

Grant Fritchey — Mon, 15 May 2006 06:06:00 GMT

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 VINNER JOIN xON x.Id = V.IdINNER JOIN yON x.Id = y.IdAND y.Version = (SELECT MAX(Version)FROM Y AS y2WHERE y2.VersionId <= v.VersionIdAND 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.

RE: Outer Join Mystery

Grant Fritchey — Mon, 15 May 2006 05:53:00 GMT

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.

RE: Outer Join Mystery

Kenneth Wilhelmsson — Mon, 15 May 2006 05:37:00 GMT

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

RE: Outer Join Mystery

Christian Buettner-167247 — Mon, 15 May 2006 02:47:00 GMT

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.

RE: Outer Join Mystery

David in .AU — Mon, 15 May 2006 00:27:00 GMT

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.

RE: Outer Join Mystery

Auke Teeninga — Mon, 15 May 2006 00:17:00 GMT

So it actually reads the old statement as

RE: Outer Join Mystery

Auke Teeninga — Mon, 15 May 2006 00:04:00 GMT

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

RE: Outer Join Mystery

Amit Garg-262782 — Sun, 14 May 2006 23:40:00 GMT

This was really good one. Request you to simplify naming convention and data taken in future articles.

Outer Join Mystery

Grant Fritchey — Fri, 14 Apr 2006 18:17:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gFritchey/outerjoinmystery.asp