Fixing disastrous joins

NULL is not the only thing crying out for disaster. Joins are also a good source of trouble if not used properly. If you have the feeling that you know everything about joins and that this section can be skipped, give it a chance. Things might not be as easy as expected.

Create demo data for joins

To show what can go wrong during a join, the best thing to do is to create a simplistic example. In this case, two tables are created:

test=# CREATE TABLE a (aid int);
CREATE TABLE
test=# CREATE TABLE b (bid int);
CREATE TABLE

Then some rows can be added:

test=# INSERT INTO a VALUES (1), (2), (3);
INSERT 0 3
test=# INSERT INTO b VALUES (2), (3), (4);
INSERT 0 3

Note that the tables are quite similar but not identical.

For the sake of completeness, here is how common values can be found:

test=# SELECT * FROM a, b WHERE aid = bid;
 aid | bid 
-----+-----
   2 |   2
   3 |   3
(2 rows)

Understanding outer joins

A simple inner join is not what we are most interested in when talking...