Introduction
JOIN clause is a basic construct in SQL used to combine rows from two or more tables. They are commonly used, but every time before writing a statement with join, many people start wondering what the result will be. The best way of understanding joins is to visualize them by using Venn diagrams.
Many beginners wonder why right joins are introduced when left exist. So, let's take a closer look at two types of joins: right and left, which seem to be the most interesting.
It's better to work on real data, so let's present two tables and fill them.
LEFT OUTER JOIN
LEFT OUTER JOIN retrieves rows from TableA with matching records from TableB. If for a certain record from TableA (left), there are no matching records from TableB (right), the corresponding (right) columns contain nulls.
Select *
FROM TableA
LEFT OUTER JOIN TableB
on tableA.name = tableB.name;
RIGHT OUTER JOIN
RIGHT OUTER JOIN retrieves rows from TableB with matching records from TableA. This situation is the opposite of the previous one. Here, when for a certain record from TableB (right), there are no matching records from TableA (left), the corresponding (left) columns contain nulls.
Select *
FROM tableA
RIGHT OUTER JOIN tableB
On tableA.name = tableB.name
Of course, right outer join can be achieved by doing a left outer join with swapped tables. The question occurs: Why does right outer join exist when there is left outer join?
In SQLite database, there is no such thing as right and full outer join. They both can be emulated by left outer join.
The example of full outer join in sqlite:
select *
from TableA left join TableB
on TableA.name = TableB.name
union
select *
from TableB left join TableA
on TableB.name = TableA.name
And one last note. LEFT OUTER JOIN = LEFT JOIN and RIGHT OUTER JOIN = RIGHT JOIN. You can find a full review of SQL joins here.
