Oracle Learning Note (II)----------Union

Hot and then we'll clean up. Use of a keyword about the database (Oracle)

Union keyword

Union literally means "union", which allows you to combine two SQL statements together to query for the desired query results.

As an example,

The following tables are available:

Student table:

+----+------+------+---------+|Id|Name|Age|ClassID|+----+------+------+---------+|  1 |Guan yu|   * |Class 7||  2 |Zhang Fei|   -  |Class 5||  3 |Zhao Yun|   to  |Class 3||  4 |Ma Chao|   -  |Class 2||  5 |Jack Huang|   -  |Class 8|+----+------+------+---------+

Execute the following SQL statement:

-- Union allows the merging of two SQL statements. Select *  from Student where >=  * Union Select *  from Student where < ;

Query Result:

+----+-------+-------+---------+|Id|Name|Age|ClassID|+----+-------+-------+---------+|  1 |Guan yu|    *  |Class 7||  5 |Jack Huang|    -  |Class 8||  4 |Ma Chao|    -  |Class 2|+----+--------+------+---------+

The Union keyword merges two SQL statements into a query, and the query is displayed together with the results.

Then look at another SQL statement:

-- Union allows the merging of two SQL statements. Select *  from Student where >=  - Union Select *  from Student where < ;

The results of the implementation are as follows:

+----+------+------+---------+|Id|Name|Age|ClassID|+----+------+------+---------+|  1 |Guan yu|   *  |Class 7||  2 |Zhang Fei|   -  |Class 5||  3 |Zhao Yun|   to  |Class 3||  5 |Jack Huang|   -  |Class 8||  4 |Ma Chao|   -  |Class 2|+----+------+------+---------+

By querying all records through the SQL statement, you can see that the Union keyword is equivalent to associating the conditions of the two where clause with or (| |, or). Note The Union does not display duplicate records.

Similar to the Union keyword, the union ALL keyword, the difference is that the UNION ALL keyword query will not merge duplicate records, union All will show all records found, the same record can be repeated.

If the SQL statement above converts the keyword Union to union all, the execution result will be different

SQL statements:

-- Union allows the merging of two SQL statements. Select *  from Student where >=  - Union  All Select *  from Student where < ;

UNION ALL execution Result:

+----+------+------+---------+|Id|Name|Age|ClassID|+----+------+------+---------+|  1 |Guan yu|   *  |Class 7||  2 |Zhang Fei|   -  |Class 5||  3 |Zhao Yun|   to  |Class 3||  5 |Jack Huang|   -  |Class 8||  2 |Zhang Fei|   -  |Class 5||  3 |Zhao Yun|   to  |Class 3||  4 |Ma Chao|   -  | Class 2|+----+------+------+---------+

As you can see, union all also merges two SQL statements, and the difference is that the results of each SQL query are displayed directly (the first 4 records are SQL one, and the last three is the second SQL), and it does not merge duplicate records.

It is important to note that in the Union and Union all queries, the field types of the two SELECT statements match and the number of fields is consistent.

The Union and union all can be not limited to the same table, and two SQL statements can correspond to different tables respectively.

For example, there is another table as follows:

+------------+--------------+------+---------------+|teacher_id|Teacher_name|Age|Teach_classid|+------------+--------------+------+---------------+|A|Tom|   -  |Class 7||B|John doe|   A  |Class 5||C|Harry|   -  |Class 3|+------------+--------------+------+---------------+

Execute the following SQL statement:

Select *  from Student where >=  - Union Select *  from Teacher where teacher < ;

The results of the implementation are as follows:

+----+------+------+---------+|Id|Name|Age|ClassID|+----+------+------+---------+| 1  |Guan yu|   *  |Class 7|| 2  |Zhang Fei|   -  |Class 5|| 3  |Zhao Yun|   to  |Class 3|| 5  |Jack Huang|   -  |Class 8||B|John doe|   A  |Class 5||C|Harry|   -  |Class 3|+----+------+------+---------+

The above example merges two SQL statements that query different tables by the Union keyword, noting that the name of the field after the merge is named after the field name of the first SQL statement.

Union All is the same, except that duplicate records are not merged.

Oracle Learning Note (II)----------Union