joins

Once and for all, joins explained :

Here is the data from two tables :

A    B
-    -
1    3
2    4
3    5
4    6

Now (1,2) only exist in A, (3,4) in both, and (5,6) only in B.

Basic is that LEFT means the left table A as show, and RIGHT means table B. An INNER join gives the common values from both tables, so there is no difference between left or right (or full). An OUTER join can be left, right or full.

The inner join of these would be retrieved with :

select * from A inner join B on A.col=B.col;

And the result would be :

<pre>
 col | col 
-----+-----
   3 |   3
   4 |   4
</pre>

So it's the intersection between those, donkey bridge ? IN-tersection ? In fact the result is the same as :

select * from A, B on A.col=B.col;

Left outer join will always represent all rows from the left table, and the matching rows (or NULL) from the right table.

select * from A left outer join B on A.col=B.col;

<pre>
     col | col 
    -----+-----
       1 |    
       2 |    
       3 |   3
       4 |   4
</pre>

So RIGHT outer join will do the same but with reversed tables :

select * from A right outer join B on A.col=B.col;

<pre>
  col | col 
 -----+-----
    3 |   3
    4 |   4
      |   5
      |   6
</pre>

Finally FULL OUTER join will guarantee all rows from both tables, with the match from the other :

select * from A full outer join B on A.col=B.col;

<pre>
  col | col 
 -----+-----
    1 |    
    2 |    
    3 |   3
    4 |   4
      |   5
      |   6
</pre>

So the join will likely contain :

• INNER : less rows than in either table
• LEFT : exactly the number of columns as the left table
• RIGHT : exactly the number of columns as the right table
• FULL : more rows than in either table