MS SQL Server: UNION ALL Operator in SQL Server

The UNION ALL operator helps combine the result set from 2 or more SELECT statements in SQL Server.

Unlike the UNION operator, the UNION ALL operator returns all rows from the query and does not delete duplicate rows.

Each SELECT in the UNION operator must have the same number of columns in the result set with the corresponding data type.

UNION ALL operator syntax

 SELECT bieu_thuc1, bieu_thuc2, … bieu_thucn 
 FROM bang 
 [WHERE dieu_kien] 
 UNION ALL 
 SELECT bieu_thuc1, bieu_thuc2, … bieu_thucn 
 FROM bang 
 [WHERE dieu_k ien]; 

Variable name or variable value

bieu_thuc1, bieu_thuc2,. bieu_thucn

The column or calculation value you want to retrieve.

state

Table wants to get the record. Must have at least 1 table in the FROM clause.

 SEL ECT sanpham_id 
 FROM sanpham 
 UNION ALL 
 SELECT sanpham_id 
 FROM hangtonkho; 

This example returns many sanpham_id in the result set if they appear in both the sanpham and hangtonkho tables. If you want to delete duplicates, use the UNION whole element.

As an example, - use ORDER BY

The UNION ALL operator can use the ORDER BY clause to sort query results.

 SELECT dan hba_id, danhba_ten 
 FROM danhba 
 WHERE ten_trang = 'Tipsmake.com' 
 UNION ALL 
 SELECT congty_id, congty_ten 
 FROM congty 
 WHERE ten_trang = 'TrangCuaBan.com' 
 ORDER BY 2;