Bryce never fully grokked relational joins:

I always thot that inner/outer was *orthogonal* to left/right. Outer joins fill in non-matches with nulls IIRC while inner skips non-matches.

Yup. And, what does it *do* with those rows from one table that it "makes up" nulls for? Why, it "matches" them to those rows from the other table that there is no real match for. But which is which??? Which table does it seek (and "fake") matches _for_, and which one does it present its fakes as "coming" _from_?

That's what you tell it with "LEFT" or "RIGHT" (or "FULL"). You *can't* specify outer/inner without telling it that, too -- ex- or implicitly -- so they are *not* orthogonal (=independent).

The left/right clause tells it which direction to join at. The default is "inner" if you don't specify inner/outer and the default is "left" if you don't specify left/right.

Don't think of it as a two-dimensional "grid", but a one-dimensional "strip": Draw two overlapping circles (or better yet, make them elongated, ellipses) to represent your tables. Orient them horizontally, mark them A and B if you want, and/or fill them in with different hash patterns or colours.

Now, an "INNER" join will be the cross-hatched (and/or colour-blended) area in the middle (the inner-most one!), while the "LEFT" and "RIGHT OUTER" ones are that inner area plus the rest of one circle (can't remember which direction means which circle, though), i.e, one whole circle and stuff to match *some* of it from the other one; and the "FULL OUTER" join will be the whole shebang, both circles including their common area.

If you drew them elongatedly enough (=stretched out in one dimension), you'll see that it's just one continuum of data you get to select from, and the inner/outer specification *together with* the left/right/full specification decides what you select, "from where to where" along your strip of paper.

Or, to put it another way: If "LEFT" / "RIGHT" were orthogonal from "OUTER" / "INNER", you ought to be able to combine, say, a "LEFT INNER" query. But your only legal alternatives are "LEFT OUTER", "RIGHT OUTER", "FULL OUTER", and "INNER" -- so they aren't independent.

HTH!

IIRC :-)

Well, you might be *remembering* it correctly... But if so, then *what* you're remembering is -- how you got it wrong in the first place.

Sorry, but that's just the way it is.