question about SQL

[cards4]

What does the syntax *= mean in SQL? For example:

WHERE
phbasic.emp_id *= phfrng.emp_id AND
phbasic.check_no *= phfrng.check_no AND
phbasic.emp_id = empaddr.emp_id

 

[SQLDenis]

Old style left outer join

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

That is equivalent to a left join. This type of syntax should be abandoned because it can cause problems.

Instead, you should do something like this...

FROM  phbasic
      Left Join phfrng
        On  phbasic.emp_id = phfrng.emp_id AND
        And phbasic.check_no = phfrng.check_no  
      Inner Join empaddr
        On phbasic.emp_id = empaddr.emp_id

Notice that the *= was converted to a left join and the = was converted to an inner join.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oooops.... I inadvertently have 2 AND's in the first ON.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

To expand on what George said, the old style left join can be interpreted differntly in SQL Server. Sometimes it wuill interpet it as a corss join which is a very bad thing. Changing this kind of code through your system should be a very high priority as it might be returning incorrect results.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

BTW, you can't even run that on SQL server 2005 it will generate this message

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/