Example
SQL has various join types to specify whether (non-)matching rows are included in the result: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN (the INNER and OUTER keywords are optional). The figure below underlines the differences between these types of joins: the blue area represents the results returned by the join, and the white area represents the results that the join will not return.
Cross Join SQL Pictorial Presentation (reference) :
Below are examples from this answer.
For instance there are two tables as below :
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner Join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common:
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B:
select * from a LEFT OUTER JOIN b on a.a = b.b;
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Right outer join
Similarly, a right outer join will give all rows in B, plus any common rows in A:
select * from a RIGHT OUTER JOIN b on a.a = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
Full outer join
A full outer join will give you the union of A and B, i.e., all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
