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

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

Means the same thing as this:

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

The * syntax is considered outdated, and is not in line with the ANSI standards.

Oracle has a similar construct like this:

  WHERE table1.yr (+)= table2.yr

Answer 2

To be plain and simple. This is a SQL-92 outer join operator ( more info )

Don't use it, its very old school, but its similar to LEFT JOIN, and RIGHT JOIN. All its doing is telling which side of the join is the "Parent" side, so rows on that side will be considered first.

If you try to run this on SQL 2005, it will throw an error, saying that you need to run this in compatibility mode.

Answer 3

yeap, it's another syntax for a left outer join

from
table1 left outer join table2 on table1.yr = table2.yr - 1

Answer 4

This is the old style syntax for expressing joins

Answer 5

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.