Difference between left join and *= in Sybase ASE 15

Question

I need help to understand this matter, what's the difference between the 2 queries below, all I know that they don't return the same result.

Query 1:

SELECT a.col1, b.c1
  FROM A a
  LEFT JOIN B b
    ON a.col1 = b.c1
 WHERE b.status = 'Y'

Query 2:

SELECT a.col1, b.c1
  FROM A a, B b
 WHERE a.col1 *= b.c1
   AND b.status = 'Y'

Answer 1

The first query:

SELECT
       a.col1, b.c1 
FROM 
       a LEFT JOIN b ON a.col1 = b.c1 
WHERE
       b.status = 'Y' ;

is equivalent to an inner join because the b.status column (from the right side of a left outer join) is used in the WHERE part:

SELECT
       a.col1, b.c1 
FROM 
       a INNER JOIN b ON a.col1 = b.c1 
WHERE
       b.status = 'Y' ;

---

The 2nd query is (probably) executed as:

SELECT
       a.col1, b.c1 
FROM 
       a LEFT JOIN b ON a.col1 = b.c1 
                    AND b.status = 'Y' ;

which may give different results as it is a (logically) different query.

That's one of the reasons you should never use this old syntax. It is ambiguous sometimes, e.g. when there are more than one conditions or more than one outer joins.

Answer 2

I know Sybase and SQL Server are closely related. The *= has been removed from SQL Server but even as far back as SQL Server 2000, it was not working correctly, sometimes interpreting as a left join and sometimes as cross join. Since Sybase and SQL Server came from the same base product, I would suspect this is also your problem with it and why the results are different. Do not use the implicit join for an outer join as it will not reliably give the correct answer.

Here is a direct quote from Books Online for SQL Server 2000 that discusses this issue:

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.