Transact-SQL shorthand join syntax?

Question

I have noticed a few times when working on legacy code, that you can do left and right outer joins in sql by using the

=*

as kind of shorthand for "right outer join" and

*=

as kind of shorthand for "left outer join" in statements like this:

select table1.firstname, table2.lastname
from table1, table2
where table1.id *= table2.id

I would guess that there are other operators like these two for the different kinds of joins, but i have not been able to find any good complete documentation about it. So do you know any good links to documentation?

I personaly think that the SQL statements i have seen using these operators are more difficult to figure out than when using the spelled out syntax, so is there any benefits using the shorthand version?

Answer 1

The =* and *= are not complaint with SQL standards, I believe these operators will be deprecated soon you should always use the standard join syntax. The other operators that you mention are confusing and need to go away, I cringe when I see these in database objects.

Answer 2

My personal opinion (after 6+ years in SQL & TSQL) is that this legacy style only makes it harder for other developers not versed in legacy syntax to easily understand your code. I always prefer a more verbose & descriptive syntax if performance is not effected - you never know when your going to have to pass the support of that code on :)

Answer 3

If you are using SQL Server, do not under any circumstances ever use that syntax. There are times when incorrect results are returned as sometimes SQL server interprets that correctly as an outer join and sometimes it interprets that syntax as a cross join. Since the result sets of the two are drastically different, you cannot ever rely onthe results from using this syntax. Further, SQL Server 2008 is the last version of SQl Server that will even allow the sysntax.

Answer 4

The reason it has unintended consequenses is because it will interpret the ENTIRE where clause as the JOIN clause. Example:

Select1:

select * from tablea left join table b on a.id=b.id where b.name = "hello world"

VS

Select2:

select * from tablea left join table b on a.id=b.id and b.name = "hello world"

These 2 selects return different results. When you write a statement like this:

select * from tablea,tableb where tablea.id *= tableb.id and b.name="hello world"

I would expect that most people would WANT the results from Select1... but you will actually get the results from Select2.

Answer 5

I wouldn't use *= or =(+) syntax since they are not compatible with other RDBMS or even in the case of MSSQL Server compatible with later versions unless you enable low compatibility levels. It is then a valid worry that at some point MS will simply drop support for it alltogether.

It took me some getting used to changing my "old" habbits.. I preferred the *= syntax because it was less to type and jived with the simpler flow of equal joins (Which are perfectly still valid and acceptable)

One of my objections to moving to using JOINS was all that typing and the mess I found in query examples using them.

Some tricks I found was just formatting issues and knowing what is really required. The use of 'INNER' and 'OUTER' are completely redundant and unecessary. Also I use brackets to delimit the end of the join clause and put each condition on its own line:

FROM blah b LEFT JOIN blah2 b2 ON (b.ID = b2.ID)
LEFT JOIN blah3 b3 ON (b.ID = b3.ID)
...

Some have said that the ANSI JOIN syntax is harder to mess up because with equal joins its easy to miss a join parameter... In practice I've had more difficulties with forgetting to say 'WHERE' and the interpreter still thinking I'm defining join conditions rather than search conditions which can lead to all sorts of difficult to find/bizzare results.