Using UNION

You can use UNION to append the rows returned by one set of selection criteria to the rows returned by another set.

Appending is different from joining. To join tables, you merge selected columns of one table with selected columns of another table. To append tables, you combine selected rows of one table to selected rows of another table.

How It's Done

To combine the CONSULTANT and EMPLOYEE tables to get a complete list of all people on the payroll, enter:

select con_id, con_lname, con_fname
       from consultant
       union
       select emp_id, emp_lname, emp_fname
              from employee;

This statement adds rows from the EMPLOYEE table to the rows in the CONSULTANT table.

The result looks like this:

EMP_ID EMP_LNAME EMP_FNAME ------ --------- --------- . . . 4321 Bradley George 4358 Robinson Judith 4456 Thompson Thomas 4660 MacGregor Bruce 4703 Halloran Martin 4773 Dexter Janice 4962 White Peter 5008 Fordman Timothy 5090 Wills Stephen 5103 Ferguson Adele 9000 Legato James 9388 Candido Linda 9439 Miller Charles 9443 Jones Diane 59 rows processed

Things to Remember about UNION

UNION combines rows from separate SELECT statements and removes duplicate rows
The columns you pair must have compatible data types.
You must name the same number of columns in each of the SELECT statements
Column names do not have to match
If you want to sort the result table, use a single ORDER BY clause after the last SELECT statement
The column names from the second table are used as column headings
You can specify column headings using AS on the second table

Adding Rows Selectively

By adding WHERE clauses, you can use UNION to selectively add rows from one or more tables to another table.

How It's Done

To see information on both employees and consultants working in a particular department, enter:

select con_id, con_lname, con_fname
       from consultant
       where dept_id = 5200
       union
       select emp_id, emp_lname, emp_fname
              from employee
              where dept_id = 5200;

This statement adds selected rows from the EMPLOYEE table to selected rows in the CONSULTANT table.

The result looks like this:

EMP_ID EMP_LNAME EMP_FNAME ------ --------- --------- 2145 Catlin Martin 2781 Thurston Joseph 2894 Griffin William 3411 Williams Catherine 4358 Robinson Judith 9388 Candido Linda 9443 Jones Diane 7 rows processed