Comparison Operator *= ?

[Bontebok]

Hi Everyone,

I am looking at a stored procedure and in the WHERE clause of the final query, I notice a *= used as a comparison operator. What does this do? In all of my experience with mysql, postgresql, and mssql, I've never seen this used before.

Here is a sample:

FROM #Budget, #Expended, #Unposted, #Emp
WHERE #Budget.JobID *= #Expended.JobID and #Budget.Period *= #Expended.FPCode and
#Budget.JobNumber *= #Unposted.job_code and #Budget.Period *= #Unposted.date_applied and
#Budget.Year = @Year and #Expended.Year = @Year and #Unposted.Year = @Year and
#Budget.JobNumber *= #Emp.JobNumber and
#Emp.JobRole = 'Project Manager'
GROUP BY #Budget.JobID, #Budget.JobNumber, #Emp.JobName, #Emp.FullName

Jeremy

 

[hneal98]

It is old code for left outer join.

 

[Bontebok]

Strange, it made sense looking at it, but I ended up building some #temp tables in the query analyzer and discovered that it was a left outer join (and of course =* is a right outer join).

I'll be rewriting this stored procedure.

Thanks,
Jeremy

 

[SQLSister]

From Books Online:

"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. 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."

AS you can see this is a dangerous practice that does not always return the correct results. You should replace this with outer joins.