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

Question

I have a query that I pulled from ms sql 2000 and plugged into a MySql query. It did not work, MySql would choke on the *= operator. In this example I have two varchar columns called person_name.

SELECT * FROM tbl1 a, tbl2 b  WHERE a.id = b.id AND a.person_name *= b.person_name 

I know in other languages myInt *= myTotal could also be read as myInt * myInt = myTotal. However, I'm working with varchars that contain all chars, no integers. I wrote it out like:

AND a.person_name * a.person_name = b.person_name 

Voila! It appears to have worked. Can somebody explain what is happening? Is the *= operator converting the chars to their integer equivalents or? And why couldn't I find this operator anywhere on the web?

Answer 1

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.

Answer 2

In MSSQL, the *= convention in the WHERE clause indicates a join. So what you are really seeing is a LEFT OUTER JOIN between tbl1 and tbl2 on person_name where all the values from tbl1 and the matching values on tbl2 will be returned.