SQL must-know note the 14th Chapter combination Query

14.1 Combination Query

Most SQL queries contain only a single SELECT statement that is returned from one or more tables. However, SQL is allowed to execute multiple queries (multiple SELECT statements) and returns the result as a single query result set. These combination queries are often called and (union) or compliant queries (compound query).
There are two basic scenarios in which you need to use a combination query.
(1) Return structural data in a single query similar to a different table.
(2) Execute multiple queries against a single table, returning data as a single query.

14.2 Creating a combined query

The union operator can be used to combine several SQL queries. With union, multiple SELECT statements can be given, combining their results into a single result set.

14.2.1 using Union

The use of Union is simple. All you need to do is give each SELECT statement and place the keyword union between the statements.

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‘;

The union instructs the DBMS to execute two SELECT statements and combine the output into a single query result set.

14.2.2UNION rules

(1)Union must consist of two or more than two SELECT statements, separated by a keyword union.
(2)each query in union must contain the same column, expression, or aggregate function (although the columns do not need to be listed in the same order).
(3) column data types must be compatible: types do not have to be identical, but must be types that the DBMS can implicitly convert.

14.2.3 rows that contain or cancel duplicates

Union automatically removes duplicate rows from the query result set. This is the default behavior of Unio, but if you want, you can change it. 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‘;

Use union All,dbms to not cancel duplicate rows.

14.2.4 sorting the results of a combined query

When you combine queries with union, you can use only one ORDER BY clause, and he 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;

SQL must-know note the 14th Chapter combination Query