UNION & UNION ALL in SQL

UNION and UNION ALL both are used to display data from two or more tables. The difference between them is UNION filter duplicate data and UNION ALL presents all the data including duplicate data.

Database- jai_school;

CREATE DATABASE jai_school;

Create two tables

CREATE TABLE india(s_n INT(10) NOT NULL AUTO_INCREMENT,states VARCHAR(50), PRIMARY KEY(s_n));
CREATE TABLE america(s_n INT(10) NOT NULL AUTO_INCREMENT,states VARCHAR(50), PRIMARY KEY(s_n));

Insert some data

INSERT INTO india(states)VALUES("Rajasthan"),("Maharastra"),("MP");
INSERT INTO america(states)VALUES("california"),("states"),("Florida");

India Table

india_table

America Table

us_table

UNION query example

SELECT states FROM india
UNION
SELECT states FROM america;

Output:-

union_query_result

UNION ALL query example

SELECT states FROM india
UNION ALL
SELECT states FROM america;

Output:-

union_all_query result

Leave a Comment