At 10:53 AM 20-05-02 +0000, you wrote:

>It understood the inner join to mean:
>
>return those rows from the left hand table (b_matdet)that match the where
>criteria, regardless of the existance of a matching row in the
>righthand one (b_order). If a matching b_order row does exist, do the
>inner join to the b_order_ops table.

The above is a bit scrambled but it seems to describe (sort of) the effect
of the outer join; and your two original examples illustrate the
difference between the outer and the inner join. Breaking down the above:

First sentence:

return those rows from the left hand table (b_matdet)that match the where
criteria, regardless of the existance of a matching row in the
righthand one (b_order).

--- this is your left outer join example. You get one row returned because
your left outer join *required* the query to return all where-eligible
lefthand table rows, regardless of what was in the righthand table.

Second sentence:

If a matching b_order row does exist, do the inner join to the b_order_ops
table.

It seems as if your first proposition was correct - you misunderstand the
terminology. Both queries are performing a join on the same join
criteria. The *join* itself is just a join. But by qualifying the join as
"left outer", you are saying "I want all the lefthand table's
where-eligible rows to return their values, even if the join returns nulls
on the right hand side of the join". This is what you would see if you had
specified any right-hand table columns in the output set (which you didn't...)

If in fact there had been a single matching row in the righthand table,
both queries would have returned exactly the same one-row result. If there
had been two matching rows in the right-hand table, both queries would have
returned two rows - identical, as it happens, because no right-hand table
columns were specified in the output.

> How can I get this if not using the query below?

You can probably give yourself a more meaningful demo by designing your
pair of queries to return one or two columns from the right-hand table...

Helen

> >
> > >I expect the same number of rows from both of the queries below
> > >when there are no rows on the righthand side of the left outer join
> > >(b_matdet.b_md_worder_ref = b_order.b_o_ref fails)
> > >
> > >but the first one returns 1 row - fine and the second returns none.
> > >
> > >select * from b_matdet left outer join b_order on
> >b_matdet.b_md_worder_ref =
> > >b_order.b_o_ref
> > >where b_matdet.b_md_ref = 7023
> > >
> > >select * from b_matdet left outer join b_order on
> >b_matdet.b_md_worder_ref =
> > >b_order.b_o_ref
> > >inner join b_order_ops on b_order.b_o_ref = b_order_ops.b_o_ref
> > >where b_matdet.b_md_ref = 7023

Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________