SQL Server *= Operator

SQL Server *= Operator?

Remove this code immediately and replace with a left join. This code does not always interpret correctly (Sometimes SQL Server decides it is a cross join) even in SQL Server 2000 and thus can give incorrect results! Also it is deprecated for the future (Using Outer Joins, SQL Server 2000 documentation archived from the original).

I'm going to add that in adjusting to left joins you should remove all of those other implicit joins as well. The implicit join syntax has been obsolete since 1992, there is no excuse for it still being in production code. And mixing implicit and explicit joins can give unexpected results.

---

SQL Server *= operator

Kill the deprecated syntax if you can, but:

*= (LEFT JOIN)

=* (RIGHT JOIN)

What does =* mean?

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

=* operator in sql

=* is an old way to write right outer joins. For example:

select  *
from    A
right outer join
        B
on      A.bid = B.id

Is written in the old style like:

select  *
from    A
,       B
where   A.bid =* B.id

What is this operand (*= star-equals) in SQL server 2000?

In SQL 2000 this was used as a LEFT OUTER JOIN

=* is a RIGHT OUTER JOIN

Your query could be:

SELECT 
  * 
FROM 
  tbl1 a LEFT OUTER JOIN tbl2 b ON a.person_name = b.person_name
WHERE 
  a.id = b.id

As stated here:

Specifies an outer join using the
nonstandard product-specific syntax
and the WHERE clause. The *= operator
is used to specify a left outer join
and the =* operator is used to specify
a right outer join.

What Does *= means in WHERE Clause in TSQL?

Using asterisk in a WHERE is an old non-ANSI compliant syntax for OUTER JOINing tables and therefore should not be used anymore.

Here's the link.

What do the *= and =* operators do in T-SQL?

That is the old and no longer recommended way of specifying table joins.

The modern equivalent to what you're seeing would be:

SELECT * 
FROM foo_table
LEFT JOIN bar_Table ON foo_table.id = bar_table.fac_id

Should I use != or for not equal in T-SQL?

Technically they function the same if you’re using SQL Server AKA T-SQL. If you're using it in stored procedures there is no performance reason to use one over the other. It then comes down to personal preference. I prefer to use <> as it is ANSI compliant.

You can find links to the various ANSI standards at...

http://en.wikipedia.org/wiki/SQL

---

Related Topics