Switch to: V11V10V9V8V7V6V5

UNION, INTERSECT and EXCEPT

UNION, INTERSECT and EXCEPT instructions are three basic operations for creating target table using two source tables.

All these operations demand that source tables be compatible - have the same number of fields, and corresponding fields have the same data-type

Show the list of goods for which there are amount of orders more than $3000, and also the goods which are stored in warehouse on sum more than $30000.

	( SELECT mfr, product
		FROM orders
	WHERE amount > 3000.00 )
	UNION
	( SELECT mfr_id, product_id
		FROM products
	WHERE ( price * qty_on_hand ) > 30000)

Show the list of goods for which there are orders on sum more than $3000, and also Such goods must be stored in warehouse on sum more than $30000.

	( SELECT mfr, product
		FROM orders
	WHERE amount > 3000.00 )
	INTERSECT
	( SELECT mfr_id, product_id
		FROM products
	WHERE ( price * qty_on_hand ) > 30000)

Show the list of goods for which there are orders on sum more than $30000, excepting those which cost less than $1000.

	( SELECT mfr, product
		FROM orders
	WHERE amount > 3000.00 )
	EXCEPT
	( SELECT mfr_id, product_id
		FROM products
	WHERE price < 1000)


By default these operations suppress duplicated records in resulting table. Technically we are sorting the result for excluding such not-unique rows. So you should not expect rows appearance in order of 'union parts' mentioning.

Example:

	( SELECT 'z')
	UNION
	( SELECT 'a')
	UNION
	( SELECT 'z')
=>
a
z

In order to leave all records you should use special forms of three operations:

  • UNION ALL
  • INTERSECT ALL
  • EXCEPT ALL.

These forms don't take care of duplicates - so there is a 'natural' order in the result.

Example:

	( SELECT 'z')
	UNION ALL
	( SELECT 'a')
	UNION ALL
	( SELECT 'z')
=>
z
a
z

If table fields are same named you may mention such fields once in CORRESPONDING clause, which you may use in all these statements: UNION, INTERSECT and EXCEPT.

Show the list of goods for which there are orders on sum more than $30000, and also the goods which are stored in warehouse on sum more than $30000.

( SELECT *
		FROM orders
	WHERE amount > 3000.00 )
	UNION CORRESPONDING BY ( mfr, product )
	( SELECT *
		FROM products
	WHERE ( price * qty_on_hand ) > 30000)

If all fields of two tables take part in the operation you may not indicate these fields’ names at all.

	( SELECT *
		FROM orders
	WHERE amount > 3000.00 )
	UNION CORRESPONDING
	( SELECT *
		FROM products
	WHERE ( price * qty_on_hand ) > 30000)

Also in any of the query specifications whose results are joined with UNION operation, you can give columns’ aliases.

CORRESPONDING statement makes query more clear if subquery has calculated fields or GROUP BY statements.