JOIN SQL

INNER JOIN

For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1.

LEFT OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1.

RIGHT OUTER JOIN

First, an inner join is performed. Then, for each row in T2 that does not satisfy the join condition with any row in T1, a joined row is added with null values in columns of T1. This is the converse of a left join: the result table will unconditionally have a row for each row in T2.

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

Joins of all types can be chained together or nested: either or both of T1 and T2 might be joined tables. Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.

To put this together, assume we have tables t1:

 num | name
-----+------
  1 | a
  2 | b
  3 | c

and t2:

 num | value
-----+-------
  1 | xxx
  3 | yyy
  5 | zzz

then we get the following results for the various joins:

=> SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value
-----+------+-----+-------
  1 | a    |   1 | xxx
  1 | a    |   3 | yyy
  1 | a    |   5 | zzz
  2 | b    |   1 | xxx
  2 | b    |   3 | yyy
  2 | b    |   5 | zzz
  3 | c    |   1 | xxx
  3 | c    |   3 | yyy
  3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
  1 | a    |   1 | xxx
  3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value
-----+------+-------
  1 | a    | xxx
  3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
num | name | value
-----+------+-------
  1 | a    | xxx
  3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
  1 | a    |   1 | xxx
  2 | b    |     |
  3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | value
-----+------+-------
  1 | a    | xxx
  2 | b    |
  3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
  1 | a    |   1 | xxx
  3 | c    |   3 | yyy
    |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
  1 | a    |   1 | xxx
  2 | b    |     |
  3 | c    |   3 | yyy
    |      |   5 | zzz