The other JOINs

The LEFT OUTER JOIN and the INNER JOIN are the stars among the JOINs. They are most often used and almost everyone knows them. But there are a few other variations of the JOIN that can be useful from time to time.

RIGHT OUTER JOIN

Symbol for RIGHT OUTER JOIN

The RIGHT OUTER JOIN is exactly like the LEFT OUTER JOIN, only mirror-inverted. So if you flip the table order, it’s identical. In practice, however, the LEFT OUTER JOIN is almost always used and its right twin almost never.

FULL OUTER JOIN

Symbol for FULL OUTER JOIN

With the FULL OUTER JOIN , data records are never removed. If a record, whether in the left or the right table, does not find a partner on the other side, then NULL values are simply written there.

CROSS JOIN

The CROSS JOIN forms the cross product between two tables. Thus, each row of the left table is combined with each row of the right table. This is rarely a technical requirement. Mostly the CROSS JOIN is used for technical applications.

Short forms

The JOIN type does not have to be written out. It can also be abbreviated. But these short forms make it difficult to read. Especially the comma for a CROSS JOIN is often overlooked.

JOIN typeShort form (please avoid)
INNER JOINJOIN
LEFT OUTER JOINLEFT JOIN
RIGHT OUTER JOINRIGHT JOIN
CROSS JOIN,