10.6 Set Operators

Mathematicians use the term set theory to describe mathematical operations on sets, where a set is defined as a group of distinct items. A relational database table meets the definition of a set.

  • A set is represented by a labeled circle.
  • A subset is a portion of a set that is contained entirely within the set.

  • The union of two sets represents a set that contains all values in both sets. This is equivalent to an OR logical operation (A OR B).

  • The intersection of two sets represents the area common to both sets. This is equivalent to an AND logical operation (A AND B).

  • The complement of set B in set A represents everything in set A that is not in set B. This is equivalent to a logical operation using NOT (A NOT B).

10.6.1 SQL Set Keywords

  • UNION: all the rows in one or both tables

  • INTERSECT: all the rows common to both tables

  • EXCEPT: all the rows in the first table but not the second

10.6.2 UNION

Query : What products were available for sale (by either catalog or Web site) in 2014 and 2015?

select  sku, sku_description, department from catalog_sku_2014
UNION
select  sku, sku_description, department from catalog_sku_2015
TABLE10.9: Displaying records 1 - 10
sku sku_description department
100100 Std. Scuba Tank, Yellow Water Sports
100300 Std. Scuba Tank, Light Blue Water Sports
100400 Std. Scuba Tank, Dark Blue Water Sports
101100 Dive Mask, Small Clear Water Sports
101200 Dive Mask, Med Clear Water Sports
201000 Half-dome Tent Camping
202000 Half-dome Tent Vestibule Camping
301000 Light Fly Climbing Harness Climbing
302000 Locking Carabiner, Oval Climbing
100200 Std. Scuba Tank, Magenta Water Sports
select  sku, sku_description, department from catalog_sku_2014
UNION ALL
select  sku, sku_description, department from catalog_sku_2015
TABLE10.10: Displaying records 1 - 10
sku sku_description department
100100 Std. Scuba Tank, Yellow Water Sports
100300 Std. Scuba Tank, Light Blue Water Sports
100400 Std. Scuba Tank, Dark Blue Water Sports
101100 Dive Mask, Small Clear Water Sports
101200 Dive Mask, Med Clear Water Sports
201000 Half-dome Tent Camping
202000 Half-dome Tent Vestibule Camping
301000 Light Fly Climbing Harness Climbing
302000 Locking Carabiner, Oval Climbing
100100 Std. Scuba Tank, Yellow Water Sports