The different combinations of join are confusing, can someone boil it down a little?

Question

Looking at JOIN's this weekend.

I was reading up on Join and seeing a ton of combinations of JOIN, LEFT, RIGHT, OUTER, FULL, INNER. I checked the MSDN docs and it looks like the only allowed combinations are in the form:

< join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
    [ < join_hint > ]
    JOIN

so from that, I am figuring you can only get:

JOIN / INNER JOIN
LEFT JOIN / LEFT OUTER JOIN
RIGHT JOIN / RIGHT OUTER JOIN
FULL JOIN / FULL OUTER JOIN

Also, OUTER appears to be an optional keyword, so LEFT JOIN and LEFT OUTER JOIN are really the same. Is this correct? What's the difference between JOIN and FULL OUTER JOIN? I have seen a JOIN alone in a script, but haven't been able to break it down, because the query was a bit complicated.

Answer 1

http://www.codinghorror.com/blog/archives/000976.html

Has one of the best explanations I've seen.

Answer 2

The Wikipedia article on SQL join is quite clear and has lots of examples.

Answer 3

This might help answer some of your questions:

A Visual Explanation of SQL Joins

Answer 4

These are all valid syntax that can go between table sources:

JOIN
INNER JOIN
LEFT JOIN
LEFT OUTER JOIN
RIGHT JOIN
RIGHT OUTER JOIN
FULL JOIN
FULL OUTER JOIN

Each requires a following ON clause.

You are correct that OUTER is optional, so RIGHT JOIN is the same as RIGHT OUTER JOIN, and likewise for LEFT and FULL. Also, INNER is optional if there is no , so JOIN is the same as INNER JOIN.

There is also CROSS JOIN, which is the same as a comma and has no ON clause. In other words,

FROM A CROSS JOIN B

and

FROM A,B

mean exactly the same thing.

The table operators JOIN and FULL OUTER JOIN are not at all the same. The table source

FROM A JOIN B ON <condition>

contains only rows for which is true. On the other hand, the table source

FROM A FULL OUTER JOIN B ON <condition>

contains rows for which is true along with all the additional rows that you'd get in either a LEFT OUTER JOIN or a RIGHT OUTER JOIN with the same ON clause.

Answer 5

You appear to have more combinations than are supported:

<join_type> ::=
    [ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ]
    [ <join_hint> ]
    JOIN

This says you can have:

       JOIN                    INNER JOIN
 LEFT  JOIN              LEFT  OUTER JOIN
 RIGHT JOIN              RIGHT OUTER JOIN
 FULL  JOIN              FULL  OUTER JOIN

The keyword OUTER is optional, in other words. In the full SQL standard, you can also have a NATURAL JOIN or a CROSS JOIN. The keyword INNER is optional too; a plain 'JOIN' is an INNER JOIN.

Actually, the formatting of your answer led me into thinking you'd not understood properly; it ran INNER JOIN with LEFT JOIN (I'll fix that shortly if no-one else has).

A FULL JOIN is radically different from a plain (INNER) JOIN. The INNER JOIN is simply based on the specified criterion (specified by an ON clause or a USING clause). The LEFT OUTER JOIN consists of the rows of an INNER JOIN plus those rows from the left-hand table that are not joined with the right-hand table extended with NULL for each column in the right-hand table. This is sometimes called the 'left outer increment'. The RIGHT OUTER JOIN reverses the sense of the outer join (so there is a 'right outer increment'). The FULL JOIN consists of the union of the INNER JOIN and the left outer increment and the right outer increment. The result of a FULL JOIN does not have a primary key because there are, in general, nulls in every column.

Answer 6

There are only three kinds of join: CROSS, INNER, and OUTER.

• Cross joins return every possible combination of rows from both tables, and aren't often used.
• Inner joins only return rows where both tables match the join condition.
• Outer joins pick one table as the "origin" table, and will always return all rows from that table. Any time there's no match in the other table, NULL values are supplied.

Within outer joins (and only outer joins) there are also three sub types: LEFT, RIGHT and FULL.

• For Left joins, the first table is the origin table.
• For Right joins the 2nd table is the origin table.
• For Full joins, both tables are origin tables.

Because LEFT, RIGHT, and FULL only apply to OUTER joins, sometimes the OUTER keyword is omitted in favor of simply specifying which kind of outer join (ie, just "LEFT JOIN").