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

Answer 1

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

Answer 2

This is the old style of joins which were deprecated in ANSI SQL92. New syntax uses INNER and OUTER JOIN which join tables based on expressions rather than equality

Answer 3

A ??? outer join is specified using the symbol =* in place of = in the WHERE clause.

Answer 4

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

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

Answer 6

There are a lot of silly answers here. You didn't give the FROM clause, so there's no way to tell if your *= represents a LEFT or a RIGHT outer join.

WHERE table1.yr =* table2.yr -1

is old syntax for an outer join, for sure. But anyone who claims to know whether it's a LEFT or RIGHT outer join is mistaken. It depends on the order in which table1 and table2 are named in the FROM clause, and that's not given.