What kinds of join are there

There are:

• Inner join
• Outer left
• Outer right
• Union (all)

Note that other set operations like minus and intersect are also supported, which can sometimes help a lot when a query is difficult to frame as a join but you know how to get the data sets you’re looking for differences in.

Union has by definition unique rows and checking for and ensuring uniqueness can take a lot of time on large data sets. Specifying union all removes this constraint.