MySQL - UNION Operator

MySQL - The UNION Operator

The UNION operator in MySQL is a powerful tool used to combine the result sets of two or more SELECT statements into a single result set. This is particularly useful when you need to retrieve similar data stored across different tables or from the same table under different conditions.

UNION vs. UNION ALL

There are two variations of the UNION operator:

• UNION: Combines the result sets and removes duplicate rows. To do this, MySQL implicitly performs a DISTINCT operation, which may have a performance cost.
• UNION ALL: Combines the result sets and includes all rows, including duplicates. This is faster than UNION because it avoids the overhead of checking for and removing duplicates. Use UNION ALL unless you specifically need to eliminate duplicates.

Rules for UNION Compatibility

For a UNION operation to succeed, the SELECT statements involved must be “union compatible”. This means they must meet the following criteria:

• They must have the same number of columns in the result set.
• The data types of corresponding columns must be compatible (e.g., you can union a VARCHAR with a CHAR, but not with a DATETIME without casting).
• The columns must appear in the same order in each SELECT statement.

Example: Combining Customers and Prospects

Let’s assume we have two tables: active_customers and prospects. We want a single mailing list.

CREATE TABLE active_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE prospects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

INSERT INTO active_customers (first_name, last_name, email) VALUES
('Walter', 'Brown', 'walter.b@example.com'),
('Bernice', 'Smith', 'bernice.s@example.com');

INSERT INTO prospects (first_name, last_name, email) VALUES
('Peter', 'Jones', 'peter.j@example.com'),
('Bernice', 'Smith', 'bernice.s@example.com'); -- Duplicate email

To get a unique list of all contacts, we use UNION:

SELECT first_name, last_name, email FROM active_customers
UNION
SELECT first_name, last_name, email FROM prospects;

This will return three rows, as the duplicate ‘Bernice Smith’ record is removed.

To get all records, including duplicates, we use UNION ALL:

SELECT first_name, last_name, email FROM active_customers
UNION ALL
SELECT first_name, last_name, email FROM prospects;

This will return all four rows.

Using WHERE, ORDER BY, and Aliases

• WHERE Clause: You can apply a WHERE clause to each individual SELECT statement to filter its results before the union.
• ORDER BY Clause: An ORDER BY clause can be applied only once, at the very end of the entire UNION statement. It sorts the final combined result set.
• Aliases: Column aliases are determined by the first SELECT statement. You can use them in the final ORDER BY clause.

SELECT
    first_name AS f_name,
    last_name AS l_name,
    email
FROM
    active_customers
WHERE
    last_name <> 'Smith'

UNION

SELECT
    first_name,
    last_name,
    email
FROM
    prospects

ORDER BY l_name ASC, f_name ASC;

Modern Set Operations (MySQL 8.0.31+)

Recent versions of MySQL have introduced INTERSECT and EXCEPT, which are standard SQL set operators that provide more ways to compare result sets:

• INTERSECT: Returns only the rows that appear in both result sets.
• EXCEPT: Returns rows from the first result set that do not appear in the second result set.

-- Find people who are both an active customer AND a prospect
SELECT first_name, last_name, email FROM active_customers
INTERSECT
SELECT first_name, last_name, email FROM prospects;
-- Result: Bernice Smith

UNION in Client Programs (Modern Examples)

Here is how to execute a UNION query using modern code in various languages.

NodeJS
Python
Java
PHP



// Using mysql2 with async/await
const mysql = require('mysql2/promise');

async function getMailingList(pool) {
    const sql = `
        SELECT first_name, last_name, email FROM active_customers
        UNION
        SELECT first_name, last_name, email FROM prospects
        ORDER BY last_name;`
    try {
        const [rows] = await pool.query(sql);
        console.log('Combined Mailing List:', rows);
        return rows;
    } catch (error) {
        console.error('Failed to retrieve mailing list:', error);
    }
}



# Using mysql-connector-python with context managers
import mysql.connector

def get_mailing_list(config):
    sql = """
        SELECT first_name, last_name, email FROM active_customers
        UNION
        SELECT first_name, last_name, email FROM prospects
        ORDER BY last_name;"""
    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute(sql)
                results = cursor.fetchall()
                print('Combined Mailing List:', results)
                return results
    except mysql.connector.Error as err:
        print(f"Database Error: {err}")



// Using JDBC with try-with-resources
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MailingListDAO {
    public List<String> getMailingList(String url, String user, String password) {
        String sql = "SELECT first_name, last_name, email FROM active_customers " +
                     "UNION " +
                     "SELECT first_name, last_name, email FROM prospects " +
                     "ORDER BY last_name;";
        List<String> mailingList = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                mailingList.add(rs.getString("email"));
            }
            System.out.println("Combined Mailing List fetched successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return mailingList;
    }
}



// Using mysqli with object-oriented style and prepared statements
<?php
function getMailingList($dbHost, $dbUser, $dbPass, $dbName) {
    $mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }

    $sql = "SELECT first_name, last_name, email FROM active_customers "
         . "UNION "
         . "SELECT first_name, last_name, email FROM prospects "
         . "ORDER BY last_name;";

    $result = $mysqli->query($sql);
    $mailingList = [];

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $mailingList[] = $row;
        }
        $result->free();
    } else {
        echo "Error: " . $mysqli->error;
    }

    $mysqli->close();
    print_r($mailingList);
    return $mailingList;
}