The difference between Union and union ALL in Oracle

Students who write SQL frequently may use the two Keywords Union and union all, and maybe you know that they can combine the result sets of two queries.

So what's the difference? Below we have a simple analysis.

Comparison

Union: Distinct The two result sets, excluding duplicate rows, equivalent to the same, and ordering the default rules;

UNION ALL: A set of two result sets, including repeated rows, that all the results are displayed, whether or not repeated;

Let's give a simple example to prove the above conclusion:

1. Preparing data:

drop table student;

CREATE TABLE Student
(
     ID int primary KEY,
     name NVARCHAR2 not NULL, score number is not
     null
);

INSERT into student values (1, ' Aaron ',);
INSERT into student values (2, ' Bill ', the);
INSERT into student values (3, ' Cindy ',);
INSERT into student values (4, ' Damon ',);
INSERT into student values (5, ' Ella ', the);
INSERT into student values (6, ' Frado ',);
INSERT into student values (7, ' Gill ',);
INSERT into student values (8, ' Hellen ',);
INSERT into student values (9, ' Ivan ',);
INSERT into student values ("Jay",);

Commit

2. Compare different points

Query comparison ①

--UNION ALL 
SELECT * FROM student where ID < 4
UNION ALL
SELECT * from student where ID > 2 and ID < 6
   
--Union
SELECT * FROM student where ID < 4
Union
SELECT * from student where ID > 2 and ID < 6

UNION ALL query Results:

Union query Result:

By comparison it is not difficult to see that union all does not remove duplicate records, and union removes duplicate records.

Query comparison ②

--UNION ALL
SELECT * from student where ID > 2 and IDS < 6
UNION ALL
SELECT * Student where ID &L T 4

--Union
SELECT * from student where ID > 2 and IDS < 6
Union
SELECT * FROM student where ID &L T 4

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/