Hack 50. Combine Tables Containing Different Data

You can use UNION when you have two tables with different data that you want to combine into one. This means that you are bringing together dissimilar table structures, and you have to make them agree before you can do the UNION.

Complex UNIONs may involve some wide tables, and in such cases it is easy to mix up the columns. The following practices can help:

• You should alias every column, which makes it easier to fix column mismatches. You don't have to do this for each SELECT, because the aliases of the first SELECT will name the columns.
• Include a sensible constant value or NULL where a column does not apply. Consider carefully whether to use NULL, and remember that a NULL will not contribute to a COUNT or an AVERAGE.
• Add a new column to the UNION to identify the source of each row. This will make it possible to pick the UNION apart again when required.

Let's look at an example. Suppose you need to combine a staff table (Table 7-3) with a student table (Table 7-4).

You want the combined table to look like Table 7-5.

Notice:

• The email address for a student is based on the student's ID, and needs to be calculated.
• The student name format has been converted to the staff name format.
• Salary and GPA have the same type but they are not comparable; it would never be sensible to add or compare them, so they belong in different columns even though they are mutually exclusive.
• The id columns have been prefixed to guarantee uniqueness.

To make this UNION work you need to concatenate strings. The SQL standard says that you should use || to do this. Unfortunately, the || operator is commonly used to mean "logical or" in other contexts and neither MySQL nor SQL Server supports this part of the SQL standard. Here's how to do it in different database systems.

7.2.1. Oracle and PostgreSQL

Oracle and PostgreSQL follow the SQL standard and use || for concatenation:

SELECT 'F' || staffId id, name name, email email, salary salary, NULL gpa, 'Staff' species FROM staff UNION SELECT 'S' || id id, lName || ', ' || fName name, id || '@bos.edu' email, NULL salary, gpa gpa, 'Student' species FROM student;

7.2.2. MySQL

In MySQL, you should use the CONCAT function:

SELECT CONCAT('F',staffId) id, name name, email email, salary salary, NULL gpa, 'Staff' species FROM staff UNION SELECT CONCAT('S',id) id, CONCAT(lName,', ',fName) name, CONCAT(id,'@bos.edu') email, NULL salary, gpa gpa, 'Student' species FROM student;

7.2.3. SQL Server and Access

The + operator is overloaded to perform concatenation in SQL Server and Access:

SELECT 'F' + staffId id, name name, email email, salary salary, NULL gpa, 'Staff' species FROM staff UNION SELECT 'S' + id id, lName + ', ' + fName name, id + '@bos.edu' email, NULL salary, gpa gpa, 'Student' species FROM student;