Share via

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

  • Article

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

  • Anonymous
    January 01, 2003
    Great post, really helped me understand, thanks again!

  • Anonymous
    January 01, 2003
    @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.

  • Anonymous
    September 14, 2008
    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.

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