Usage of union and Union all

In the project, the region information for reference to this quote region is queried, and the information for this region and the queried reference quote region information are combined into a temporary table, which uses union or union all,

SQLCodeAs follows:

SQL 1

 1   Create Type ty_areaandgoods As   Table (
 2 Areaid Int ,
 3 Typeid Int 
 4 )
 5  Declare   @ Dt Ty_areaandgoods;
 6   Insert   Into   @ Dt   Values ( 1 , 1 );
 7   Select AA. areaid, AA. goodstypeid From Md_area AA, @ Dt TTWhere AA. quotationtype = TT. areaid And AA. goodstypeid = TT. typeid
 8   Union 
 9   Select Areaid, typeid From   @ Dt 

I create a table value variable type, declare the variables of this type, use this variable to pass data, and then use Union to implement functional requirements.

Note the following when using union:

1. All Union queries must have the same number of columns in the select list.

2. The column title at the Result Level of the Union query is only obtained from the first query.

3. The data types corresponding to the query must be implicitly consistent.

4. The default return option for Union queries is distinct.

SQL 2

 1   Create   Table Tb_test
 2 (
 3 Col Int   Identity ( 1 , 1 ),
 4 Name Char (10 )
 5 )
 6   Create   Table Tb_test2
 7 (
 8 Col Int   Identity ( 1 , 1 ),
 9 NameNvarchar ( 20 )
 10 )
 11   Create   Table Tb_test3 (
 12 Col Int   Identity ( 1 , 1 ),
 13 NameNvarchar ( 10 )
 14 )
 15   Insert   Into Tb_test Values ( '  Aaa  ' );
 16   Insert   Into Tb_test Values ('  Bbb  ' );
 17   Insert   Into Tb_test2 Values ( '  Ccccccccccc  ' );
 18   Insert   Into Tb_test2 Values ( ' Ccccccccccccccccc  ' );
 19   Insert   Into Tb_test2 Values ( '  Aaa  ' );
 20   Insert   Into Tb_test3 Values ( '  Ddd ' );
 21   Insert   Into Tb_test3 Values ( '  Fff  ' );
 22   Select Name From Tb_test
 23   Union 
 24  Select Name From Tb_test2
 25   Union  
 26   Select Name From Tb_test3

You can see that the query result has been processed by distinct. to display all the results, you need to make the following changes:

SQL 3

  1   select  name  as  test  from  tb_test 
  2   Union   all  
  3   select  name  from  tb_test2 
  4   Union   all  
  5   select  name  from  tb_test3