SQL Union Set Operation

What is the SQL UNION set operation?

The SQL UNION set operation combines the results of two separate queries into a single result that contains all rows from both queries. Visualized by a Venn diagram, the UNION set operation looks like:

UNION

Notice that every section of the circles (representing the results of separate queries) is shaded. This means that all rows of the each query are returned as part of the results. Let’s take a look at an example. Suppose we have two queries: one that returns the ProductID and Name column from Production.Product for all rows where ProductID is greater than 100 and another returning the same columns but for all rows where the ProductID is less than or equal to 100. Let’s apply the SQL UNION operation to them and see what the results look like:

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID > 100

UNION

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID <= 100

The two queries, which you can run separately to look at the results, combine together and all rows from each query are returned.

What happens if some rows appear in both queries of the SQL UNION set operation? Let’s take a look and see what happens. Modify the previous example so that the second query looks at all rows where the ProductID is less than or equal to 400. Now the full query becomes:

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID > 100

UNION

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID <= 400

If you run the queries separately, there are some rows that are in both queries. In fact, all rows with a ProductID between 101 and 400 are contained in both queries. However, when the SQL UNION operation is evaluated, only one row is presented in the results. No duplicate rows appear. This is because the UNION operation, by default, selects DISTINCT rows and outputs them to the results panel. There is a feature, the SQL UNION ALL set operation, that returns all rows regardless of if they are duplicates.

Consider our complete SQL for Beginners Course to learn more about UNION set operations.

2 replies

Trackbacks & Pingbacks

  1. […] SQL EXCEPT set operation is another set operation like the UNION or INTERSECT operations. This operation, however, returns all rows from the first query except […]

  2. […] the distinct rows from two or more queries that appear in each query’s results. Unlike the SQL UNION set operation that returns all rows from both queries, the SQL INTERSECT operation only returns the rows that […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *