Amazon Redshift - What is the default join?

Question

What is the default "join" in Amazon Redshift? When I do not specify any direction, does Redshift use Inner or Left or Right or Outer join?

Answer 1

Amazon Redshift is based on PostgreSQL which defaults on INNER JOIN same as many other query languages.

You can also find it out by creating two simple tables as following and executing a simple SELECT by JOIN query

A    B
-    -
1    3
2    4
3    5
4    6

select * from a JOIN b on a.a = b.b;

Here 1,2 are unique to table A and 5,6 are unique to table B while 3,4 being common the query above returns only the values 3,4 so it's inner join.

Answer 2

Redshift, like pretty much all SQL databases, follow the SQL Standard.

It specifies that when join type is absent, it is equivalent to INNER JOIN

From the SQL Specification:

If a < qualified join > or < natural join > is specified and a < join type > is not specified, then INNER is implicit.