Using Unions

Howard,

I need to access data in three files: WORKPRC, MATL1 and MATL2. When a field in WORKPRC has a value of ‘A’ I want to read a field from the MATL1 file. When the value of the field is ‘B’ I want to read a field from file MATL2. Is this possible in SQL? -- Matt

Dear Matt,

What you want is a UNION. UNION allows you to put the results of two SQL queries together. Lets build some sample tables and make some unions to illustrate their use.

CREATE TABLE SQLBOOK.U1
	(ID1 INTEGER NOT NULL,
	 JT CHAR(1) NOT NULL);

INSERT INTO SQLBOOK.U1 VALUES (1,’A’);
INSERT INTO SQLBOOK.U1 VALUES (2,’A’);
INSERT INTO SQLBOOK.U1 VALUES (3,’A’);
INSERT INTO SQLBOOK.U1 VALUES (4,’B’);
INSERT INTO SQLBOOK.U1 VALUES (5,’B’);
INSERT INTO SQLBOOK.U1 VALUES (6,’B’);

CREATE TABLE SQLBOOK.U2
	(ID2 INTEGER NOT NULL,
	 CA1 DECIMAL(8,2) NOT NULL);

INSERT INTO SQLBOOK.U2 VALUES (1,11.11);
INSERT INTO SQLBOOK.U2 VALUES (1,11.11);
INSERT INTO SQLBOOK.U2 VALUES (2,22.22);
INSERT INTO SQLBOOK.U2 VALUES (2,22.22);
INSERT INTO SQLBOOK.U2 VALUES (3,33.33);
INSERT INTO SQLBOOK.U2 VALUES (3,33.33);

CREATE TABLE SQLBOOK.U3
	(ID2 INTEGER NOT NULL,
	 CA2 DECIMAL(8,2) NOT NULL);

INSERT INTO SQLBOOK.U3 VALUES (4,44.44);
INSERT INTO SQLBOOK.U3 VALUES (5,55.55);
INSERT INTO SQLBOOK.U3 VALUES (6,66.66);
INSERT INTO SQLBOOK.U3 VALUES (4,44.44);
INSERT INTO SQLBOOK.U3 VALUES (5,55.55);
INSERT INTO SQLBOOK.U3 VALUES (6,66.66);

Now, consider the following SQL statement:

SELECT CA1 
   FROM SQLBOOK.U1 INNER JOIN
	SQLBOOK.U2 ON (ID1=ID2)
   WHERE JT='A';

It returns the results

So, pretend that statement gets the data you want when the first condition is true. Now, lets say this statement defines the second condition:

SELECT CA2 
   FROM SQLBOOK.U1 INNER JOIN 
             SQLBOOK.U2 ON (ID1=ID2)
   WHERE JT='B’;

Now, if you UNION the results of the statements together, you get the following results:

SELECT CA1
    FROM SQLBOOK.U1 INNER JOIN
             SQLBOOK.U2 ON (ID1=ID2)
    WHERE JT='A'
UNION
SELECT CA2
    FROM SQLBOOK.U1 INNER JOIN
              SQLBOOK.U3 ON (ID1=ID2)
    WHERE JT='B';

Hey, but that’s wrong because SQL left out some of the records! Actually, it is not wrong as the definition of UNION is to return a set of records with no duplicates. (This is the same as using SELECT DISTINCT on an individual query). If you want to get all of the records from both sets, you need to use UNION ALL like this:

SELECT CA1
     FROM SQLBOOK.U1 INNER JOIN
               SQLBOOK.U2 ON (ID1=ID2)
     WHERE JT='A'
UNION ALL
SELECT CA2
     FROM SQLBOOK.U1 INNER JOIN
               SQLBOOK.U3 ON (ID1=ID2)
     WHERE JT='B';

Note that we now have the set of ALL records from the first query unioned with the set of ALL records from the second query. To use a union, you must ensure that all queries being unioned have the same number of columns and each column has a compatible data type when matched with the corresponding column in the union query. For example, lets say you have file T1 which has the fields ID INTEGER and MAKEMEE CHAR(12) and then you have file T2 which has fields ID INTEGER and COST DECIMAL(12,2). The following statement would not work:

SELECT ID, MAKEMEE FROM T1
UNION
SELECT ID, COST FROM T2

This is because MAKEMEE is type CHAR and COST is type DECIMAL and those types are not compatible. However, if you do the following statement the union will work:

SELECT ID, MAKEMEE FROM T1
UNION
SELECT ID, VARCHAR(COST) FROM T2

I used the VARCHAR function to cast the COST field to a VARCHAR data type, which is compatible with the CHAR data type of the second column in the first select statement. I could also redo the query this way:

SELECT ID, CAST(NULL AS DECIMAL(12,2), MAKEMEE
     FROM T1
UNION
SELECT ID, COST, CAST(NULL AS CHAR(12))
     FROM T2

The above statement would work because the second column in the first select is a null cast as a DECIMAL(12,2) preserving the space for the value in the second select statement. Note that in the second select statement we need to case the NULL value to be a CHAR(12) in order to make it compatible with the data type of the third column in the first select statement.

There is more information about unions in my book, iSeries and AS/400 SQL at Work.