Mastering PostgreSQL 9.6

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 should ring alarm bells to 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 condition.

The next example might come as a surprise to many people:

test=# ...