MySQL - UNION Operator

Hello there, future database wizards! Today, we're going to dive into one of MySQL's most powerful tools: the UNION operator. It's like a magical wand that allows us to combine the results of multiple SELECT statements into a single result set. Exciting, right? Let's embark on this adventure together!

MySQL UNION Operator

The UNION operator is used to combine the result sets of two or more SELECT statements. It's like inviting different groups of friends to the same party - they all come together in one place!

Here's the basic syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Let's look at a practical example. Imagine we have two tables: 'employees' and 'customers'.

-- Create employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- Insert some data
INSERT INTO employees VALUES
(1, 'John Doe', '[email protected]'),
(2, 'Jane Smith', '[email protected]');

-- Create customers table
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- Insert some data
INSERT INTO customers VALUES
(1, 'Alice Johnson', '[email protected]'),
(2, 'Bob Wilson', '[email protected]');

-- Now, let's use UNION
SELECT name, email FROM employees
UNION
SELECT name, email FROM customers;

This query will give us a combined list of names and emails from both tables. Cool, right?

Remember, UNION removes duplicate rows by default. It's like a bouncer at our party, making sure no one gets in twice!

UNION with WHERE clause

We can make our UNION even more powerful by adding WHERE clauses. This allows us to filter the results from each SELECT statement before they're combined.

SELECT name, email FROM employees WHERE id > 1
UNION
SELECT name, email FROM customers WHERE name LIKE 'B%';

This query will give us employees with an ID greater than 1 and customers whose names start with 'B'. It's like having VIP sections at our party!

UNION with ORDER BY clause

Want to sort your combined results? No problem! We can use ORDER BY with UNION, but it must come at the end of the entire UNION statement.

SELECT name, email FROM employees
UNION
SELECT name, email FROM customers
ORDER BY name ASC;

This will give us all names and emails, sorted alphabetically by name. It's like arranging our party guests in alphabetical order!

UNION with Aliases

Sometimes, the columns in our tables might have different names. No worries! We can use aliases to make them match up.

SELECT name, email AS contact FROM employees
UNION
SELECT customer_name, customer_email AS contact FROM customers;

Here, we're assuming the customers table has slightly different column names. The AS keyword lets us rename them on the fly!

UNION ALL Operator

Remember our bouncer who was removing duplicates? Sometimes we want to let everyone in, duplicates and all. That's where UNION ALL comes in.

SELECT name FROM employees
UNION ALL
SELECT name FROM customers;

This will give us all names, even if there are duplicates between the two tables. It's like having an "everyone's welcome" policy at our party!

UNION Operator Using Client Program

Now, let's see how we can use the UNION operator in a client program. I'll show you an example using Python and the mysql-connector library.

import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='your_username', password='your_password',
                              host='127.0.0.1', database='your_database')
cursor = cnx.cursor()

# Execute the UNION query
query = """
SELECT name, email FROM employees
UNION
SELECT name, email FROM customers
ORDER BY name
"""
cursor.execute(query)

# Fetch and print the results
for (name, email) in cursor:
    print(f"{name}: {email}")

# Close the connection
cursor.close()
cnx.close()

This script connects to your MySQL database, executes a UNION query, and prints out the results. It's like having a personal assistant to manage your party guest list!

Here's a table summarizing the UNION methods we've discussed:

Method	Description
UNION	Combines results, removes duplicates
UNION ALL	Combines results, keeps duplicates
UNION with WHERE	Filters results before combining
UNION with ORDER BY	Sorts combined results
UNION with Aliases	Renames columns for compatibility

And there you have it, my young database enthusiasts! We've journeyed through the land of UNION operators, from basic combinations to complex queries with filtering and sorting. Remember, practice makes perfect, so don't be afraid to experiment with these queries on your own databases. Who knows? You might just throw the best data party in town!

Credits: Image by storyset