MySQL UNION Operator

The UNION operator in MySQL combines the result sets of two or more SELECT queries into a single result. By default, UNION removes duplicate rows. To keep duplicates, use UNION ALL.

When to Use UNION

Use UNION when the same type of data is stored in separate tables and a combined view is needed — for example, customers from two regional databases, or active and archived orders in different tables.

Rules for UNION

• Each SELECT must return the same number of columns.
• Corresponding columns must have compatible data types.
• Column names in the result come from the first SELECT statement.

Syntax

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Sample Tables

CREATE TABLE north_customers (
    customer_id INT,
    customer_name VARCHAR(100),
    city VARCHAR(50)
);

INSERT INTO north_customers VALUES (1, 'Anita Roy', 'Delhi');
INSERT INTO north_customers VALUES (2, 'Ravi Mehta', 'Jaipur');
INSERT INTO north_customers VALUES (3, 'Pooja Sharma', 'Agra');

CREATE TABLE south_customers (
    customer_id INT,
    customer_name VARCHAR(100),
    city VARCHAR(50)
);

INSERT INTO south_customers VALUES (4, 'Suresh Nair', 'Chennai');
INSERT INTO south_customers VALUES (5, 'Kavya Das', 'Bengaluru');
INSERT INTO south_customers VALUES (2, 'Ravi Mehta', 'Jaipur');

Note: Ravi Mehta (ID 2) appears in both tables.

Example: UNION (Removes Duplicates)

SELECT customer_id, customer_name, city FROM north_customers
UNION
SELECT customer_id, customer_name, city FROM south_customers;

Result:

customer_id | customer_name | city
------------+---------------+-----------
1           | Anita Roy     | Delhi
2           | Ravi Mehta    | Jaipur
3           | Pooja Sharma  | Agra
4           | Suresh Nair   | Chennai
5           | Kavya Das     | Bengaluru

Ravi Mehta appears only once — UNION removed the duplicate.

Example: UNION ALL (Keeps Duplicates)

SELECT customer_id, customer_name, city FROM north_customers
UNION ALL
SELECT customer_id, customer_name, city FROM south_customers;

Result:

customer_id | customer_name | city
------------+---------------+-----------
1           | Anita Roy     | Delhi
2           | Ravi Mehta    | Jaipur
3           | Pooja Sharma  | Agra
4           | Suresh Nair   | Chennai
5           | Kavya Das     | Bengaluru
2           | Ravi Mehta    | Jaipur

Ravi Mehta appears twice because UNION ALL does not remove duplicates.

UNION with ORDER BY

ORDER BY applies to the entire combined result, not individual queries. Place it at the end.

SELECT customer_name, city FROM north_customers
UNION
SELECT customer_name, city FROM south_customers
ORDER BY customer_name ASC;

UNION with WHERE in Individual Queries

SELECT customer_name, city FROM north_customers WHERE city = 'Delhi'
UNION
SELECT customer_name, city FROM south_customers WHERE city = 'Chennai';

UNION vs JOIN

Key Points

• UNION stacks result sets from multiple SELECT queries vertically.
• UNION removes duplicate rows; UNION ALL keeps them.
• All SELECT statements must have the same number of columns with compatible types.
• Column names in the output are taken from the first SELECT.
• ORDER BY is placed at the end of the last SELECT and applies to the full result.