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:

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

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.

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

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

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

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.

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.

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