UNION / Compound Queries

There are basically 2 scenarios in which you would use combined queries:

  1. To return similarly structured data from different tables in a single query
  2. To perform multiple queries against a single table returning the data as one query

MySQL enable you to perform multiple queries (multiple SELECT statements) and return the results as a single query result set. For the most part, combining two queries to the same table accomplish the same thing as a single query with multiple WHERE clauses. In other words, any SELECT statement with multiple WHERE clauses can also be specified as a combined query.

SQL queries are combined using the UNION operator. Using UNION is simple enough. All you do is specifying each SELECT statement and place the keyword UNION between each.

SELECT vendorID, productID, price FROM products WHERE price <= 5
SELECT vendorID, productID, price FROM products WHERE vendorID IN (1001,1002);

is equivalent to:

SELECT vendorID, productID, price FROM products WHERE price <= 5 OR vendID IN (1001,1002);

In this simple example, the UNION might actually be more complicated than using a WHERE clause. But in a more complex filtering conditions, or if the data is being retrieved from multiple tables (not just a single table), the UNION can make the process simpler.

The UNION automatically remove any duplicate rows from the query result set. If you want all occurrences of all matches returned, you can use UNION ALL.

When combining queries with a UNION, only one ORDER BY clause may be used, and it must occur after the final SELECT statement.

By default, UNION eliminates duplicate rows from the result set. To retains all rows, replace UNION with UNION ALL. UNION ALL is more efficient for the server to process because it need not perform duplicate removal. However, returning the result set to the client involves more network traffic.

ORDER BY and LIMIT clauses can be used to sort and limit a UNION result set as a whole. To do this, surround each SELECT with parentheses and then add ORDER BY or LIMIT after the last parenthesis:

(SELECT subscriber, email FROM list1)
UNION (SELECT name, address FROM list2)
UNION (SELECT real_name, email FROM list3)
ORDER BY email LIMIT 10;

Columns named in such an ORDER BY clause should refer to columns in the first SELECT of the statement because the first SELECT determines the column names for the result set.

ORDER BY and LIMIT clauses can also be applied to individual SELECT within a UNION. Surround each SELECT with parentheses and add ORDER BY or LIMIT to the end of appropriate SELECT. In this case, an ORDER BY should refer to columns of the particular SELECT. Although LIMIT may be used by itself in this context, ORDER BY has no effect unless combined with LIMIT. The optimizer ignores it otherwise. (If ORDER BY is used by itself, the optimizer ignore it.)

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License