---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Learning Objectives", "text":"After completing this lesson, the successful student will be able to:

1. Explain why multiple SELECT queries might be combined.
2. Us the UNION operator to combine multiple SELECT statements into a single result.

" ---[SIDEBAR]--- ## Why Combine Queries? Most of the SELECT queries described in this resource have all contained a single SELECT statement. Some of these queries hav ebeen relatively complex drawing data from multiple tables or using sophistocated logic to restrict the records that are retrieved. In ---[REF]---"style":"link","pageId":"lesson-subqueries"---[REF]---, the idea of a subquery was presented. As the name implies, a subquery is a SQL statement that contains another SQL ststement inside of it. A subquery is used to help specify an element of the main query. For example, a subquery in the WHERE clause of the main query might be used to dynamically generate the values used in a logical test. Despite differences in the features SQL described, all of the SELECT queries presented in this resource have had one thing in common: they produce a single set of results. It is possible to write a query that combines multiple SELECT statements (that would independently produce separate results sets) into a statement with a combined results set. These combined queries are called UNION queries. ---[SIDEBAR]--- "type":"term", "title":"UNION", "text":"An operator used in a SELECT statement to combine two or more queries into a single compound query." ---[SIDEBAR]--- The UNION operator can be used to help simplify the syntax of a more complex SQL statement with multiple logical tests in the WHERE clause. The UNION operator can be used in two situations: 1. To combine similar data from queries of different tables 2. To comine the results of multiple queries of the same table into a single result. ---[SIDEBAR]--- "type":"note", "title":"Using UNION queries", "text":"Generally, the results produced by a UNION query can also be achieved using a single query with multiple logical test conditions in the WHERE clause. The opposite is also generally true, UNION queries can be used in the place of a single SELECT statement with multiple WHERE clause conditions." ---[SIDEBAR]--- ## Creating Combined Queries using the UNION Operator The syntax for combining two or more SELECT queries using the UNION operator is straightforward. You first create the queries you wish to combine and then you combine them using the keyword UNION. For example, you may wish to combine two queries of the museum table. The first query returns the museums (name and city) in the United States. The second retrieves the museums (name and city) with the work "National" in the name. The independent queries would be writen as: SELECT name, city FROM museum WHERE country = 'USA'; and SELECT name, city FROM museum WHERE name like '%National%'; **Try the SQL statement.** ---[SQL]--- connection:SQL-Book Queries schema:canvas diagram:canvas owner:atlas-query result:museum-name-USA ---[SQL]--- SELECT name, city FROM museum WHERE country = 'USA'; ---[SQL]--- ---[SQL]--- connection:SQL-Book Queries schema:canvas diagram:canvas owner:atlas-query result:museum-name-national ---[SQL]--- SELECT name, city FROM museum WHERE name like '%National%'; ---[SQL]--- The first query retrieves the 25 museums in the United States. The second returns the eight museums with the word 'National' in the name. Note that one museum, The National Gallery of Art, located in Washington, appears in both results sets.

You can use a chat with ^ai_name^ to create the SQL statement that will combine these queries into a single SQL statement: ---[AI]--- [ { "prompt": "Use the UNION operator to combine two queries. The first query should retrieve the name and city of each museum in the USA. The second should retrieve the name and city for each museum with the word National in the name.", "pretext": "Certainly! You can use the UNION operator to combine the results of two queries. Here's an example SQL query that accomplishes what you described:", "response": "SELECT name, city \nFROM museum \nWHERE country = 'United States' \nUNION \nSELECT name, city \nFROM museum \nWHERE name LIKE '%National%';", "posttext": "This query consists of two parts separated by the UNION operator. The first part retrieves the name and city of each museum in the USA, and the second part retrieves the name and city for each museum with the word 'National' in the name. The UNION operator combines the results of both queries, removing duplicate rows from the final result set." } ] ---[AI]--- ---[SIDEBAR]--- "type":"note", "title":"Prompt Engineering", "text":"Notice the precise wording of the prompt provided to ^ai_name^. Because UNION queries can typically be written as a single SELECT statement with multiple criteria in the WHERE clause, the statements generated by artificial intellegence tools may not use a UNION unless you specify that you want to use one." ---[SIDEBAR]---

To combine these queries into a single SQL statement, the UNION operator would be used: SELECT name, city FROM museum WHERE country = 'USA' UNION SELECT name, city FROM museum WHERE name like '%National%';

**Try the SQL statement.** ---[SQL]--- connection:SQL-Book Queries schema:canvas diagram:canvas owner:atlas-query result:museum-name-usa-national-combined ---[SQL]--- SELECT name, city FROM museum WHERE country = 'USA' UNION SELECT name, city FROM museum WHERE name like '%National%'; ---[SQL]--- There are a couple of important things to note about this combined query. First, notice the query results. The rows of the two independent queries are intermixed into one set. The museums from the United States appear along side the museums from other countries, even though the results where generated with separate SELECT statements. Second, the structure of the combined queries is the same. In this case, both queried retrieve the name and city fields from the museum table. There are a couple of rules for the structure of queries combined with the UNION operator. 1. The combined queries must have the same number of columns. 2. The columns must be in the same order and the same or compatible data types. ---[SIDEBAR]--- "type":"note", "title":"Columns in a Combined Query", "text":"The columns of combined queries don't necessarily need to have the same name. If the corresponding columns of combined queries have different names, the column name in first query will be displayed at the top of the query result." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"caution", "title":"The Number of Queries Combined using Unions", "text":"There is technically not a limit to the number of SELECT statements that can be combined using the UNION operator. Some DBMS will limit the number of queries combined using UNION. You will want to consult the documentation of the DBMS to determine constraints on the queries you wish to create." ---[SIDEBAR]--- ## Including or Eliminating Duplicate Rows Notice that the combined query in the last example omits duplicate results. The query that retrieved the museums in the United States returned 25 rows. The second query retrieved 8. You might expect that the combined query would return 33 rows, but it retrieves 32. This happens because the original queries both contained the National Gallery of Art in Washington. The combined results contain this museum only once. By default, UNION will automatically remove duplicate rows from the query result. You can design the query to show all rows. This is done using the UNION ALL operator.

You can use a chat with ^ai_name^ to create the SQL statement: ---[AI]--- [ { "prompt": "Update the last query to show all rows in the combined results.", "pretext": "Certainly! If you want to include all rows in the combined result set, including duplicates, you can use the UNION ALL operator instead of UNION. Here's the updated query:", "response": "SELECT name, city \nFROM museum \nWHERE country = 'United States' \nUNION ALL \nSELECT name, city \nFROM museum \nWHERE name LIKE '%National%';", "posttext": "Using UNION ALL will include all rows from both queries in the result set, even if there are duplicates. If you want to eliminate duplicate rows, you can stick with UNION." } ] ---[AI]---

To show all rows in the previous example, you would use this syntax: SELECT name, city FROM museum WHERE country = 'USA' UNION ALL SELECT name, city FROM museum WHERE name like '%National%';

**Try the SQL statement.** ---[SQL]--- connection:SQL-Book Queries schema:canvas diagram:canvas owner:atlas-query result:museum-name-usa-national-combined-duplicates ---[SQL]--- SELECT name, city FROM museum WHERE country = 'USA' UNION ALL SELECT name, city FROM museum WHERE name like '%National%'; ---[SQL]--- The results set retrieved by this SELECT statement includes 33 rows. This includes the duplicated National Gallery of Art in Washington that appears in both query results. ## Sorting Combined Query Results You can use the ORDER BY clause to sort the combined results of UNION queries. Since a combined query can contain only one ORDER BY clause, it must appear after the final SELECT statement. For example, you may wish to sort the results of the example query used in this lesson by city in alphabetical order.

You can use a chat with ^ai_name^ to create the SQL statement: ---[AI]--- [ { "prompt": "Use the UNION operator to combine two queries. The first query should retrieve the name and city of each museum in the united states. The second should retrieve the name and city for each museum with the word National in the name. Sort the results by city in alphabetical order.", "pretext": "Certainly! You can use the ORDER BY clause to sort the combined result set by city in alphabetical order. Here's the updated SQL query:", "response": "SELECT name, city \nFROM museum \nWHERE country = 'United States' \nUNION ALL \nSELECT name, city \nFROM museum \nWHERE name LIKE '%National%' \nORDER BY city;", "posttext": "This query combines the two queries using the UNION operator and then orders the combined result set by the city column in alphabetical order using the ORDER BY clause." } ] ---[AI]---

The syntax for this query is: SELECT name, city FROM museum WHERE country = 'USA' UNION SELECT name, city FROM museum WHERE name like '%National%' ORDER BY city;

**Try the SQL statement.** ---[SQL]--- connection:SQL-Book Queries schema:canvas diagram:canvas owner:atlas-query result:museum-name-usa-national-combined-sorted ---[SQL]--- SELECT name, city FROM museum WHERE country = 'USA' UNION SELECT name, city FROM museum WHERE name like '%National%' ORDER BY city; ---[SQL]--- Notice that even though the ORDER BY clause is specified in the last query, it applies to the combined results. ## Combining Queries of Different Tables The UNION operator can also be used to combine SELECT statements that retrieve data from different tables. SELECT statements can be part of a combined query as long as the number of columns in each query is the same and the data types for each columns are compatible. Suppose you want to combine a query of the artist table with a query of the work table.

You can use a chat with ^ai_name^ to create the SQL statement: ---[AI]--- [ { "prompt": "Use the UNION operator to combine two queries. The first query should retrieve the full_name of each artist (renamed as name), the style of the artist, and a literal value 'artist' renamed source_table for all artists that have a value for style. The second query should retrieve the name of each work of art, style for each work of art, and the literal value 'work' for all work that have a value for style. Sort the results by style.", "pretext": "Certainly! Here's the SQL query that combines the two queries using the UNION operator and sorts the results by the style column:", "response": "SELECT full_name AS name, style, 'artist' AS source_table \nFROM artist \nWHERE style IS NOT NULL \nUNION \nSELECT name, style, 'work' AS source_table \nFROM work \nWHERE style IS NOT NULL \nORDER BY style;", "posttext": "In this query:

• The first part (SELECT full_name AS name, style, 'artist' AS source_table FROM artist WHERE style IS NOT NULL) retrieves the full_name of each artist, their style, and a literal value 'artist' as source_table for artists who have a value for style.
• The second part (SELECT name, style, 'work' AS source_table FROM work WHERE style IS NOT NULL) retrieves the name of each work of art, its style, and a literal value 'work' as source_table for works of art that have a value for style.

The UNION operator combines the results of both queries, and the ORDER BY style clause sorts the final result set by the style column." } ] ---[AI]---

An example of such a combined query is: SELECT full_name as name, style, 'artist' as source_table FROM artist WHERE style IS NOT NULL UNION SELECT name, style, 'work' FROM work WHERE style IS NOT NULL; ORDER BY style;

**Try the SQL statement.** ---[SQL]--- connection:SQL-Book Queries schema:canvas diagram:canvas owner:atlas-query result:artist-style-work-style-combined ---[SQL]--- SELECT full_name as name, style, 'artist' as source_table FROM artist WHERE style IS NOT NULL UNION SELECT name, style, 'work' FROM work WHERE style IS NOT NULL ORDER BY style; ---[SQL]--- This example demonstates a few aspects of combined queries. First, notice that aliases are used to rename columns in the first query. The aliases are used as the column headings for the combined results. Second, both queries include a WHERE clause. Combined queries can use any of the clauses found in a single SELECT statement. Two notable exceptions to this are that only the last query in a combined query can include an ORDER BY clause and only the column names specified in the first query will be used in the combined results. Third, the queries retrieve rows from different tables. This works because the queries have the same number of columns and the relative columns in each of the tables have compatible data types. The first column each query has the name of the artist or work, respectively. The second column has data about the style of the artist or work. The last column contains either the text 'artist' or 'work'. ---[SIDEBAR]--- "type":"tip", "title":"Label Values in Combined Queries", "text":"The use of the words 'artist' and 'work' in the example demonstrate how using literals as labels can be helpful with combined queries. In the first query, the word 'artist' is included for each row. In the second, each row will contain the word 'work'. When the two queries are combined, it is easy to determine which of the rows in the combined result come from the artist table and which come from the work table." ---[SIDEBAR]--- ## Summary This lesson described how to combine multiple SELECT statements using the UNION operator. Queries can be combined either to show rows that are duplicated in the original queries or to show only the unique set of results. The original queries can contain any of the clauses and SQL features discussed elsewhere in this resource. The combined results can also be combined using an ORDER BY clause in the last combined query.