[thelist] SQL .. is there a better way ?
rudy
r937 at interlog.com
Sun May 26 00:34:00 CDT 2002
> 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
More information about the thelist
mailing list