Back

Explore Courses Blog Tutorials Interview Questions
+3 votes
153 views
in SQL by (47.6k points)

What is the difference between UNION and UNION ALL?

1 Answer

+5 votes
by (40.4k points)
edited by

UNION: This command is used to select the tuples which have related information from two or more tables.

Are you interested in learning SQL from scratch! Have a look at this interesting video on SQL provided by Intellipaat:

If you understand by watching videos, here’s the informative video about UNION

It’s similar to JOIN command. But when we are using UNION command, the selected columns must be of the same data type. It removes all the duplicate records from the final result.

Syntax

Select Column1, Column2, Column3 from Table A

UNION

Select Column1, Column2, Column3 from Table B

 

Example: There are two tables named Student and Teacher

TABLE 1: Student

StudentID

StudName

StudentAge

1

Bhumi

16

2

Manas

17

3

Mallik

18

4

Suraj

15

5

Raj

15.5

 

TABLE 2: Teacher

TeacherID

TeachName

TeacherAge

1

Betty

28

2

Mallik

29

3

James

45

4

Suraj

30

Query: 

SELECT StudName FROM Student

UNION

SELECT TeachName FROM Teacher

NO

StudName

1

Bhumi

2

Betty

3

James

4

Manas

5

Mallik

6

Raj

7

Suraj

Here, you can see the duplicate tuples are removed.

UNION ALL: This command is the same as UNION command. It just concatenates the records. Unlike UNION, UNION ALL pulls all the values from all the tables i.e. it doesn’t eliminate duplicate records.

Syntax

Select Column1, Column2, Column3 from Table A

UNION

Select Column1, Column2, Column3 from Table B

Example: Consider the above two tables Student and Teacher.

 Query:

Select Column1, Column2, Column3 from Table A

UNION ALL

Select Column1, Column2, Column3 from Table B

NO

StudName

1

Bhumi

2

Manas

3

Mallik

4

Suraj

5

Raj

6

Betty

7

Mallik

8

James

9

Suraj

You can observer here, the result is a combination of both the tables. 

UNION vs UNION ALL

The only difference between Union and Union All is that Union All will not removes duplicate rows or records, instead, it just selects all the rows from all the tables which meets the conditions of your specifics query and combines them into the result table.

 UNION doesn’t work with a column that has Text Data Type. Whereas, UNION ALL works with all data type columns.

Related questions

0 votes
1 answer
asked May 1, 2020 in SQL by Sudhir_1997 (55.5k points)
0 votes
1 answer
0 votes
1 answer
asked Oct 20, 2020 in SQL by dev_sk2311 (43.2k points)
0 votes
1 answer
asked Oct 19, 2020 in SQL by dev_sk2311 (43.2k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...