Book Image

Troubleshooting PostgreSQL

Book Image

Troubleshooting PostgreSQL

Overview of this book

Table of Contents (17 chapters)
Troubleshooting PostgreSQL
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

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 about...