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;

Database schema

Consumers

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

Producers

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

Example

The code below selects the cities from "Consumers" and "Producers" (the above tables) and then takes their `UNION`. 
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

Note: The UNION operator only selects the distinct values from the selected columns.

Output

The distinct values obtained from the columns after their `UNION` are returned.

Explanation

+ **Line 3–26**: We declare the `Consumers` table and populate it with the values according to the defined schema. + **Line 29–52**: We declare the `Producers` table and populate it with the values according to the defined schema. + **Line 56–58**: We apply the `UNION` operator between the `Consumers` and `Producers` table.
Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
YouTubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)