You can use this operator to combine the results of two or more SELECT statements.
About the UNION SELECT command
This optional operator combines the results of two SELECT commands or clauses, displaying the results of the second SELECT command below those of the first. By default, UNION deletes duplicate rows. Include the optional keyword ALL to include duplicate rows in the final result. You cannot combine sub-SELECT commands using UNION.
The UNION operator requires the following three conditions:
•The SELECT statements must specify an equal number of columns.
•Columns that are being combined must have the same data type.
•Only the last SELECT statement can contain an ORDER BY clause.
To use a UNION SELECT to combine two text columns of different lengths, put parentheses around the column names to have R:BASE interpret the values an expressions. The same technique can be used to combine NOTE and TEXT data types values.
Examples
Example 01:
The following example lists all employees and the sales transactions for each, including those employees who have not yet completed a sale.
SELECT employee.empid, transid +
FROM employee, transmaster +
WHERE employee.empid = transmaster.empid +
UNION SELECT empid, 0 +
FROM employee +
WHERE employee.empid NOT IN +
(SELECT empid FROM transmaster)
The first SELECT displays the empid column from the employee table and transid from the transmaster table, linking the tables by the common column, empid. In short, the first SELECT displays all employees who have made a sale.
The second SELECT command selects the empid column from employee, including rows only for those employees who are not listed in the transmaster table. Because the results of the second SELECT are appended to those of the first (by the UNION operator), those employees who have not yet made a sale are shown at the bottom of the results with a zero in the transid column. The final results look like this:
empid transid
------ --------
102 4795
102 4975
102 4980
102 5000
102 5045
102 5060
129 4790
129 4865
129 5050
129 5070
129 5075
131 4970
131 5010
131 5015
131 5085
133 4760
133 5048
133 5080
160 4780
160 4800
160 5065
165 5046
165 5049
166 0
Example 02:
The following example combines two tables where the 'X' in the first part of the SELECT is treated as an expression, which means that it is created as a NOTE data type, because that is the data type which supports variable length TEXT values as a result of an expression. The DYCD_AcctNum column is an actual column of type TEXT and length 1. As columns that are being combined must have the same data type, putting parentheses around the DYCD_AcctNum column makes it an expression as well.
SELECT Major,GLDesc=50,'X' AS Status +
FROM GLAcctNum +
UNION SELECT Major,GLDesc=50,(DYCD_AcctNum) AS Status +
FROM HHSAcctNum
Major GLDesc Status
---------- -------------------------------------------------- ---------------
4 PAYROLL CASH ACCOUNT X
5 CLAIMS CASH ACCOUNT X
42 PETTY CASH FUND ACCOUNT X
53 ACCOUNT RECEIVABLE X
53 OTHER RECEIVABLE X
53 TAX REFUND DUE X
100 TOTAL ASSETS X
200 TAX PAYABLE PASS THROUGH X
201 FEDERAL TAX PAYABLE X
206 LOAN - MISC. PAYROLL DEDUCTIONS X
206 MEDICAL - MISC. PAYROLL DEDUCTIONS X
206 OTHER -MISC. PAYROLL DEDUCTIONS X
235 LOANS PAYABLE X
1100 GROSS SALARIES AND WAGES X
2410 SAFETY AND HEALTH X
2420 STAFF TRAINING X
2430 ACCOUNTING COSTS X
2440 LEGAL COSTS X
3200 EQUIPMENT PURCHASE X
3710 OTHER COSTS X
3739 OTHER OPERATING COSTS X
