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 Monday, May 15, 2006 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 11:26 AM
Points: 3, Visits: 14

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)
       )TMP
WHERE 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.

 

 

 




Post #280157
Posted Monday, May 15, 2006 2:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160

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.




Post #280187
Posted Monday, May 15, 2006 6:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 16, 2006 4:37 AM
Points: 1, Visits: 1

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

Post #280223
Posted Monday, May 15, 2006 9:36 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

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




Post #280233
Posted Tuesday, May 16, 2006 1:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

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

 




Post #280261
Posted Tuesday, May 16, 2006 4:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 PM
Points: 13,999, Visits: 28,378

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.



----------------------------------------------------
"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 #280294
Posted Tuesday, May 16, 2006 4:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 PM
Points: 13,999, Visits: 28,378

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.



----------------------------------------------------
"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 #280295
Posted Tuesday, May 16, 2006 4:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 PM
Points: 13,999, Visits: 28,378

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, column
FROM...

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



----------------------------------------------------
"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 #280298
Posted Tuesday, May 16, 2006 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 23, 2007 7:06 AM
Points: 1, Visits: 1
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
Post #280331
Posted Tuesday, May 16, 2006 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 5, 2012 12:03 PM
Points: 117, Visits: 163
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
IIATransactionId
FROM
dbo.IIAFeedTransaction
WHERE
IIATransactionId IN (
SELECT SubString(RATS_FILENAME,1+PatIndex('%{________-____-____-____-____________}%',RATS_FILENAME),36)
FROM dbo.RATSIIAFeedTransaction
)
Post #280428
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse