Understanding and fixing joins

Joins are an important thing; 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 into 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 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...