What does =* mean?

Question

I'm trying to trace some SQL in Microsoft Server. I came across a join that is using a convention unfamiliar to me. What does "=*" mean?

WHERE table1.yr =* table2.yr -1

Solution 1

This:

WHERE t.column =* s.column

...is old TSQL (pre SQL Server 2005) outer join syntax, and is not an ANSI JOIN.

Reference: SQL Server 2005 Outer Join Gotcha

Solution 2

I believe that is old syntax indicating an outer join condition from table1 to table2

Old style:

SELECT * FROM table1, table2
WHERE table1.yr =* table2.yr -1

New style (SQL92):

SELECT * FROM table2 
LEFT OUTER JOIN table1 ON table1.yr = table2.yr - 1

Solution 3

This is the old style syntax for expressing joins

Solution 4

It means the code needs to be replaced immediately! This style join is supposed to be a right join. Unfortunately it will sometimes be interpreted as a cross join, so the results of using this join may not be correct. Also, this syntax is deprecated and cannot be used inteh next version of SQl server.

Solution 5

That is the ANSI SQL 1989 syntax for RIGHT OUTER JOIN, where *= would be the LEFT OUTER JOIN.

You should note also that putting the join syntax in the WHERE clause is deprecated in SQL 2008. http://scarydba.wordpress.com/2009/09/15/no-join-predicate/ <== A timely article on this.

View more solutions