Sybase ASE: Left outer join

Sybase ASE supports both the old syntax and the newer SQL-92 syntax for left outer joins:

Old syntax:

SELECT * FROM table1, table2 WHERE table1.key*=table2.fkey

New syntax:

SELECT * FROM table1 LEFT JOIN table2 ON table1.key=table2.fkey

As long as you do not have other criteria, the results will be the same. But you might experience some differing results as soon as you add some other criteria e.g. the two following statements seem to do the same but do deliver different results:

1> select top 10 p.p_key, e.e_uid, e.field1 from table_p p, table_e e where p.p_key*=e.p_key and e.field1='V1'
2> go
 p_key       e_uid            field1
 ----------- ---------------- ----------------
           2 2005092612595815 V1
           2 2005030715593204 V1
           2 2005092614251692 V1
           4 NULL             NULL
           8 NULL             NULL
           9 NULL             NULL
          10 NULL             NULL
          11 NULL             NULL
          14 NULL             NULL
          15 NULL             NULL

(10 rows affected)
1> select top 10 p.p_key, e.e_uid, e.field1 from table_p p left join table_e e on p.p_key=e.p_key where e.field1='V1'
2> go
 p_key       e_uid            field1
 ----------- ---------------- ----------------
     1057606 2006100212531641 V1
     1093639 2006100215370890 V1
     1015380 2006100410065929 V1
     1093639 2006100215370949 V1
     1029807 2006100508354802 V1
     1029807 2006100508402832 V1
     1044378 2006100509331826 V1
     1092232 2006100510385895 V1
     1030314 2006100513585134 V1
     1093947 2006100606211859 V1

(10 rows affected)

The reason is that the database engines when executing the first statement does not only consider p.p_key=e.p_key as join criterion but also e.field1='V1'. So basically the first statement is equivalent to the following SQL-92 statement:

1> select top 10 p.p_key, e.e_uid, e.field1 from table_p p left join table_e e on p.p_key=e.p_key and e.field1='V1'
2> go
 p_key       e_uid            field1
 ----------- ---------------- ----------------
           2 2005092612595815 V1
           2 2005030715593204 V1
           2 2005092614251692 V1
           4 NULL             NULL
           8 NULL             NULL
           9 NULL             NULL
          10 NULL             NULL
          11 NULL             NULL
          14 NULL             NULL
          15 NULL             NULL

(10 rows affected)

Note that the second criterion is not in the where clause but in the on part.

So the old left outer join syntax is compacter but it is ambiguous as it doesn’t properly separate the join criteria and the where criteria. In case of a left outer join it makes a huge difference since the join criteria do not filter the returned rows but the where criteria do.

In most cases, the results you were after are the ones returned by the first and last queries above. But you should avoid the old left outer join syntax and try to use the SQL-92 syntax everywhere. It makes it clearer what you mean with the statement and can save some time searching why you did not get the output you were expecting. But also with the SQL-92 syntax you should carefully think whether you want to add a criterion to the join criteria or to the where clause (and as stated above in most cases when using a left outer join, the criteria on the joined tables should probably go in the join criteria).

Leave a Reply

Your email address will not be published.