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 stan@bos.edu Stern, Stan 99,000
0101 ali@bos.edu 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 stan@bos.edu 99,000 NULL Staff
F0101 Aloof, Alison ali@bos.edu 30,000 NULL Staff
S1007 Perfect, Peter 1007@bos.edu NULL 590 Student
S1008 Dunce, Donald 1008@bos.edu 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;


SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net