8.9 Sophisticated Joins

Joins are the heart of every complex query. Whenever more than just one table is involved in a query, joins must be performed. So far you have dealt with normal joins, but in this section you will take a closer look at some more helpful and useful features.

8.9.1 Left, Right, and Co.

In general performing simple joins is easy in some cases, however, it is necessary to perform some more complex operations. In this section you will start to deal with a set of different join operations such as left and right joins.

Let's start with an example. Two tables must be created:

 phpbook=# CREATE TABLE event (id int4, name text); CREATE phpbook=# CREATE TABLE comment (event_id int4, comment text); CREATE 

The first table stores the id and the name of an event. The second table contains the id of the event stored in the first table and a comment. No integrity constraints have been defined.

In the next step three records can be inserted into event:

 phpbook=# INSERT INTO event VALUES (1, 'concert of Manifold'); INSERT 26009 1 phpbook=# INSERT INTO event VALUES (2, 'concert of Scentless Work'); INSERT 26010 1 phpbook=# INSERT INTO event VALUES (3, 'concert of Le Craval'); INSERT 26011 1 

Then some records can be added to the second table:

 phpbook=# INSERT INTO comment VALUES (1, 'great concert on La Palma (Spain)'); INSERT 26012 phpbook=# INSERT INTO comment VALUES (1, 'I liked the Rolling Stones songs most'); INSERT 26013 1 phpbook=# INSERT INTO comment VALUES (2, 'they played Falco''s "Rock me Amadeus"'); INSERT 26014 1 phpbook=# INSERT INTO comment VALUES (4, 'nice workshop and nice food'); INSERT 26015 1 

As you can see, the id 3 can only be found in the first table and the id 4 can only be found in the second table. The target of the first example is to perform a simple join used to retrieve all records that have an id in common:

 SELECT event.name, comment.comment         FROM event, comment         WHERE event.id=comment.event_id; 

This join is nothing new for you, so the result is not surprising:

            name            |                comment ---------------------------+---------------------------------------  concert of Manifold       | great concert on La Palma (Spain)  concert of Manifold       | I liked the Rolling Stones songs most  concert of Scentless Work | they played Falco's "Rock me Amadeus" (3 rows) 

You have displayed the name of the event as well as the comment on the event. The same result can be achieved by using an INNER JOIN:

 SELECT event.name, comment.comment         FROM event INNER JOIN comment                 ON event.id=comment.event_id; 

In the previous result you have seen that every record having a counterpart in comment has been listed. The target of the next example is to display all events even if there is no comment:

 SELECT event.name, comment.comment         FROM event LEFT JOIN comment ON                 event.id=comment.event_id; 

The result contains one additional record:

            name            |                comment ---------------------------+---------------------------------------  concert of Manifold       | great concert on La Palma (Spain)  concert of Manifold       | I liked the Rolling Stones songs most  concert of Scentless Work | they played Falco's "Rock me Amadeus"  concert of Le Craval      | (4 rows) 

No comment has been added for the concert of the band called Le Craval, but you have performed a left join. If you want to have all records that have a comment but no event, you can use a right join:

 SELECT event.name, comment.comment         FROM event RIGHT JOIN comment                 ON event.id=comment.event_id; 

Again, four records are displayed, but this time the column containing the name is displayed completely:

            name            |                comment ---------------------------+---------------------------------------  concert of Manifold       | great concert on La Palma (Spain)  concert of Manifold       | I liked the Rolling Stones songs most  concert of Scentless Work | they played Falco's "Rock me Amadeus"                            | nice workshop and nice food (4 rows) 

Let's see what happens if the list of tables in the FROM clause is changed:

 SELECT event.name, comment.comment         FROM comment RIGHT JOIN event                 ON event.id=comment.event_id; 

The result of the query is equal to the left join you have seen before. As you can see, there is more than one way to solve the problem:

            name            |                comment ---------------------------+---------------------------------------  concert of Manifold       | great concert on La Palma (Spain)  concert of Manifold       | I liked the Rolling Stones songs most  concert of Scentless Work | they played Falco's "Rock me Amadeus"  concert of Le Craval      | (4 rows) 

Left and right joins are a powerful feature of SQL and especially of PostgreSQL. With the help of these joins, it is possible to compute complex results with little effort and in an efficient way.