SQL UNION 与 UNION ALL的区别

/*--  创建Student表 --*/

IF (Object_id('Student') IS NOT NULL)

    DROP TABLE Student
 
CREATE TABLE Student   
(
    Id int primary key,   
    [Name] nvarchar(50) NOT NULL,   
    Score int NOT NULL  
)

/*-- -添加数据到Student表 --*/ 
insert into student values(1,'Aaron',78)  
insert into student values(2,'Bill',76)  
insert into student values(3,'Cindy',89)  
insert into student values(4,'Damon',90)   
insert into student values(5,'Ella',73)  
insert into student values(6,'Frado',61)  
insert into student values(7,'Gill',99)   
insert into student values(8,'Hellen',56)   
insert into student values(9,'Ivan',93)   

insert into student values(10,'Jay',90) 

/*-- UNION例子 --*/
 select * from student where id<4
 union   

 select * from student where id>2 and id<6 

/*-- Union All例子：--*/
select * from student where id<4   
union all   

select * from student where id>2 and id<6

/*-- Union和Union All的区别之一在于对重复结果的处理 --*/

/*交换一个两个SELECT语句的顺序，看看结果是怎样的 --*/
select * from student where id>2 and id<6   
union   
select * from student where id<4

select * from student where id>2 and id<6   
union all   
select * from student where id<4

/*-- 对于UNION来说，交换两个SELECT语句的顺序后结果仍然是一样的，因为UNION会自动排序。而UNION ALL在交换了SELECT语句的顺序后结果则不相同，因为UNION ALL不会对结果自动进行排序。--*/