Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing

Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing

  • Comments 4
  • Likes

UPDATE (15 September 2008): per Adam Machanic's comment, this syntax is in fact deprecated in compatibility mode 9.0 databases (e.g. SQL Server 2005).  Thanks, Adam! 

I learned something interesting recently (well, not too recently; I'll be going through my inbox in the next week or so catching up on blog-compatible content) which seems worthy of sharing.

There's been a lot of talk about the deprecation of "old style" (ANSI-92) JOIN syntax in SQL Server 2008.  Consider the following old-style LEFT OUTER JOIN:

SELECT  tableA.id, tableB.id
FROM
    tableA, tableB
WHERE   tableA.id *= tableB.id

The table citation portion of the query (FROM tableA, tableB) is ANSI standard and is NOT being deprecated.  The LEFT OUTER JOIN syntax (*=) is not ANSI standard and IS deprecated as of SQL Server 2005.

So my old friend Brian can keep separating his tables with commas just as he's been doing..  but if he wants to do a LEFT or RIGHT OUTER JOIN, he'll need to use the "new" syntax.

Thanks to the always-knowledgeable Umachandar Jayachandran for sharing this information.

-wp

Comments
  • Hi Ward,

    I hate to break it to you, but you're about three years too late reporting this; this syntax was first deprecated in SQL Server 2005, for any databases in 90 compatibility mode.

  • Great post, really helped me understand, thanks again!

  • *= is left outer join. In order to get left outer join we just put the * sign on the other side of equivalence '='

  • @Pru: it's unclear to me what you're saying here..  you've accurately identified the syntax for sure.  *= and =* for left and right outer joins are deprecated as of SQL 2005.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment