Re: Outer join with constants

In article <84555688.0409190216.874be90_at_posting.google.com>,  laojiqi_at_hotmail.com (LJQ) wrote:

> Thanks.
>
> I as using Oracle9i Enterprise Edition Release 9.2.0.1.0 for HPUX and
> Windows.
>
> Outer join supposes to handly the relation with 2 tables, how does
> ANSI (SQL99) define this (join two contants with no relation on the
> target tables)? The standards only say that the JOIN ON clause using
> the same syntax with where clause but it does not give the expected
> result.
>
> I have checked the FIXED BUG list for 9.2.0.5.0 as well, it does not
> mention this one.

Conceptually it's supposed to work like this: - disassemble the FROM clause into pairwise joins of tables or other joins. (A subquery generates a table for the purposes of this discussion.) A table listed in a comma-list is equivalent to <table> JOIN <everything else> ON 1=1.
- Starting at the bottom of your join tree and working up, perform each join thus: form the cartesian product of the two join sources. (The cart prod == all possible combinations of all rows.) - Filter the cart-prod through the ON clause, keeping rows that evaluate to TRUE and tossing the ones that evaluate to FALSE. - If the join is an OUTER join, return to the outer source. Determine which rows did not appear in some output combination. Output those rows, with nulls for the inner-side columns. (For a left join, the outer table is on the left, and similarly for a right join. For a full join, do this for both left then right join sources.) - After you've performed all the FROM clause joining, filter the result through the WHERE clause, and then on to the rest of the select query.

So the ON clause simply determines which rows join. It's just a TRUE/FALSE test and you can put anything you like in there. (Well, anything consistent with the join-sources available at that point in the query.) Non-joining rows may be eliminated completely (inner join), or output with null inner columns (outer join).

Please note again that the above is a purely conceptual formulation, one of several possible ways of saying it. One would hope that no DBMS would be insane enough to actually evaluate all those cart-prods unless there were no alternative. Additionally, I have no clue how any of this relates to any particular version of Oracle. This conceptual formulation is how it's SUPPOSED to work, and to repeat, any other answer is a bug.

Karl Received on Mon Sep 20 2004 - 10:03:13 CDT