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:
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.
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;
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;