-- Set operations and joins.
-- -------------------------

-- We'll start by creating two simple tables, each with a single
-- integer column and three rows.  These match the "a" and "b" sets
-- illustrated on the slide.

CREATE TABLE a (col1 int);

INSERT INTO a (col1) VALUES (1);
INSERT INTO a (col1) VALUES (2);
INSERT INTO a (col1) VALUES (3);

SELECT * FROM a;

CREATE TABLE b (col2 int);

INSERT INTO b (col2) VALUES (3);
INSERT INTO b (col2) VALUES (4);
INSERT INTO b (col2) VALUES (5);

SELECT * FROM b;

-- Cartesian product
-- Every row in table a is combined with every row in table b.
-- Both queries are equivalent.

SELECT a.col1,b.col2 FROM a,b;
SELECT a.col1,b.col2 FROM a CROSS JOIN b;

-- Set operations
-- --------------

-- Intersection
-- The intersection selects the values common to both sets

SELECT a.col1 AS "col" FROM a INTERSECT (SELECT b.col2 as "col" FROM b) ORDER BY col;

-- This combines both columns into a single column (set), but only
-- includes values found in both.

-- Difference
-- Subtract one set from the other.  The result is values which are
-- not found in the other set.  As before, both columns are combined
-- into a single column, "col".

SELECT a.col1 AS "col" FROM a EXCEPT (SELECT b.col2 as "col" FROM b) ORDER BY col;
SELECT b.col2 AS "col" FROM b EXCEPT (SELECT a.col1 as "col" FROM a) ORDER BY col;

-- Union
-- Combine both sets into a single set.

SELECT a.col1 AS "col" FROM a UNION (SELECT b.col2 as "col" FROM b) ORDER BY col;

-- Joins
-- -----

-- Joining is combining two tables together to create a combined table
-- (the cartesian product).  Normally we join tables together where
-- there is a common column between the two (for example, a primary
-- key and foreign key reference).  We select the rows from the
-- cartesian product where the two match.

SELECT a.col1,b.col2 FROM a CROSS JOIN b WHERE (a.col1 = b.col2);
SELECT a.col1,b.col2 FROM a INNER JOIN b ON (a.col1 = b.col2);

-- Sometimes, there is a NULL value in the forign key column (if, for
-- example, it is optional), and a CROSS JOIN or INNER JOIN will
-- automatically skip those columns because the WHERE or ON condition
-- will never return TRUE for a comparison with a NULL value.  If we
-- still want to see those rows, we use an OUTER JOIN.

-- There are three types of outer join, depending on which table(s)
-- contain the NULL values.

SELECT a.col1,b.col2 FROM a LEFT OUTER JOIN b ON (a.col1 = b.col2);
SELECT a.col1,b.col2 FROM a RIGHT OUTER JOIN b ON (a.col1 = b.col2);

SELECT a.col1,b.col2 FROM a LEFT OUTER JOIN b ON (a.col1 = b.col2) WHERE (b.col2 IS NULL);
SELECT a.col1,b.col2 FROM a RIGHT OUTER JOIN b ON (a.col1 = b.col2) WHERE (a.col1 IS NULL);

-- Notice how the IS NULL variants behave similarly to the set
-- difference operation, but by default the common elements are
-- included.  This means that these sorts of join are not true set
-- operations (there are two columns, rather than one), so though they
-- are similar they are not identical and so should not be treated as
-- such.  If you want true set operations, use the INTERSECT, UNION
-- and DIFFERENCE keywords, but you will find that you will use joins
-- far more frequently.

SELECT a.col1,b.col2 FROM a FULL OUTER JOIN b ON (a.col1 = b.col2);
SELECT a.col1,b.col2 FROM a FULL OUTER JOIN b ON (a.col1 = b.col2) WHERE (a.col1 IS NULL OR b.col2 IS NULL);