[Teach Youself SQL in 10 Minutes] combining queries-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_1678/article/details/82044733

本文详细介绍了SQL中UNION操作符的使用方法及其规则，包括如何将多个SELECT语句的结果集合并为一个单一结果集，并探讨了如何通过UNION和UNION ALL来包含或消除重复行，同时讲解了如何对合并后的查询结果进行排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL queries are combined using the UNION operator. Using UNION, multiple SELECT statements can be specified, and their results can be combined into a single result set.

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN ('IL','IN','MI')

UNION

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_name = 'Fun4All'

ORDER BY cust_name, cust_contact;

【Using UNION, the DBMS automatically removes any duplicate rows from the query result set .】

UNION instructs the DBMS to execute both SELECT statements and combine the output into a single query result set.

`UNION` Rules

As you can see, unions are very easy to use. But there are a few rules governing exactly which can be combined:

A UNION must be comprised of two or more SELECT statements, each separated by the keyword UNION (so, if combining four SELECT statements there would be three UNION keywords used).
Each query in a UNION must contain the same columns, expressions, or aggregate functions (although columns need not be listed in the same order).
Column datatypes must be compatible: They need not be the exact same type, but they must be of a type that the DBMS can implicitly convert (for example, different numeric types or different date types).

Including or Eliminating Duplicate Rows

Go back to the preceding section titled "Using UNION" and look at the sample SELECT statements used. You'll notice that when executed individually, the first SELECT statement returns three rows, and the second SELECT statement returns two rows. However, when the two SELECT statements are combined with a UNION, only four rows are returned, not five.

The UNION automatically removes any duplicate rows from the query result set (in other words, it behaves just as do multiple WHERE clause conditions in a single SELECT would). Because there is a Fun4All location in Indiana, that row was returned by both SELECT statements. When the UNION was used the duplicate row was eliminated.

This is the default behavior of UNION, but you can change this if you so desire. If you would, in fact, want all occurrences of all matches returned, you can use UNION ALL instead of UNION.

Look at the following example:

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN ('IL','IN','MI')

UNION ALL

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_name = 'Fun4All';

【Using UNION ALL, the DBMS does not eliminate duplicates.】

Sorting Combined Query Results

This UNION takes a single ORDER BY clause after the final SELECT statement,multiple ORDER BY clauses are not allowed. Even though the ORDER BY appears to only be a part of that last SELECT statement, the DBMS will in fact use it to sort all the results returned by all the SELECT statements.

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN ('IL','IN','MI')

UNION

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_name = 'Fun4All'

ORDER BY cust_name, cust_contact;