SQL Action result set-set, difference, intersection, result set sort

Operation Result set

In order to fit the test, two tables have been built, and some test data have been added, which are repeatedly recorded as soochow characters.

Table: Person_1 wei People

　　　

Table: Person_2 Shu Characters

　　

　　A, Union forms and sets

Union can connect two or more result sets to form a "set". all the records of the child result set are grouped together to form a new result set.

1. Limited conditions

If you use union to connect the result set, there are 4 qualifications.

(1), the child result set to have the same structure.

(2), the number of columns of the word result set must be the same.

(3), the data type corresponding to the child result set must be compatible.

(4), each child result set cannot contain an order BY and a COMPUTE clause.

2. Grammatical form

Select_statement Union [all] select_statement

All represents the final result set that will contain all the rows, and cannot delete duplicate rows.

Example:

Select name from person_1 UNION SELECT name from person_2

The resulting result is:

　　　

Noting the repetition of records, Sun Quan and Zhou Yu only showed one. Let's replace union with union all to see what the result is:

Select name from person_1 UNION all SELECT name from person_2

Notice that the duplicate record, Sun Quan and Zhou Yu appeared two times, this is the union all and the Union difference.

　　

　　　B, except forming a difference set

Except can connect two or more result sets to form a "difference set". returns records that are already in the results collection on the left and those that are not in the right result set.

Qualifying conditions:

1, the child result set to have the same structure.

2, the child result set must have the same number of columns.

3. The data type corresponding to the child result set must be compatible.

4. Each child result set cannot contain an order BY and a COMPUTE clause.

Grammatical form:

select_statement except Select_statement

Automatically delete duplicate rows.

Example:

Select name from person_1 EXCEPT SELECT name from person_2

Results:

　　　　

Notice the table person_2, Sun Quan Yu has been removed.

　　C, intersect formation intersection

Intersect can connect two or more result sets to form an "intersection." returns records from both the left and right result sets.

　　1. Limited conditions　　

If you use except to connect to the result set, there are 4 qualifications.

(1), the child result set to have the same structure.

(2), the number of columns in the child result set must be the same.

(3), the data type corresponding to the child result set must be compatible.

(4), each child result set cannot contain an ORDER BY or COMPUTE clause.

　　2. Grammatical form

select_statement intersect select_statement

Example:

Select name from person_1 INTERSECT SELECT name from person_2

The results returned are as follows:

　　　

Notice that only two tables are recorded (Zhou Yu, Sun Quan), which is the so-called intersection.

　　D. Sorting of result sets

Select name from person_1 INTERSECT select name from Person_2 ORDER by name DESC--the field names here are the same, if different, remember to sort the column names, only the columns of the first table Name

There's only two points to note.

1.ORDER by is the result of sorting through the entire operation, not on a single data set.

The field name that is sorted after 2.ORDER by is the field name or alias of the first dataset.

SQL Action result set-set, difference, intersection, result set sort