Union Vs Union All  

With Examples

   Part - 16

create table tblEmployee_1 (id int)

Insert tblEmployee_1 values (1),(2),(3)

create table tblEmployee_2 (id int)

Insert tblEmployee_2 values (3),(4)

SELECT id FROM  tblEmployee_1

UNION 

SELECT id FROM tblEmployee_2

SELECT id FROM  tblEmployee_1

UNION ALL

SELECT id FROM tblEmployee_2

It Removes Duplicates and gives 

UNION ALL

------------------------------------------------------------------------------

--Columns of Select Statement should be schema compatible (Implicit Conversion)

--> Datatype compatible  (Implicit Conversion)

------------------------------------------------------------------------------

create table tblEmployee_3 (Dates date) 

Insert tblEmployee_3 values ('2019-01-02'),('2020-02-03')

SELECT Dates FROM tblEmployee_3 --> date 

UNION 

SELECT id FROM tblEmployee_2 --> Int

SELECT Dates FROM tblEmployee_3 --> date 

UNION ALL

SELECT id FROM tblEmployee_2 --> Int

--> Implicit conversion works

create table tblEmployee_4 (id varchar(5)) 

Insert tblEmployee_4 values (3),(4)

SELECT id FROM tblEmployee_4  -- varchar with numeric data

UNION 

SELECT id FROM tblEmployee_2 -- int

Both the tables are having 3,4 each and it has removed duplicate records and provided only once

SELECT id FROM tblEmployee_4  -- varchar with numeric data

UNION ALL

SELECT id FROM tblEmployee_2 -- int

Implicit conversion happened as the table is having only numeric values. 

Lets Add some text record, to fail the implicit conversion

Insert tblEmployee_4 values ('test')

create table tblEmployee_5 (id int)

Insert tblEmployee_5 values (6),(7),(8),(9),(1)

create table tblEmployee_6 (id int)

Insert tblEmployee_6 values (8),(2),(6),(5),(4),(3)