-- http://en.wikipedia.org/wiki/Join_(sql)

SET client_encoding TO 'latin-1';

-- Inner join, with the old SQL syntax
SELECT name, title FROM Books, Authors WHERE Books.author = Authors.id;
-- Does not display books without an author
-- This inner join is also an equi-join (equality sign in WHERE)

-- Inner join, new syntax. Apart from the syntax, same as above
SELECT name, title FROM Books INNER JOIN Authors ON Books.author = Authors.id;
-- If the fields had the same name, we could have used USING

-- Cross join. Uninteresting but funny.
-- SELECT * FROM Books CROSS JOIN Authors;

-- Left outer join. There is no "old syntax" here.
SELECT name, title FROM Books LEFT OUTER JOIN Authors ON Books.author = Authors.id;
-- LEFT means we retain tuples from the left table (Books) even when they do 
-- not match (books without an author)

-- If we don't like the empty name column:
SELECT CASE WHEN name IS NULL THEN 'Unknown author' ELSE name END, 
       title FROM Books LEFT OUTER JOIN Authors ON Books.author = Authors.id;

-- Right outer join
SELECT name, title FROM Books RIGHT OUTER JOIN Authors ON Books.author = Authors.id;
-- RIGHT means we retain tuples from the right table (Authors) even when they do 
-- not match (people who did not write a book)

-- A right outer join can always be replaced by a left one by swapping
-- the tables. This join is the same as the previous one.
SELECT name, title FROM Authors LEFT OUTER JOIN Books ON Books.author = Authors.id;

-- Full outer join, retain non-matching tuples from both sides
SELECT name, title FROM Books FULL OUTER JOIN Authors ON Books.author = Authors.id;

-- TODO: use subqueries instead of outer joins, play with self-joins, etc