Understanding and fixing joins

Joins are important; everybody needs them on a regular basis. Consequently, joins are also relevant to maintaining or achieving good performance. To ensure that you can write good joins, I have decided to include a section about joining in this book.

Getting joins right

Before we dive into optimizing joins, it is important to take a look at some of the most common problems arising with joins and which of them should ring alarm bells for you.

Here is an example:

test=# CREATE TABLE a (aid int);
CREATE TABLE
test=# CREATE TABLE b (bid int);
CREATE TABLE
test=# INSERT INTO a VALUES (1), (2), (3);
INSERT 0 3
test=# INSERT INTO b VALUES (2), (3), (4);
INSERT 0 3

In the next example, you will see a simple outer join:

test=# SELECT * FROM a LEFT JOIN b ON (aid = bid);
 aid | bid
-----+-----
   1 |
   2 |    2
   3 |    3 
(3 rows)

You can see that PostgreSQL will take all rows from the left-hand side and only list the ones fitting the join.

The next example might come as...