Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator
union
operators

What is the SQL UNION operator?

Saad Akbar

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

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.

RELATED TAGS

sql
communitycreator
union
operators

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring