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)