SQL Server Union and Union All

UNION : UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

Syntax:

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

UNION

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

EXAMPLE :

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION

    

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

UNION ALL: UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.The same rules that apply to UNION apply to the UNION ALL operator.

Syntax:

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

UNION ALL

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

EXAMPLE :

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION ALL

    

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;