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).

Table 7-3. The staff table

staffId	email	name	salary
0173	[e-mail]	Stern, Stan	99,000
0101	[e-mail]	Aloof, Alison	30,000

Table 7-4. The student table

id	fName	lName	gpa
1007	Peter	Perfect	590
1008	Donald	Dunce	220

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

Table 7-5. The combined staffStudent table

id	Name	email	salary	gpa	species
F0073	Stern, Stan	[e-mail]	99,000	NULL	Staff
F0101	Aloof, Alison	[e-mail]	30,000	NULL	Staff
S1007	Perfect, Peter	[e-mail]	NULL	590	Student
S1008	Dunce, Donald	[e-mail]	NULL	220	Student

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;