[Teach Youself SQL in 10 Minutes] combining queries

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;