UNION and UNION ALL

UNION and UNION ALL

UNION allows to combine the results of two or more TABLE using SELECT statement into a single result set.

You can use UNION on table that have same structure to get results like they must have the same number of columns and columns must have compatible data types

by default, UNION removes duplicate rows from the result set but if you use ALL (UNION ALL) then all rows are included in the results and duplicates are not removed

When UNION is used, the individual SELECT statement cannot have their own ORDER BY or COMPUTE clause
but There can be only one ORDER BY or COMPUTE clause after the last SELECT statement.

Syntax:
select_statement UNION [ALL] select_statement

CREATE TABLE  FY1
(CustomerNo INT PRIMARY KEY,
 OrderAvg_FY1 INT,
 OrderCount_FY1 INT)

INSERT INTO FY1 VALUES(0155, 300, 7),
(0133, 700, 6),
(0144, 200, 2)

CREATE TABLE  FY2
(CustomerNo INT PRIMARY KEY,
 OrderAvg_FY2 INT,
 OrderCount_FY2 INT)

INSERT INTO FY2 VALUES(0155, 100, 2),
(0130, 500, 4),
(0144, 20, 1),
(0011, 25, 3)


SELECT *FROM FY1
UNION
SELECT *FROM FY2

Results:

and result set of UNION ALL are: