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.