SQL Union — Collections of Collections

Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. Combining the result sets from one or more SQL Select statements is done with the SQL set operators: UNION, UNION ALL, INTERSECT, and EXCEPT. The basic syntax of these set operators is:

SELECT column(s) FROM table1
set_operation
SELECT column(s) FROM table2;

Each SELECT statement must have

• The same number of columns selected
• The same order of column specified
• The same data types between columns included (but not the same length)

Here we explore the meaning and use of the SQL set operators.

The SQL UNION Operation

The Venn diagram above a graphical representation of the overlap between two result sets “A”’ and “B”. The blue areas show the distinct portion of the sets; the white area shows the duplicates between them.

To illustrate the concept of a union between sets, consider tables of proper names and species, one of animals and a second of common housepets. (The complete SQL code for creating these tables appears at the bottom of this blog entry.)

The following SQL statement returns the  UNION of these two tables:

SELECT name FROM animals
UNION
SELECT name FROM pets ;

The SQL UNION ALL Operation

The SQL Union All set operator returns those results common to one or more SQL Select statements, including any duplicates.

SELECT name FROM animals
UNION ALL
SELECT name FROM pets ;

Note that “cat” and “dog” appear twice in the UNION ALL:

The SQL INTERSECT Operation

The SQL Intersect set operator returns those rows which appear in the results of both SQL Select statements.

The syntax of SQL Intersect

SELECT name FROM animals
INTERSECT
SELECT name FROM pets ;

MySQL doesn’t provide a SQL Intersect operation. Implement your own:

SELECT animals.name FROM animals
WHERE animals.name
 IN (
  SELECT pets.name FROM pets) ;

The SQL EXCEPT Operation

The SQL Except set operator returns rows from the first SELECT statement that are not included in the results of the second SELECT statement.

SELECT name
FROM animals
WHERE name NOT IN (SELECT name FROM pets);

Please note that in SQL Except the order of the arguments matter. Compare what follows with the above, where the animals and pets tables are reversed: 

SELECT name FROM pets
WHERE name NOT IN (SELECT name FROM animals);

Chaining SQL Set Operators

The above examples demonstrate the SQL set operators working on two SQL Select statements. This is the most common use case. Any number of SQL Select statements can be chained by extending the chain with the demonstrated syntax, like:

SELECT column(s) FROM table1
set_operation
SELECT column(s) FROM table2;
set_operation
SELECT column(s) FROM table3;
set_operation
SELECT column(s) FROM table4;

(Actually, the SQL Selects don’t actually need to be against separate tables; they could all be referencing the same table.)

Conclusion

We’ve covered the syntax and use cases surrounding the ways to combine the result sets from one or more SQL Select statements via the SQL set operators: UNION, UNION ALL, INTERSECT, and EXCEPT. Where a particular database doesn’t natively support a set operator some SQL equivalencies are shown.

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE animals (
name VARCHAR(99),
species VARCHAR(99)) ;
INSERT INTO
animals 
VALUES
( 'cat', 'Felis catus' ),
( 'dog', 'Canis lupus familiaris' ),
( 'African bush elephant', 'Loxodonta africana' ),
( 'panther', 'Panthera pardus' ),
( 'Egyptian vulture', 'Neophron percnopterus' ) ;
CREATE TABLE pets (
name VARCHAR(99),
species VARCHAR(99)) ;
INSERT INTO
pets 
VALUES
( 'cat', 'Felis catus' ),
( 'dog', 'Canis lupus familiaris' ),
( 'ferret', 'Mustela putorius furo' ),
( 'leaf-nosed moray eel', 'Rhinomuraena quaesita' ),
( 'lop-eared rabbit', 'Oryctolagus cuniculus' ) ;

This article was originally going to use two tables, one of the symbols and English names of the chemical elements and another of the U. S. postal service abbreviations of the states and their full names, but that turned out to be non-intuitive and unwieldy. I leave them here should they become helpful. 

CREATE TABLE elements (
symbol VARCHAR(2),
name VARCHAR(33)) ;
INSERT INTO
elements 
VALUES
( 'Ac', 'Actinium' ), ( 'Ag', 'Silver' ), ( 'Al', 'Aluminum' ), ( 'Am', 'Americium' ), ( 'Ar', 'Argon' ), ( 'As', 'Arsenic' ), ( 'At', 'Astatine' ), ( 'Au', 'Gold' ), ( 'B', 'Boron' ), ( 'Ba', 'Barium' ), ( 'Be', 'Beryllium' ), ( 'Bh', 'Bohrium' ), ( 'Bi', 'Bismuth' ), ( 'Bk', 'Berkelium' ), ( 'Br', 'Bromine' ), ( 'C', 'Carbon' ), ( 'Ca', 'Calcium' ), ( 'Cd', 'Cadmium' ), ( 'Ce', 'Cerium' ), ( 'Cf', 'Californium' ), ( 'Cl', 'Chlorine' ), ( 'Cm', 'Curium' ), ( 'Cn', 'Copernicium' ), ( 'Co', 'Cobalt' ), ( 'Cr', 'Chromium' ), ( 'Cs', 'Cesium' ), ( 'Cu', 'Copper' ), ( 'Db', 'Dubnium' ), ( 'Ds', 'Darmstadtium' ), ( 'Dy', 'Dysprosium' ), ( 'Er', 'Erbium' ), ( 'Es', 'Einsteinium' ), ( 'Eu', 'Europium' ), ( 'F', 'Fluorine' ), ( 'Fe', 'Iron' ), ( 'Fl', 'Flerovium' ), ( 'Fm', 'Fermium' ), ( 'Fr', 'Francium' ), ( 'Ga', 'Gallium' ), ( 'Gd', 'Gadolinium' ), ( 'Ge', 'Germanium' ), ( 'H', 'Hydrogen' ), ( 'He', 'Helium' ), ( 'Hf', 'Hafnium' ), ( 'Hg', 'Mercury' ), ( 'Ho', 'Holmium' ), ( 'Hs', 'Hassium' ), ( 'I', 'Iodine' ), ( 'In', 'Indium' ), ( 'Ir', 'Iridium' ), ( 'K', 'Potassium' ), ( 'Kr', 'Krypton' ), ( 'La', 'Lanthanum' ), ( 'Li', 'Lithium' ), ( 'Lr', 'Lawrencium' ), ( 'Lu', 'Lutetium' ), ( 'Lv', 'Livermorium' ), ( 'Mc', 'Moscovium' ), ( 'Md', 'Mendelevium' ), ( 'Mg', 'Magnesium' ), ( 'Mn', 'Manganese' ), ( 'Mo', 'Molybdenum' ), ( 'Mt', 'Meitnerium' ), ( 'N', 'Nitrogen' ), ( 'Na', 'Sodium' ), ( 'Nb', 'Niobium' ), ( 'Nd', 'Neodymium' ), ( 'Ne', 'Neon' ), ( 'Nh', 'Nihonium' ), ( 'Ni', 'Nickel' ), ( 'No', 'Nobelium' ), ( 'Np', 'Neptunium' ), ( 'O', 'Oxygen' ), ( 'Og', 'Oganesson' ), ( 'Os', 'Osmium' ), ( 'P', 'Phosphorus' ), ( 'Pa', 'Protactinium' ), ( 'Pb', 'Lead' ), ( 'Pd', 'Palladium' ), ( 'Pm', 'Promethium' ), ( 'Po', 'Polonium' ), ( 'Pr', 'Praseodymium' ), ( 'Pt', 'Platinum' ), ( 'Pu', 'Plutonium' ), ( 'Ra', 'Radium' ), ( 'Rb', 'Rubidium' ), ( 'Re', 'Rhenium' ), ( 'Rf', 'Rutherfordium' ), ( 'Rg', 'Roentgenium' ), ( 'Rh', 'Rhodium' ), ( 'Rn', 'Radon' ), ( 'Ru', 'Ruthenium' ), ( 'S', 'Sulfur' ), ( 'Sb', 'Antimony' ), ( 'Sc', 'Scandium' ), ( 'Se', 'Selenium' ), ( 'Sg', 'Seaborgium' ), ( 'Si', 'Silicon' ), ( 'Sm', 'Samarium' ), ( 'Sn', 'Tin' ), ( 'Sr', 'Strontium' ), ( 'Ta', 'Tantalum' ), ( 'Tb', 'Terbium' ), ( 'Tc', 'Technetium' ), ( 'Te', 'Tellurium' ), ( 'Th', 'Thorium' ), ( 'Ti', 'Titanium' ), ( 'Tl', 'Thallium' ), ( 'Tm', 'Thulium' ), ( 'Ts', 'Tennnessine' ), ( 'U', 'Uranium' ), ( 'V', 'Vanadium' ), ( 'W', 'Tungsten' ), ( 'Xe', 'Xenon' ), ( 'Y', 'Yttrium' ), ( 'Yb', 'Ytterbium' ), ( 'Zn', 'Zinc' ), ( 'Zr', 'Zirconium' ) ;
CREATE TABLE states (
symbol VARCHAR(2),
name VARCHAR(33)) ;
INSERT INTO
states 
VALUES
( 'AL', 'Alabama' ), ( 'AK', 'Alaska' ), ( 'AZ', 'Arizona' ), ( 'AR', 'Arkansas' ), ( 'CA', 'California' ), ( 'CO', 'Colorado' ), ( 'CT', 'Connecticut' ), ( 'DE', 'Delaware' ), ( 'DC', 'District of Columbia' ), ( 'FL', 'Florida' ), ( 'GA', 'Georgia' ), ( 'HI', 'Hawaii' ), ( 'ID', 'Idaho' ), ( 'IL', 'Illinois' ), ( 'IN', 'Indiana' ), ( 'IA', 'Iowa' ), ( 'KS', 'Kansas' ), ( 'KY', 'Kentucky' ), ( 'LA', 'Louisiana' ), ( 'ME', 'Maine' ), ( 'MD', 'Maryland' ), ( 'MA', 'Massachusetts' ), ( 'MI', 'Michigan' ), ( 'MN', 'Minnesota' ), ( 'MS', 'Mississippi' ), ( 'MO', 'Missouri' ), ( 'MT', 'Montana' ), ( 'NE', 'Nebraska' ), ( 'NV', 'Nevada' ), ( 'NH', 'New Hampshire' ), ( 'NJ', 'New Jersey' ), ( 'NM', 'New Mexico' ), ( 'NY', 'New York' ), ( 'NC', 'North Carolina' ), ( 'ND', 'North Dakota' ), ( 'OH', 'Ohio' ), ( 'OK', 'Oklahoma' ), ( 'OR', 'Oregon' ), ( 'PA', 'Pennsylvania' ), ( 'RI', 'Rhode Island' ), ( 'SC', 'South Carolina' ), ( 'SD', 'South Dakota' ), ( 'TN', 'Tennessee' ), ( 'TX', 'Texas' ), ( 'UT', 'Utah' ), ( 'VT', 'Vermont' ), ( 'VA', 'Virginia' ), ( 'WA', 'Washington' ), ( 'WV', 'West Virginia' ), ( 'WI', 'Wisconsin' ), ( 'WY', 'Wyoming' ) ;

And not once was mentioned John Venn’s 1880 paper On the Diagrammatic and Mechanical Representation of Propositions and Reasonings.