Understanding Combined Queries

Mosy SQL queries contain just one SELECT statement that can return data from one table, or several tables using a join. The technique of combining two or more independent queries into a single data set is usually known as a union or a compound query.

You might want to use this technique to retrieve records from two tables that have a similar structure in a single query. For instance, suppose you have archived off some data so that you have a customers table that contains your current customers and another table called old_customers.

These tables would have the same structureor at least would share many common columns if new columns had been added to the customers table since the archive took place. Therefore, you could perform a query on this table that takes into account both current and archived customers. This would look something like the following: 

SELECT name, telephone, email FROM customers UNION SELECT name, telephone, email FROM old_customers;

You can also use UNION to perform two different queries on the same table and combine the two results into a single data set.

The UNION Operator

The UNION operator is placed between two SQL queries to return a single result. A UNION is possible only if both queries return the same number of columns; otherwise, MariaDB produces an error.

The following simple example demonstrates how the UNION operator works by combining two separate queries on the customer_contacts table: 

MariaDB> SELECT first_name, last_name
 -> FROM customer_contacts
 -> WHERE customer_code = 'SCICORP'
 -> UNION
 -> SELECT first_name, last_name
 -> FROM customer_contacts
 -> WHERE customer_code = 'PRESINC';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Albert | Einstein |
| Charles | Darwin |
| Marie | Curie |
| Benjamin | Franklin |
| Abraham | Lincoln |
| Richard | Nixon |
| Franklin | Roosevelt |
| Theodore | Roosevelt |
+------------+-----------+
8 rows in set (0.01 sec)

All the rows from the first query are returned first, followed by all the rows from the second query.

Terminating Queries

When you execute a UNION using mysql, the terminating semicolon must only appear once, after the final query.

As you might have noticed, the previous example produced the same result that you can achieve using a single SELECT and an IN operator in the WHERE clause to select multiple values for customer_code.

The following example produces a result that cannot be accomplished any other way. Two different columns from the same table are selected in two different queries and combined using the UNION operator. 

MariaDB> SELECT first_name
 -> FROM customer_contacts
 -> UNION
 -> SELECT last_name
 -> FROM customer_contacts;
+------------+
| first_name |
+------------+
| Abraham |
| Richard |
| Franklin |
...
| Curie |
| Gershwin |
| Britten |
| Lennon |
+------------+
19 rows in set (0.00 sec)

The output is abbreviated to save space, but the full output contains every first_name and last_name value from the customer_contacts table, returned in a single column.

The UNION ALL Operator

Note that the total number of records returned by the previous query is 19. This might seem peculiar because you are selecting two values from each table rowyou might expect the query to return an even number of values.

However, although the customer_contacts table contains 11 total rows, 3 of the values were duplicated; by default, the UNION operator returns only unique values. The duplicate names in this UNION are Benjamin (which appears as a first_name twice), Roosevelt (which appears as a last_name twice), and Franklin (which appears once as a first_name and once as a last_name).

If you want to ensure that every value in a UNION is returned, use the UNION ALL operator. This example would then look like the following: 

MariaDB> SELECT first_name
 -> FROM customer_contacts
 -> UNION ALL
 -> SELECT last_name
 -> FROM customer_contacts;
+------------+
| first_name |
+------------+
| Abraham |
| Richard |
| Franklin |
...
| Franklin |
| Gershwin |
| Britten |
| Lennon |
+------------+
22 rows in set (0.00 sec)

Sorting a UNION

Notice that in the previous example, the column heading is first_name, even though half the values returned were fetched from the last_name column. The column headings on UNION are taken from the column names or aliases in the first query only.

Therefore, to specify a sort order on a UNION, you must use the column name from the first query in the ORDER BY clause. The following is an example: 

MariaDB> SELECT first_name
 -> FROM customer_contacts
 -> UNION
 -> SELECT last_name
 -> FROM customer_contacts
 -> ORDER BY first_name;
+------------+
| first_name |
+------------+
| Abraham |
| Albert |
| Benjamin |
... 
| Richard |
| Roosevelt |
| Theodore |
+------------+
19 rows in set (0.02 sec)

It is important to realize that even though the ORDER BY clause appears to relate to the last query in the UNION, it actually applies to the entire result set. The individual SELECT statements are executed, then combined, and finally sorted using the ORDER BY rule.

Ordering

You cannot specify a sort order on individual queries in a UNION. MariaDB will give an error if ORDER BY appears anywhere other than after the last SELECT statement.

Previous Page Next Page