CPSC340A - Day 6 Notes

Union

Joins merge two tables by adding the columns from two different tables into a new table
Unions merge two tables by adding the rows of two different tables
```
SELECT value1 FROM Table1 UNION SELECT value2 FROM Table2;
```

The number of columns must be same

SELECT value1, value2 FROM Table1 UNION SELECT value3 FROM Table2; -- Error

The type of columns should be same
If they are not the same, mySQL will automatically convert but it may not do what you want
```
SELECT text FROM Table1 UNION SELECT number FROM Table2;
```
The rows will be unique

If you want duplicate rows, add the ALL keyword

SELECT value1 FROM Table1 UNION ALL SELECT value2 FROM Table2;

Note that, ALL is faster, so if you know there are no duplicates, add it

The retrieved column name is the name of the select on the left

SELECT value1 AS noonen FROM Table1 UNION SELECT value2 FROM Table2;

Unions can be chained

SELECT value1  FROM Table1 UNION SELECT value2 FROM Table2 UNION SELECT value3 FROM Table3;

Select queries being unioned can be complex

SELECT value1 FROM Table1 INNER JOIN Table2 USING (id) UNION SELECT value2 FROM Table2 UNION SELECT value3 FROM Table3;

The order can be changed using ORDER BY

SELECT value1 FROM Table1 UNION SELECT value2 FROM Table2 ORDER BY value1;

Note that this orders the union and not the select on the left

Also note that the name used in the order by is the name of the column in the retrieved table

SELECT value1 FROM Table1 UNION SELECT value2 FROM Table2 ORDER BY Table1.value1; -- Error

SELECT value1 FROM Table1 UNION SELECT value2 FROM Table2 ORDER BY value2; -- Error

It is possible to order the sub query in the select, but this not usual

SELECT value1  FROM Table1 UNION (SELECT value2 FROM Table2 ORDER BY number);

To filter, can’t just add WHERE to the end

SELECT value1  FROM Table1 UNION SELECT value2 FROM Table2 WHERE value2 < 7; -- Only filters right select

SELECT value1  FROM Table1 UNION SELECT value2 FROM Table2 WHERE value1 < 7; -- Error, value1 isn't in right select

(SELECT value1  FROM Table1 UNION SELECT value2 FROM Table2) WHERE value1 < 7; -- Error

Need to filter each where

(SELECT value1  FROM Table1 WHERE value1 < 7) UNION (SELECT value2 FROM Table2 WHERE value1 < 7);

Or use a subquery, which is slower

SELECT * FROM (SELECT value1 FROM Table1 UNION SELECT value2 FROM Table2) WHERE value1 < 7;

Union can be used for symmetric difference, aka FULL OUTER JOIN

    SELECT value1 FROM Table1 LEFT OUTER JOIN Table2 USING (id) WHERE
    UNION ALL
    SELECT value1 FROM Table1 RIGHT OUTER JOIN Table2 USING (id) WHERE value1 IS NULL;