SQL: Composite query in Chapter 14th

SQL: Composite query in Chapter 14th
14.1 Combined Query

Most SQL queries only contain a single SELECT statement returned from one or more tables. However, SQL and allow execution of multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are generally called union or compound queries ).
There are two basic cases, in which combined query is required.
(1) return structure data from different tables in a single query.
(2) perform multiple queries on a single table and return data based on a single query.

14.2 create a Combined Query

You can use the UNION operator to combine several SQL queries. Using UNION, you can give multiple SELECT statements and combine their results into a single result set.

14.2.1 use UNION

UNION is easy to use. All you need to do is to give each SELECT statement and put the keyword UNION between each statement.

SELECT cust_name,cust_contact,cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNIONSELECT cust_name,cust_contact,cust_emailFROM CustomersWHERE cust_name = 'Fun4All';

UNION indicates that the DBMS executes two SELECT statements and combines the output into a single query result set.

14.2.2UNION rules

(1)UNION must be composed of two or more SELECT statements, which are separated by the keyword UNION.
(2)Each query in UNION must contain the same columns, expressions, or aggregate functions (but each column does not need to be listed in the same order ).
(3)Column data types must be compatible: The types must not be identical, but must be implicitly converted by DBMS.

14.2.3 include or cancel duplicate rows

UNION automatically removes duplicate rows from the query result set. This is the default behavior of UNIO, but you can change it if you want. In fact, if you want to return ALL matching rows, you can use union all instead of UNION.

SELECT cust_name,cust_contact,cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNION ALLSELECT cust_name,cust_contact,cust_emailFROM CustomersWHERE cust_name = 'Fun4All';

If union all is used, DBMS does not cancel duplicate rows.

14.2.4 sort the combined query results

Only one order by clause can be used for a UNION combination query. It must appear after the last SELECT statement.

SELECT cust_name,cust_contact,cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')UNION ALLSELECT cust_name,cust_contact,cust_emailFROM CustomersWHERE cust_name = 'Fun4All'ORDER BY cust_name,cust_contact;