[thelist] SQL .. is there a better way ?

[rudy]

> btw, I'm having a *really* difficult time trying to figure out
> when to use what kind of join.  I must be missing the basic
> logic for it, as I always seem to struggle trying to figure it out ..
> inner join, left join, right join, outer join.

an inner join returns only rows where the keys match

an inner join is what you get when you use the "table list" syntax

    from TableA, TableB
    where ...

an outer join returns all the rows of one table, plus columns from the
other table where the keys match, with nulls in those fields where the keys
don't match

a left join and a right join are just outer joins -- if you don't say
which, it's usually left by default


TableA
2 foo
4 bar
6 qux

TableB
2 john
2 bill
6 mary
8 fred

TableA inner join TableB
2 foo john
2 foo bill
6 qux mary

TableA left outer join TableB
2 foo john
2 foo bill
4 bar --
6 qux mary

TableA right outer join TableB
2 foo john
2 foo bill
6 qux mary
8 -- fred

a right join is actually the same as a left join with the tables mentioned
in the other order, so you hardly ever see right joins in the wild

there is also a "full outer join" but not all databases implement it, and
it isn't often needed --

TableA full outer join TableB
2 foo john
2 foo bill
4 bar --
6 qux mary
8 -- fred

the key to knowing whether you want an inner or an outer join is whether
you need all the rows of one of the tables, even if there are no matching
row from the other table on some of the keys


helps?


rudy