23

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?

user692942
  • 16,398
  • 7
  • 76
  • 175
Blaise Swanwick
  • 1,735
  • 1
  • 16
  • 18
  • Are those string/char fields? Wonder what the reasoning behind multiplying strings would be. – Marc B Jun 07 '11 at 18:26
  • 1
    Yes, they are varchar fields. I was asking myself the same question, "Why in hell would you multiply two strings?" – Blaise Swanwick Jun 07 '11 at 18:32
  • 1
    And ifyou are still using the SQl server 2000 database all instances of =* or *= need to be fixed to left joins or Right joins as the implicit join is broken even in SQl server 2000 and may NOT be returning correct results as it is sometime intepreted as a cross join. Implicit joins should never be used for outer joins in SQl Server. – HLGEM Jun 07 '11 at 20:49
  • @HLGEM can you provide source for this statement? I need to proove thsi to my coleagues? – Hurda Dec 03 '12 at 09:17
  • Direct quote from BOL:In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. – HLGEM Dec 03 '12 at 14:42
  • 1
    Rest of quote:SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax. – HLGEM Dec 03 '12 at 14:43

2 Answers2

23

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.

Niklas
  • 13,005
  • 23
  • 79
  • 119
CristiC
  • 22,068
  • 12
  • 57
  • 89
  • That's something they didn't teach me in school. I've implemented your suggestion and it worked. Thank you very much! – Blaise Swanwick Jun 07 '11 at 18:30
  • Yes, it is something I run into it a lot of times when I changed reports designed in SQL 2000. – CristiC Jun 07 '11 at 18:32
  • 1
    This post was quite a while ago, but I thought it good to mention it here. The query is an example of a classic mistake when dealing with LEFT OUTER JOIN. Because of the WHERE clause a.id = b.id, this actually becomes like an INNER JOIN. For a true LEFT OUTER JOIN, the WHERE clause should be moved up into the LEFT OUTER JOIN like: LEFT OUTER JOIN ... a.person_name = b.person_name AND a.id = b.id – umphy Nov 03 '17 at 03:19
4

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.

bsexton
  • 351
  • 2
  • 3