SQL Join Types

New enhanced 25 pages long article with 44 examples and visual pictures describing SQL join types can be found here.

I'm studying a bit SQL joins and trying to make clear at least for me what they are and what the relationships among them are. As I've always thought that picture is worth hundred words I've tried to create ER diagram describing metainfo about joins. So the question for everyone is - is this diagram OK? And aren't there obvious bugs? :)
Probably from the strict viewpoint of Set theory and relational algebra it is not OK, but I'm more concerned of practical SQL usage and understanding. In case you know similar diagramm or whatever else visual explanation of relationships among join types please leave comment with link.
So the diagram is as follows (please click on it to get bigger):



Now for those that aren’t familiar with all these join types and terms in diagram above I'll try to give SQL examples (without data and results) for each one of them. If you'd like to have also examples with data cehck one of the links at the very end of this post.
Assume we have 2 tables - TableA (id, title) and TableB (id, description).
What the result are for each one of them you can find in other places for example:

• Cross join:

SELECT * FROM TableA CROSS JOIN TableB

• Join with restriction:

It is either natural join or qualified join. Each join with restriction has one cross join which is the degraded case of it.

• Natural join:

Natural joins can be Inner or one of Outer joins, but they always are equi joins. Without explicitly specified inner or outer it is inner join. More about natural joins in my previous blog post.

SELECT * FROM TableA NATURAL JOIN TableB

• Qualified join:

Qualified joins can be written either specifying join columns (named columns join) or explicitly writing join condition.

• Join type:

Could not find anything better to somehow unite inner and various outer joins under one roof.

• Inner join:

SELECT * FROM TableA NATURAL INNER JOIN TableB
SELECT * FROM TableA INNER JOIN TableB USING (id)
SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id)

• Outer join:

Outer join is one of left, right or full outer join.

• Left outer join:

SELECT * FROM TableA NATURAL LEFT OUTER JOIN TableB
SELECT * FROM TableA LEFT OUTER JOIN TableB USING (id)
SELECT * FROM TableA LEFT OUTER JOIN TableB ON (tablea.id = tableb.id)

• Right outer join and Full outer join:

Take the same examples as from left outer join and just replace left with right or outer.

• Qualified join expression:

There are two ways how more explicitly write join condition either specifying join columns or explicitly writing join condition.

• Named columns join:

This join is written with "USING" clause. Named columns join always is equi join.

SELECT * FROM TableA INNER JOIN TableB USING (id)

• Conditional join:

This join is the real one that I'd like to suggest using. With explicitly specifying what the join condition is. This also is the only join where one can use not only equi join but also another predicate operator than "=".

SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id)

• Predicate operator type:

Based on predicate operator type joins can be classified as either equi or theta (nonequi) joins.

• Equi join:

Equi join is join where operator used in join condition is equivalence ("=").
Both natural joins and Named columns joins are always equi joins. Examples:

SELECT * FROM TableA NATURAL LEFT OUTER JOIN TableB
SELECT * FROM TableA FULL OUTER JOIN TableB USING (id)
SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id)

• Theta (Nonequi) join:

Equi join is join where operator used in join condition is something other than equivalence ("=").

SELECT * FROM TableA INNER JOIN TableB ON (tablea.id <>

I've not showed here such beasts like UNION JOINS, SEMI JOINS and ANTI JOINS because the first one (UNION join) is neither in SQL standard nor in any database I worked with.
SEMI JOINS and ANTI JOINS are somehow less interesting for me.

Further reading:

• SQL und relationale Algebra (in German with for everyone understandable examples)
• Relational algebra, joins and join like operation from wikipedia
• Why I think natural joins are most unnatural joins ever!