7.4. Handling UNION and UNION ALL Situations with an Unequal Number of Columns
As has been mentioned earlier, in order to successfully UNION or UNION ALL result sets, the result sets being unioned have to have the same number of columns. That is, all queries in a UNION or UNION ALL operation must return the same number of columns. But what if all the queries being used in the UNION or UNION ALL do not return the same number of columns?
If we want to union two result sets that do not have the same number of columns, we have to use NULL (or other) values in the column-places as place holders. For example, from our Student_course database, if we want to union the Course table and the Prereq table with all the columns, under normal circumstances, this would not be possible, because the Course table has four columns and the Prereq table has only two. Therefore, to perform a UNION ALL operation, we would have to place NULL values or some other values in the columns that will be empty, as follows (this example uses NULL as a place holder):
SELECT c.*, NULL
FROM Course c
WHERE c.credit_hours = 4
UNION ALL
SELECT NULL, p.course_number, NULL, NULL, p.prereq
FROM Prereq p
This query produces the following 18 rows of output:
COURSE_NAME COURSE_NUMBER CREDIT_HOURS OFFERING_DEPT
-------------------- ------------- ------------ ------------- --------
INTRO TO COMPUTER SC COSC1310 4 COSC NULL
DATA STRUCTURES COSC3320 4 COSC NULL
ADA - INTRODUCTION COSC5234 4 COSC NULL
CALCULUS 1 MATH1501 4 MATH NULL
SOCIALISM AND COMMUN POLY4103 4 POLY NULL
POLITICS OF CUBA POLY5501 4 POLY NULL
NULL ACCT3333 NULL NULL ACCT2220
NULL CHEM3001 NULL NULL CHEM2001
NULL COSC3320 NULL NULL COSC1310
NULL COSC3380 NULL NULL COSC3320
NULL COSC3380 NULL NULL MATH2410
NULL COSC5234 NULL NULL COSC3320
NULL ENGL1011 NULL NULL ENGL1010
NULL ENGL3401 NULL NULL ENGL1011
NULL ENGL3520 NULL NULL ENGL1011
NULL MATH5501 NULL NULL MATH2333
NULL POLY2103 NULL NULL POLY1201
NULL POLY5501 NULL NULL POLY4103
(18 row(s) affected)
We can also use other values (other than NULL) as placeholders, as shown here:
SELECT c.*, COU_NUM = 'XXXXXXXXXXXX'
FROM Course c
WHERE c.credit_hours = 4
UNION ALL
SELECT 'XXXXXXXXXXXXX', p.course_number, 00000000000, 'XXXXXXXXXXXXX', p.prereq
FROM Prereq p
This query gives the same output as the previous query, but this time we have used a series of Xs and 0s as placeholders instead of NULL (we have 18 rows of output):
COURSE_NAME COURSE_NUMBER CREDIT_HOURS OFFERING_DEPT COU_NUM
-------------------- ------------- ------------ ------------- ------------
INTRO TO COMPUTER SC COSC1310 4 COSC XXXXXXXXXXXX
DATA STRUCTURES COSC3320 4 COSC XXXXXXXXXXXX
ADA - INTRODUCTION COSC5234 4 COSC XXXXXXXXXXXX
CALCULUS 1 MATH1501 4 MATH XXXXXXXXXXXX
SOCIALISM AND COMMUN POLY4103 4 POLY XXXXXXXXXXXX
POLITICS OF CUBA POLY5501 4 POLY XXXXXXXXXXXX
XXXXXXXXXXXXX ACCT3333 0 XXXXXXXXXXXXX ACCT2220
XXXXXXXXXXXXX CHEM3001 0 XXXXXXXXXXXXX CHEM2001
XXXXXXXXXXXXX COSC3320 0 XXXXXXXXXXXXX COSC1310
XXXXXXXXXXXXX COSC3380 0 XXXXXXXXXXXXX COSC3320
XXXXXXXXXXXXX COSC3380 0 XXXXXXXXXXXXX MATH2410
XXXXXXXXXXXXX COSC5234 0 XXXXXXXXXXXXX COSC3320
XXXXXXXXXXXXX ENGL1011 0 XXXXXXXXXXXXX ENGL1010
XXXXXXXXXXXXX ENGL3401 0 XXXXXXXXXXXXX ENGL1011
XXXXXXXXXXXXX ENGL3520 0 XXXXXXXXXXXXX ENGL1011
XXXXXXXXXXXXX MATH5501 0 XXXXXXXXXXXXX MATH2333
XXXXXXXXXXXXX POLY2103 0 XXXXXXXXXXXXX POLY1201
XXXXXXXXXXXXX POLY5501 0 XXXXXXXXXXXXX POLY4103
(18 row(s) affected)
NULL does not have a data type, so it can be used as a placeholder for both numeric and character columns. But when using other values as placeholders, the data types have to match. Hence we used 'XX...' (in the query with the single quotes) for the character columns, and 000s (in the query without quotes) for the numeric columns.
 | 
