Mastering PostgreSQL 12 - Third Edition

Getting joins right

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

Here is an example of a simple table structure to demonstrate how joins work:

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

The following example shows a simple outer join:

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

As you can see, PostgreSQL will take all the rows from the left-hand side and only list the ones that fit the join.

The following example ...