websql

17 Combining Queries

Learning Objectives

:

Describe when and why to use combined queries.

Combine two or more SELECT statements using the UNION operator.

Describe the rules that govern which SELECT statements can be combined with UNION.

Use UNION ALL to include duplicate rows in a combined result.

Sort the results of a combined query.

17.1 Understanding Combined Queries

Most SQL queries contain a single SELECT statement that returns data from one or more tables. SQL also enables you to perform multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are usually known as unions or compound queries.

There are basically two scenarios in which you'd use combined queries:

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

Combining Queries and Multiple WHERE Conditions. For the most part, combining two queries to the same table accomplishes the same thing as a single query with multiple WHERE clause conditions. In other words, any SELECT statement with multiple WHERE clauses can also be specified as a combined query, as you'll see in the section that follows.

17.2 Creating Combined Queries

SQL queries are combined using the UNION operator. Using UNION, you can specify multiple SELECT statements, and their results can be combined into a single result set.

17.3 Using UNION

Using UNION is simple enough. All you do is specify each SELECT statement and place the keyword UNION between each. Let's look at an example. You need a report on all your French, Italian, and Spanish artists. You also want to include all Impressionist artists, regardless of nationality. Of course, you can create a WHERE clause that will do this, but this time you'll use a UNION instead.

As just explained, creating a UNION involves writing multiple SELECT statements. First, look at the individual statements:

SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish');

SELECT full_name, nationality, style
FROM artist
WHERE style = 'Impressionist';

The first SELECT retrieves all rows for artists of French, Italian, or Spanish nationality by passing those values to the IN clause. The second SELECT uses a simple equality test to find all artists working in the Impressionist style. You'll notice that an artist like Claude Monet — French, and working in the Impressionist style — appears on both outputs, since he meets both WHERE conditions.

To combine these two statements, you can use a chat with an AI assistant to write the SELECT statement:

Write a SQL statement that combines two queries into one result set using UNION: one that lists French, Italian, and Spanish artists, and one that lists Impressionist artists.send

You place the keyword UNION between the two SELECT statements. Here's the SQL statement:
SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish')
UNION
SELECT full_name, nationality, style
FROM artist
WHERE style = 'Impressionist';
UNION instructs the DBMS to run both SELECT statements and combine their output into a single result set, automatically removing any duplicate rows — so an artist who matches both conditions, like a French Impressionist, will only appear once.

Try the SQL statement.

SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish')
UNION
SELECT full_name, nationality, style
FROM artist
WHERE style = 'Impressionist';

The preceding statement is made up of both of the previous SELECT statements separated by the UNION keyword. UNION instructs the DBMS to execute both SELECT statements and combine the output into a single query result set.

As a point of reference, here is the same query using a single WHERE clause with an OR condition instead of a UNION:

SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish') OR style = 'Impressionist';

In our simple example, the UNION might actually be more complicated than using a WHERE clause. But with more complex filtering conditions, or if the data is being retrieved from multiple tables (and not just a single table), the UNION could have made the process much simpler indeed.

UNION Limits. There is no standard SQL limit to the number of SELECT statements that can be combined with UNION statements. However, it is best to consult your DBMS documentation to ensure that it does not enforce any maximum statement restrictions of its own.

Performance Issues. Most good DBMSs use an internal query optimizer to combine the SELECT statements before they are even processed. In theory, this means that from a performance perspective, there should be no real difference between using multiple WHERE clause conditions or a UNION. In practice, though, most query optimizers don't always do as good a job as they should. Your best bet is to test both methods to see which will work best for you.

17.4 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 composed of two or more SELECT statements, each separated by the keyword UNION (so, if you're combining four SELECT statements, you would use three UNION keywords).
Each query in a UNION must contain the same columns, expressions, or aggregate functions (and some DBMSs even require that columns be listed in the same order).
Column datatypes must be compatible. They need not be the same name or 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).

UNION Column Names. If SELECT statements that are combined with a UNION have different column names, what name is actually returned? For example, if one statement contained SELECT full_name and the next used SELECT artist_name, what would be the name of the combined returned column?

The answer is that the first name is used, so in our example the combined column would be named full_name, even though the second SELECT used a different name. This also means that you can use an alias on the first name to set the returned column name as needed.

This behavior has another interesting side effect. Because the first set of column names are used, only those names can be specified when sorting. Again, in our example, you could use ORDER BY full_name to sort the combined results, but ORDER BY artist_name would display an error message because there is no column artist_name in the combined results.

Aside from these basic rules and restrictions, unions can be used for any data retrieval tasks.

17.5 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 multiple WHERE clause conditions in a single SELECT would). Because Claude Monet is both French and Impressionist, 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 it if you so desire. If you would, in fact, want all occurrences of all matches returned, you could use UNION ALL instead of UNION.

Look at the following example:

SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish')
UNION ALL
SELECT full_name, nationality, style
FROM artist
WHERE style = 'Impressionist';

When you use UNION ALL, the DBMS does not eliminate duplicates. Therefore, the preceding example returns five rows, one of them (Claude Monet) occurring twice.

UNION Versus WHERE. At the beginning of this lesson, we said that UNION almost always accomplishes the same thing as multiple WHERE conditions. UNION ALL is the form of UNION that accomplishes what cannot be done with WHERE clauses. If you do, in fact, want all occurrences of matches for every condition (including duplicates), you must use UNION ALL and not WHERE.

17.6 Sorting Combined Query Results

SELECT statement output is sorted using the ORDER BY clause. When combining queries with a UNION, you may use only one ORDER BY clause, and it must occur after the final SELECT statement. There is very little point in sorting part of a result set one way and part another way, and so multiple ORDER BY clauses are not allowed.

The following example sorts the results returned by the previously used UNION:

Adjust the previous UNION query so the combined results are sorted by full_name and then nationality.send

You add a single ORDER BY clause after the final SELECT statement — it will apply to the combined result set as a whole. Here's the SQL statement:
SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish')
UNION
SELECT full_name, nationality, style
FROM artist
WHERE style = 'Impressionist'
ORDER BY full_name, nationality;
Even though the ORDER BY clause appears to belong only to the last SELECT statement, the DBMS uses it to sort all of the rows returned by the entire UNION.

Try the SQL statement.

SELECT full_name, nationality, style
FROM artist
WHERE nationality IN ('French', 'Italian', 'Spanish')
UNION
SELECT full_name, nationality, style
FROM artist
WHERE style = 'Impressionist'
ORDER BY full_name, nationality;

This UNION takes a single ORDER BY clause after the final SELECT statement. Even though the ORDER BY appears to be a part of only that last SELECT statement, the DBMS will in fact use it to sort all the results returned by all the SELECT statements.

Other UNION Types. PostgreSQL supports two additional types of UNION. EXCEPT can be used to retrieve only the rows that exist in the first query but not in the second (some other DBMSs, notably Oracle, call this same operation MINUS instead — PostgreSQL uses EXCEPT), and INTERSECT can be used to retrieve only the rows that exist in both queries. In practice, however, these UNION types are rarely used because the same results can be accomplished using joins.

Working with Multiple Tables. For simplicity's sake, the examples in this lesson have all used UNION to combine multiple queries on the same table. In practice, UNION is really useful when you need to combine data from multiple tables, even tables with mismatched column names, in which case you can combine UNION with aliases to retrieve a single set of results.

17.7 Summary

In this lesson, you learned how to combine SELECT statements with the UNION operator. Using UNION, you can return the results of multiple queries as one combined query, either including or excluding duplicates. The use of UNION can greatly simplify complex WHERE clauses and retrieval of data from multiple tables.