Set Operators (UNION, INTERSECT and EXCEPT)

In addition to the operators described earlier in the chapter, three set operators are supported in the Transact-SQL language:

• UNION
• INTERSECT
• EXCEPT

UNION Set Operator

The result of the union of two sets is the set of all elements appearing in either or both of the sets. Accordingly, the union of two tables is a new table consisting of all rows appearing in either or both of the tables.

The general form of the UNION operator is

select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}...

select_1, select_2,… are SELECT statements that build the union. If the ALL option is used, all resulting rows, including duplicates, are displayed. The ALL option has the same meaning with the UNION operator as it has in the SELECT list, with one difference: the ALL option is the default in the SELECT list, but it must be specified with the UNION operator to display all resulting rows, including duplicates.

The sample database in its original form is not suitable for a demonstration of the UNION operator. For this reason, this section introduces a new table, employee_enh, which is identical to the existing employee table, up to the additional domicile column. The domicile column contains the place of residence of every employee.

The new employee_enh table has the following form:

Creation of the employee_enh table provides an opportunity to show the use of the INTO clause of the SELECT statement. SELECT INTO has two different parts. First, it creates the new table with the columns corresponding to the columns listed in the SELECT list. Second, it inserts the existing rows of the original table into the new table. (The name of the new table appears with the INTO clause, and the name of the source table appears in the FROM clause of the SELECT statement.)

Example 6.44 shows the creation of the employee_enh table.

USE sample;
SELECT emp_no, emp_fname, emp_lname, dept_no
	INTO employee_enh
	FROM employee;
ALTER TABLE employee_enh
		ADD domicile CHAR(25) NULL;Code language: PHP (php)

In Example 6.44, SELECT INTO generates the employee_enh table and inserts all rows from the initial table (employee) into the new one. Finally, the ALTER TABLE statement appends the domicile column to the employee_enh table.

After the execution of Example 6.44, the domicile column contains no values. The values can be added using SQL Server Management Studio or the following UPDATE statements:

USE sample;
UPDATE employee_enh SET domicile = 'San Antonio'
	WHERE emp_no = 25348;
UPDATE employee_enh SET domicile = 'Houston'
	WHERE emp_no = 10102;
UPDATE employee_enh SET domicile = 'San Antonio'
	WHERE emp_no = 18316;
UPDATE employee_enh SET domicile = 'Seattle'
	WHERE emp_no = 29346;
UPDATE employee_enh SET domicile = 'Portland'
	WHERE emp_no = 9031;
UPDATE employee_enh SET domicile = 'Tacoma'
	WHERE emp_no = 2581;
UPDATE employee_enh SET domicile = 'Houston'
	WHERE emp_no = 28559;Code language: PHP (php)

Example 6.45 shows the union of the tables employee_enh and department.

USE sample;
SELECT domicile
	FROM employee_enh
UNION
SELECT location
	FROM department;Code language: PHP (php)

The result is

Two tables can be connected with the UNION operator if they are compatible with each other. This means that both the SELECT lists must have the same number of columns, and the corresponding columns must have compatible data types. (For example, INT and SMALLINT are compatible data types.)

The ordering of the result of the union can be done only if the ORDER BY clause is used with the last SELECT statement, as shown in Example 6.46. The GROUP BY and HAVING clauses can be used with the particular SELECT statements, but not with the union itself.

Get the employee number for employees who either belong to department d1 or entered their project before 1/1/2007, in ascending order of employee number:

USE sample;
SELECT emp_no
	FROM employee
	WHERE dept_no = 'd1'
UNION
SELECT emp_no
	FROM works_on
	WHERE enter_date < '01.01.2007'
ORDER BY 1;Code language: PHP (php)

The result is

The OR operator can be used instead of the UNION operator if all SELECT statements connected by one or more UNION operators reference the same table. In this case, the set of the SELECT statements is replaced through one SELECT statement with the set of OR operators.

INTERSECT and EXCEPT Set Operators

The two other set operators are INTERSECT, which specifies the intersection, and EXCEPT, which defines the difference operator. The intersection of two tables is the set of rows belonging to both tables. The difference of two tables is the set of all rows, where the resulting rows belong to the first table but not to the second one. Example 6.47 shows the use of the INTERSECT operator.

USE sample;
SELECT emp_no
	FROM employee
	WHERE dept_no = 'd1'
INTERSECT
SELECT emp_no
	FROM works_on
	WHERE enter_date < '01.01.2008';Code language: PHP (php)

The result is

Example 6.48 shows the use of the EXCEPT set operator.

USE sample;
SELECT emp_no
	FROM employee
	WHERE dept_no = 'd3'
EXCEPT
SELECT emp_no
	FROM works_on
	WHERE enter_date > '01.01.2008';Code language: PHP (php)

The result is