SQL – UNION AND UNION ALL

Spread the love

SQL- UNION ALL – it merges the multiple queries results and returns the all the records for every column of all the tables

SQL- UNION – it merges the multiple queries results and returns the distinct records for every column of all the tables

Prerequisite – Total no. of Columns And Data Types must me be matched, Else it will through an error
Error – Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

 

PRODUCT TABLE

S_NOPRODUCTMONTHYEARQTYSALES
1NIKEDEC2016107599
2REEBOKJAN2014903500
3FILAFEB201786000
4PUMAJAN2015165000
5NULLAPR201252500

 

SALES TABLE

PRODUCTQTYSALES
NIKE107599
REEBOK181200
FILA86000
PUMA165000
NULL52500

 

UNION ALL

SELECT PRODUCT , QTY , SALES
FROM PRODUCT

UNION ALL

SELECT PRODUCT , QTY , SALES
FROM SALES

PRODUCTQTYSALES
NIKE107599
REEBOK903500
FILA86000
PUMA165000
NULL52500
NIKE107599
REEBOK181200
FILA86000
PUMA165000
NULL52500

 

UNION

It will show the unique records from merged results

SELECT PRODUCT , QTY , SALES
FROM PRODUCT

UNION

SELECT PRODUCT , QTY , SALES
FROM SALES

 

Result

PRODUCTQTYSALES
NULL52500
FILA86000
NIKE107599
PUMA165000
REEBOK181200
REEBOK903500

Test to wrong columns input, so mapped sales to qty , qty to sales

SELECT QTY , SALES
FROM PRODUCT

UNION

SELECT SALES , QTY
FROM SALES

Result

QTYSALES
52500
86000
107599
165000
903500
120018
25005
500016
60008
759910

 

 

 
difference between union all and Union
sql union all vs Union
union all vs Union in SQL
Write union all and Union in SQL
union all in sql
sql union all
union all query writing
write query as union all
query writing union all
structure of union all sql
easy method to write union all query
remove complexity of query writing by union all
Technics to write union all query
structure of union all query sql
Union in sql
sql Union
Union query writing
write query as Union
query writing Union
structure of Union sql
easy method to write Union query
remove complexity of query writing by Union
Technics to write Union
structure of Union query sql