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)
No comments:
Post a Comment