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.