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

Union [all] select_statement

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

Example:

SELECT  from person_1 UNION SELECT  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  from person_1 UNION  All SELECT  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:

except select_statement

Automatically delete duplicate rows.

Example:

SELECT  from person_1 EXCEPT SELECT  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

intersect select_statement　　　　　　　　

Example:

SELECT  from person_1 INTERSECT SELECT  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  from person_1 INTERSECT SELECT  from person_2 ORDER  by DESC --the field names here are the same, if different, remember to sort the column names, only the first table column names 　　

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