What is the SQL UNION operator?

Overview

The SQL UNION operator is used to aggregate the result of multiple SQL SELECT statements. However, there are some conditions that must be fulfilled in order for the UNION operator to work successfully.

Each SELECT statement used in the UNION operator must have an equal number of columns.
The columns must have the same data types.
The columns in each SELECT statement must also have the same order.

Syntax

The syntax of the UNION statement is given below:

SELECT column(s) 
FROM first_table
UNION
SELECT column(s) 
FROM second_table;

CREATE DATABASE test;
CREATE TABLE Consumers (
    ConsumerID int,
    ConsumerName varchar(20),
    City varchar(20),
    Country varchar(20)
);
INSERT INTO Consumers
VALUES (1, 'Ethan Goldberg','NewYork','U.S.A');
INSERT INTO Consumers
VALUES (2, 'John Snow','Berlin','Germany');
INSERT INTO Consumers
VALUES (3, 'Saad Qureshi','Ontario','Canada');
INSERT INTO Consumers
VALUES (4, 'Amir Iqbal','Paris','France');
INSERT INTO Consumers
VALUES (5, 'Dania Khan','Bowling Green','U.S.A');
INSERT INTO Consumers
VALUES (6, 'Yousaf Khan','Tenesse','U.S.A');
CREATE TABLE Producers (
    ProducerID int,
    PriducerName varchar(20),
    City varchar(20),
    Country varchar(20)
);
INSERT INTO Producers
VALUES (1, 'Amazon','NewYork','U.S.A');
INSERT INTO Producers
VALUES (2, 'Walmart','Berlin','Germany');
INSERT INTO Producers
VALUES (3, 'TacoBell','Ontario','Canada');
INSERT INTO Producers
VALUES (4, 'Walmart','NewYork','U.S.A');
INSERT INTO Producers
VALUES (5, 'Mercedes-Benz','Franfurt','Germany');
INSERT INTO Producers
VALUES (6, 'BMW','Berlin','Germany');
SELECT City FROM Consumers
UNION
SELECT City FROM Producers

What is the SQL UNION operator?

Overview

Syntax

Database schema

Consumers

Producers

Example

Output

Explanation

ConsumerID	Name	City	Country
1	Ethan Goldberg	NewYork	U.S.A
2	John Snow	Berlin	Germany
3	Saad Qurshi	Ontario	Canada
4	Amir Iqbal	Paris	France
5	Dania Khan	Bowling Green	U.S.A
6	Yousaf Khan	Tenesse	U.S.A

ProducerID	Name	City	Country
1	Amazon	NewYork	U.S.A
2	Walmart	Berlin	Germany
3	TacoBell	Ontario	Canada
4	Walmart	NewYork	U.S.A
5	MercedsBenz	Frankfurt	Germany
6	BMW	Berlin	Germany