I am attempting to trace some SQL in Microsoft Server. I discovered a join that's utilizing a convention unfamiliar in my experience. Exactly what does "=*" mean?

WHERE table1.yr =* table2.yr -1

This:

WHERE t.column =* s.column

...now has wrinkles TSQL (pre SQL Server 2005) outer join syntax, and isn't an ANSI JOIN.

Reference: SQL Server 2005 Outer Join Gotcha

In my opinion that's old syntax showing an outer join condition from table1 to table2

Old-style:

Choose * FROM table1, table2

WHERE table1.yr =* table2.yr -1

New style (SQL92):

Choose * FROM table2

LEFT OUTER JOIN table1 ON table1.yr = table2.yr - 1

This is actually the old-style syntax for indicating joins

This means the code must be changed immediately! This style join should be the right join. Regrettably it'll be construed like a mix join, therefore the outcomes of by using this join might not be correct. Also, this syntax is deprecated and can't be utilized inteh next version of SQl server.

That's the ANSI SQL 1989 syntax for RIGHT OUTER JOIN, where *= will be the LEFT OUTER JOIN.

You need to note additionally that putting the join syntax within the WHERE clause is depricated in SQL 2008. http://scarydba.wordpress.com/2009/09/15/no-join-predicate/ <== A timely article about this.

This is actually the old-style of joins that have been deprecated in ANSI SQL92. New syntax uses INNER and OUTER JOIN which join tables according to expressions instead of equality

A ??? outer join is specified while using symbol =* instead of = within the WHERE clause.

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


from

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

You will find lots of silly solutions here. You did not provide the FROM clause, so there is no method to know if your *= signifies a LEFT or perhaps a RIGHT outer join.

WHERE table1.yr =* table2.yr -1

now has wrinkles syntax to have an outer join, without a doubt. But anybody who states know it could be a Right or left outer join is mistaken. It is dependent around the order by which table1 and table2 are named within the FROM clause, and that is not given.

Choose *

FROM table1, table2

WHERE table1.yr =* table2.yr -1

Means exactly the same factor because this:

  Choose *

  FROM

    table2

    LEFT OUTER JOIN

    table1

    ON table1.yr = (table2.yr - 1)

The * syntax is recognized as outdated, and isn't good ANSI standards.

Oracle includes a similar construct such as this:

  WHERE table1.yr (+)= table2.yr

To become basically. This can be a SQL-92 outer join operator ( more information )

Avoid using it, its early school, nevertheless its much like LEFT JOIN, and RIGHT JOIN. Its doing is telling which argument from the join may be the "Parent" side, so rows on that side is going to be considered first.

By trying to operate this on SQL 2005, it'll throw a mistake, saying you need to run this in compatibility mode.