I’m asked once a while what is an inner join and outer join?
While this has become something I do in my sleep the best way to explain this is by example so here we go:
Assuming you’re joining on columns with no duplicates, which is by far the most common case:
Let’s create two small test tables a and b as follow:
Small Test table a fred=> timing on Timing is on. fred=> create table smtesta (a int, description character(11)); CREATE TABLE Time: 63.253 ms fred=> Small Test table b fred=> create table smtestb (b int, description character(11)); CREATE TABLE Time: 42.475 ms fred=>
Let’s quickly give table b to Fred as well
fred=# SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- postgres | postgres (1 row) fred=# alter table smtestb owner to fred; ALTER TABLE fred=#
Now let’s insert some values on these.
For smtesta:
fred=> insert into smtesta (a, description)
select x.a, 'Insert # ' || x.a
from generate_series (1,5) as x(a);
INSERT 0 5
Time: 40.997 ms
fred=>
:Skip few inserts
fred=> insert into smtesta (a, description)
select x.a, 'Insert # ' || x.a
from generate_series (11,25) as x(a);
INSERT 0 15
Time: 48.092 ms
For smtestb:
fred=> insert into smtestb (b, description)
select x.b, 'Insert # ' || x.b
from generate_series (1,9) as x(b);
INSERT 0 9
Time: 44.455 ms
fred=>
Skip few inserts:
fred=> insert into smtestb (b, description)
select x.b, 'Insert # ' || x.b
from generate_series (21,34) as x(b);
INSERT 0 14
Time: 40.779 ms
fred=>
Now lets list smtesta and smtestb:
fred=> select * from smtesta; a | description ----+--------------------------- 1 | Insert # 1 2 | Insert # 2 3 | Insert # 3 4 | Insert # 4 5 | Insert # 5 .... 20 | Insert # 20 21 | Insert # 21 22 | Insert # 22 23 | Insert # 23 24 | Insert # 24 25 | Insert # 25 (20 rows) Time: 4.249 ms fred=> select * from smtestb; b | description ----+--------------------------- 1 | Insert # 1 2 | Insert # 2 3 | Insert # 3 4 | Insert # 4 5 | Insert # 5 .... 31 | Insert # 31 32 | Insert # 32 33 | Insert # 33 34 | Insert # 34 (23 rows) Time: 4.464 ms fred=>
Now we’re ready to test our inner join First:
Note the unique and common values in both smtesta and smtestb tables
Inner join
An inner join using either the “where” equivalent queries would return set with the intersection(common values) of the two tables.
Time: 4.464 ms fred=> select * from smtesta sa INNER JOIN smtestb sb on sa.a = sb.b; a | description | b | description ----+---------------------------+----+--------------------------- 1 | Insert # 1 | 1 | Insert # 1 2 | Insert # 2 | 2 | Insert # 2 3 | Insert # 3 | 3 | Insert # 3 4 | Insert # 4 | 4 | Insert # 4 5 | Insert # 5 | 5 | Insert # 5 21 | Insert # 21 | 21 | Insert # 21 22 | Insert # 22 | 22 | Insert # 22 23 | Insert # 23 | 23 | Insert # 23 24 | Insert # 24 | 24 | Insert # 24 25 | Insert # 25 | 25 | Insert # 25 (10 rows) Time: 11.734 ms Equivalent using "where" instead fred=> select sa.*, sb.* fred-> from smtesta sa, smtestb sb fred-> where sa.a = sb.b; a | description | b | description ----+---------------------------+----+--------------------------- 1 | Insert # 1 | 1 | Insert # 1 2 | Insert # 2 | 2 | Insert # 2 3 | Insert # 3 | 3 | Insert # 3 4 | Insert # 4 | 4 | Insert # 4 5 | Insert # 5 | 5 | Insert # 5 21 | Insert # 21 | 21 | Insert # 21 22 | Insert # 22 | 22 | Insert # 22 23 | Insert # 23 | 23 | Insert # 23 24 | Insert # 24 | 24 | Insert # 24 25 | Insert # 25 | 25 | Insert # 25 (10 rows) Time: 2.784 ms fred=>
Left outer join
A left outer join will return all rows in smtesta, plus any common rows in smtestb.
fred=> select * from smtesta sa LEFT OUTER JOIN smtestb sb on sa.a = sb.b; a | description | b | description ----+---------------------------+----+--------------------------- 1 | Insert # 1 | 1 | Insert # 1 2 | Insert # 2 | 2 | Insert # 2 3 | Insert # 3 | 3 | Insert # 3 4 | Insert # 4 | 4 | Insert # 4 5 | Insert # 5 | 5 | Insert # 5 11 | Insert # 11 | | 12 | Insert # 12 | | 13 | Insert # 13 | | 14 | Insert # 14 | | 15 | Insert # 15 | | 16 | Insert # 16 | | 17 | Insert # 17 | | 18 | Insert # 18 | | 19 | Insert # 19 | | 20 | Insert # 20 | | 21 | Insert # 21 | 21 | Insert # 21 22 | Insert # 22 | 22 | Insert # 22 23 | Insert # 23 | 23 | Insert # 23 24 | Insert # 24 | 24 | Insert # 24 25 | Insert # 25 | 25 | Insert # 25 (20 rows) Time: 4.438 ms fred=>
Right outer join:
A right outer join will return all rows in smtestb, plus any common rows in smtesta.
fred=> select * from smtesta sa RIGHT OUTER JOIN smtestb sb on sa.a = sb.b;
a | description | b | description
----+---------------------------+----+---------------------------
1 | Insert # 1 | 1 | Insert # 1
2 | Insert # 2 | 2 | Insert # 2
3 | Insert # 3 | 3 | Insert # 3
4 | Insert # 4 | 4 | Insert # 4
5 | Insert # 5 | 5 | Insert # 5
| | 6 | Insert # 6
| | 7 | Insert # 7
| | 8 | Insert # 8
| | 9 | Insert # 9
21 | Insert # 21 | 21 | Insert # 21
22 | Insert # 22 | 22 | Insert # 22
23 | Insert # 23 | 23 | Insert # 23
24 | Insert # 24 | 24 | Insert # 24
25 | Insert # 25 | 25 | Insert # 25
| | 26 | Insert # 26
| | 27 | Insert # 27
| | 28 | Insert # 28
| | 29 | Insert # 29
| | 30 | Insert # 30
| | 31 | Insert # 31
| | 32 | Insert # 32
| | 33 | Insert # 33
| | 34 | Insert # 34
(23 rows)
Time: 2.258 ms
fred=>
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 value in B, then the B portion is null, and vice verse.
fred=> select * from smtesta sa FULL OUTER JOIN smtestb sb on sa.a = sb.b;
a | description | b | description
----+---------------------------+----+---------------------------
1 | Insert # 1 | 1 | Insert # 1
2 | Insert # 2 | 2 | Insert # 2
3 | Insert # 3 | 3 | Insert # 3
4 | Insert # 4 | 4 | Insert # 4
5 | Insert # 5 | 5 | Insert # 5
11 | Insert # 11 | |
12 | Insert # 12 | |
13 | Insert # 13 | |
14 | Insert # 14 | |
15 | Insert # 15 | |
16 | Insert # 16 | |
17 | Insert # 17 | |
18 | Insert # 18 | |
19 | Insert # 19 | |
20 | Insert # 20 | |
21 | Insert # 21 | 21 | Insert # 21
22 | Insert # 22 | 22 | Insert # 22
23 | Insert # 23 | 23 | Insert # 23
24 | Insert # 24 | 24 | Insert # 24
25 | Insert # 25 | 25 | Insert # 25
| | 26 | Insert # 26
| | 27 | Insert # 27
| | 33 | Insert # 33
| | 31 | Insert # 31
| | 34 | Insert # 34
| | 32 | Insert # 32
| | 8 | Insert # 8
| | 28 | Insert # 28
| | 30 | Insert # 30
| | 6 | Insert # 6
| | 29 | Insert # 29
| | 9 | Insert # 9
| | 7 | Insert # 7
(33 rows)
Time: 2.322 ms
fred=>
Ok, mates that’s it for now until next PostgreSQL or Oracle or so tutorial. Or you could check other cool PostgreSQL Tutorials: Sharing Knowledge Tutorials
Cheers
Alf