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'

Do you have a query that uses `*=` in production? If not, don't worry about this old syntax and go on with the ISO/ANSI Standard-92 `JOIN` syntax for joins. — ypercubeᵀᴹ, Dec 07 '12 at 14:38
AFAIR the 2 queries are exactly equivalent: new (1992 ?) notation against old (pre-1992) notation. **Use the new notation ONLY**. — Germann Arlington, Dec 07 '12 at 14:41

score 6 · Answer 1 · answered Dec 07 '12 at 15:09

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.

score 5 · Answer 2 · edited Mar 03 '18 at 10:42

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.

score 4 · Accepted Answer · edited Mar 03 '18 at 10:35

4

Sorry to be a little bit late, but i found the solution: In the old syntax *=, the condition b.Status = 'Y' will be in the left join on clause, so to have to same result in the first query I just moved the b.Status = 'Y' to the "on" clause.

edited Mar 03 '18 at 10:35

Jinxed

356
2
16

answered Dec 07 '12 at 18:17

R Vive L OL

177
3
3
10

score 0 · Answer 4 · answered Dec 07 '12 at 14:57

These queries look the same. You say they don't return the same results. Do you have an example?

Where the old and new join notations do differ is if you moved the where to the join

Select
  a.col1, 
  b.c1
From
  A
    Left Join
  B 
    On a.col1 = b.c1 And b.Status = 'Y';

This is different, and can't be so easily represented in the old notation (at least not in Oracle's, don't have direct experience of Sybase)

Example (albeit Oracle uses (+) instead of *=)

score 0 · Answer 5 · answered May 16 '19 at 17:08

I tried to find conversion of old queries to new queries when more than 2 tables with many where clauses are involved, but could not find it anywhere online, thus posting my solution here. The result will exactly match if you follow the trick below:

In case of below query:

    select a.*
    from A a, B b, C c
    where a.server_id=0
    and a.name = 'John'
    and a.country_id*=b.value_cd
    and b.table_nm = 'Employee'
    and b.Attribute_nm ='A_nm'
    and a.state_id*= c.value_cd
    and c.table_nm = 'Company'

All where conditions except for first table (i.e. Table A) can go into on clause in join, however Where condition with first table should remain in Where clause as in the below query:

    select a.*
    from A a
    left join B b on a.country_id = b.value_cd 
       and b.table_nm = 'Employee' 
       and b.Attribute_nm ='A_nm'
    left join C c on a.state_id = c.value_cd 
       and c.table_nm = 'Company' 
    where a.server_id=0
       and a.name = 'John'

Difference between left join and *= in Sybase ASE 15

5 Answers5

Linked