Oracle intersection and set

Today, I looked at Oracle intersection and the set, and after that I went through the search for data, after testing, organized as follows:

1. and set

Table 1:

INSERT into student1 values (1, ' Student 1 ');
INSERT into student1 values (1, ' Student 2 ');
INSERT into student1 values (1, ' Student 3 ');

Table 2:

INSERT into Student2 values (1, ' Student 1 ');
INSERT into Student2 values (1, ' Student 4 ');
INSERT into Student2 values (1, ' student 5 ');

The SET statement:

[SQL]View Plaincopy print?

1. SELECT * FROMstudent1
2. Union All
3. SELECT * FROMstudent2

Select *from student1union allselect *from Student2

Post-check Results

See the test results to understand, union all to two result sets to do union operations, including repeating rows, not sorted.

If you remove the ALL keyword,

[SQL]View Plaincopy print?

1. SELECT * FROMstudent1
2. Union
3. SELECT * FROMstudent2

Select *from student1unionselect *from student2

When you see the results, you can conclude that the two result sets are combined, not including duplicate rows, and that the default rules are sorted

2. Intersection

[Delphi]View Plaincopy print?

1. Select *from student1
2. Intersect
3. Select *from Student2

Select *from student1intersectselect *from student2

The result is:

Yes, returning the same part of the query results is their intersection

Add: minus keyword

The query time to put table 1 in front,

[SQL]View Plaincopy print?

1. SELECT * FROMstudent1
2. Minus
3. SELECT * FROMstudent2

Select *from student1minusselect *from student2

The result is:

The query time to put table 2 in front,

[SQL]View Plaincopy print?

1. SELECT * FROMstudent2
2. Minus
3. SELECT * FROMstudent1

Select *from student2minusselect *from student1

The result is:

Use minus to return the part of a row record that is not the same as the second query result in the first query result, that is, the difference set of two results

The result set using the query above has two most basic rules:
(1) The Order of columns and columns must be the same in all queries.
(2) Data type must be compatible

Oracle intersection and set