SQL Anywhere database - join criteria question

Author

join criteria question

Bob Shirley

2005-05-25, 11:23 am

I've probably missed something in browsing the archives, but I'm having a
problem with a simple left outer join in ASA 9.0.1 that I can't explain.

When I run this query:

SELECT class_packprod.directclassid, class_packprod.id,
class_packprod.classsystemid
FROM class_packprod
LEFT OUTER JOIN packprod
ON (class_packprod.id = packprod.id)
AND (class_packprod.classsystemid = 2)

I don't expect to see results where classsystemid has values other than 2,
but I do. If I simply change the way the classsystemid criteria is
expressed:

SELECT class_packprod.directclassid, class_packprod.id,
class_packprod.classsystemid
FROM class_packprod
LEFT OUTER JOIN packprod
ON (class_packprod.id = packprod.id)
WHERE (class_packprod.classsystemid = 2)

Then I get the results I expect... ?

Can anyone please explain this to me and/or point me to the documentation
that explains why the first syntax (which I use all the time in other DBs)
isn't acting as I expect ?

Thanks,
Bob S.

Glenn Paulley

2005-05-25, 11:23 am

The ASA server is giving the correct answer for both queries.

In an outer join, the join inputs are either "preserved" or "null-
supplying":

LEFT OUTER JOIN: "left" input is preserved, "right" is null-supplied
RIGHT OUTER JOIN: "right" input is preserved, "left" is null-supplied
FULL OUTER JOIN: both inputs are *both* preserved and null-supplied.

When an input is preserved, *every* row of that input is guaranteed to
appear in the result. In a null-supplied input, NULL values may be
substituted for a row when there is no combination of a preserved row and
*any* of the null-supplied rows that satisfy the ON condition (make the
condition evaluate to TRUE).

Now, as to your specific example:

> FROM class_packprod
> LEFT OUTER JOIN packprod
> ON (class_packprod.id = packprod.id)
> AND (class_packprod.classsystemid = 2)

the class_packprod table is preserved, hence every row of the table is
guaranteed to appear in the result, even if classsystemid <> 2. However,
any specific row of class_packprod where classsystemid <> 2 will fail to
join with any row from packprod because it will be impossible for the ON
condition to evaluate to TRUE - hence that row from class_packprod will
appear in the result with NULLs for all the columns of packprod.

Hope this helps.

Glenn

"Bob Shirley" < bob_shirley@firstdat
abank.nospam.com> wrote in
news:4294a278$1@foru
ms-2-dub:

> I've probably missed something in browsing the archives, but I'm
> having a problem with a simple left outer join in ASA 9.0.1 that I
> can't explain.
>
> When I run this query:
>
> SELECT class_packprod.directclassid, class_packprod.id,
> class_packprod.classsystemid
> FROM class_packprod
> LEFT OUTER JOIN packprod
> ON (class_packprod.id = packprod.id)
> AND (class_packprod.classsystemid = 2)
>
> I don't expect to see results where classsystemid has values other
> than 2, but I do. If I simply change the way the classsystemid
> criteria is expressed:
>
> SELECT class_packprod.directclassid, class_packprod.id,
> class_packprod.classsystemid
> FROM class_packprod
> LEFT OUTER JOIN packprod
> ON (class_packprod.id = packprod.id)
> WHERE (class_packprod.classsystemid = 2)
>
> Then I get the results I expect... ?
>
> Can anyone please explain this to me and/or point me to the
> documentation that explains why the first syntax (which I use all the
> time in other DBs) isn't acting as I expect ?
>
> Thanks,
> Bob S.
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Sponsored Links