SQL SET Operation

Spread the love

SET Theory –

In many parts of the world, basic set theory is included in elementary-level math curriculum. Perhaps you recall looking at something like what is shown below.

The shaded area in the above figure represents the union of sets A and B, which is the combination of the two sets (with any overlapping regions included only once). Is this starting to look familiar? If so, then you’ll finally get a chance to put that knowledge to use; if not, don’t worry, because it’s easy to visualize using a couple of diagrams.

Using circles to represent two data sets (A and B), imagine a subset of data that is common to both sets; this common data is represented by the overlapping area shown in the above figure. Since set theory is rather uninteresting without an overlap between data sets, I use the same diagram to illustrate each set operation. There is another set operation that is concerned only with the overlap between two data sets; this operation is known as the intersection and is demonstrated in the below figure.

The data set generated by the intersection of sets A and B is just the area of overlap between the two sets. If the two sets have no overlap, then the intersection operation yields the empty set.

The third and final set operation, which is demonstrated in the below figure is known as the except operation.

This figure shows the results of A except B, which is the whole of set A minus any overlap with set B. If the two sets have no overlap, then the operation A except B yields the whole of set A.

Using these three operations, or by combining different operations together, you can generate whatever results you need. For example, imagine that you want to build a set demonstrated below.

The data set you are looking for includes all of sets A and B without the overlapping region. You can’t achieve this outcome with just one of the three operations shown earlier; instead, you will need to first build a data set that encompasses all of sets A and B, and then utilize a second operation to remove the overlapping region. If the combined set is described as A union B, and the overlapping region is described as A intersect B, then the operation needed to generate the data set represented by above figure will look as follows.

(A union B) except (A intersect B)

Of course, there are often multiple ways to achieve the same results; you could reach a similar outcome using the following operation:

(A except B) union (B except A)

While these concepts are fairly easy to understand using diagrams, the next sections show you how these concepts are applied to a relational database using the SQL set operators.

SET Theory in Practice –

The circles used in the previous section’s diagrams to represent data sets don’t convey anything about what the data sets comprise. When dealing with actual data, however, there is a need to describe the composition of the data sets involved if they are to be combined. Imagine, for example, what would happen if you tried to generate the union of the customer table and the city table, whose definitions are as follows:

desc customer;
desc city;

When combined, the first column in the result set would include both the customer.customer_id and city.city_id columns, the second column would be the combination of the customer.store_id and city.city columns, and so forth. While some column pairs are easy to combine (e.g., two numeric columns), it is unclear how other column pairs should be combined, such as a numeric column with a string column or a string column with a date column. Additionally, the fifth through ninth columns of the combined tables would include data from only the customer table’s fifth through ninth columns, since the city table has only four columns. Clearly, there needs to be some commonality between two data sets that you wish to combine.

Therefore, when performing set operations on two data sets, the following guidelines must apply:

Both data sets must have the same number of columns.

The data types of each column across the two data sets must be the same (or the server must be able to convert one to the other).

With these rules in place, it is easier to envision what “overlapping data” means in practice; each column pair from the two sets being combined must contain the same string, number, or date for rows in the two tables to be considered the same. You perform a set operation by placing a set operator between two select statements, as demonstrated by the following:

SELECT 1 num, 'abc' str
UNION
SELECT 9 num, 'xyz' str;

Each of the individual queries yields a data set consisting of a single row having a numeric column and a string column. The set operator, which in this case is union, tells the database server to combine all rows from the two sets. Thus, the final set includes two rows of two columns. This query is known as a compound query because it comprises multiple, otherwise-independent queries. As you will see later, compound queries may include more than two queries if multiple set operations are needed to attain the final results.

Set Operators –

The SQL language includes three set operators that allow you to perform each of the various set operations described earlier in this post. Additionally, each set operator has two flavors, one that includes duplicates and another that removes duplicates (but not necessarily all of the duplicates). The following subsections define each operator and demonstrate how they are used.

The Union Operator –

The union and union all operators allow you to combine multiple data sets. The difference between the two is that union sorts the combined set and removes duplicates, whereas union all does not. With union all, the number of rows in the final data set will always equal the sum of the number of rows in the sets being combined. This operation is the simplest set operation to perform (from the server’s point of view), since there is no need for the server to check for overlapping data. The following example demonstrates how you can use the union all operator to generate a set of first and last names from multiple tables:

We will use this database for all our examples – sakila

SELECT 'CUST' typ, c.first_name, c.last_name
FROM customer c 
UNION ALL 
SELECT 'ACTR' typ, a.first_name, a.last_name
FROM actor a;

The query returns 799 names, with 599 rows coming from the customer table and the other 200 coming from the actor table. The first column, which has the alias  typ, is not necessary, but was added to show the source of each name returned by the query.

While you are unlikely to repeat the same query twice in a compound query, here is another compound query that returns duplicate data:

SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
UNION ALL 
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

Both queries return the names of people having the initials JD. Of the five rows in the result set, one of them is a duplicate (Jennifer Davis). If you would like your combined table to exclude duplicate rows, you need to use the union operator instead of union all:

SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
UNION 
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

For this version of the query, only the four distinct names are included in the result set, rather than the five rows returned when using union all.

The Intersect Operator –

If the two queries in a compound query return non overlapping data sets, then the intersection will be an empty set. Consider the following query:

SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'D%' AND c.last_name LIKE 'T%'
INTERSECT
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'D%' AND a.last_name LIKE 'T%';

While there are both actors and customers having the initials DT, these sets are completely non overlapping, so the intersection of the two sets yields the empty set. If we switch back to the initials JD, however, the intersection will yield a single row: Unfortunately version 8.0 of MYSQL does not implement the INTERSECT operator, so I can’t show you the results.

The Except Operator –

The ANSI SQL specification includes the except operator for performing the except operation. Once again, version 8.0 of MySQL does not implement the except operator.

The except operator returns the first result set minus any overlap with the second result set. Here is an example.

SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
EXCEPT
SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

SET operation Rules –

The following sections outline some rules that you must follow when working with compound queries.

Sorting compound query results –

If you want the results of your compound query to be sorted, you can add an order by clause after the last query. When specifying column names in the order by clause, you will need to choose from the column names in the first query of the compound query. Frequently, the column names are the same for both queries in a compound query, but this does not need to be the case, as demonstrated by the following:

SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL 
SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
ORDER BY lname, fname;

The column names specified in the two queries are different in this example. If you specify a column name from the second query in your order by clause, you will see the following error:

SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL 
SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
ORDER BY last_name, first_name;

I recommend giving the columns in both queries identical column aliases in order to avoid this issue.

Set Operation Precedence –

If your compound query contains more than two queries using different set operators, you need to think about the order in which to place the queries in your compound statement to achieve the desired results. Consider the following three-query compound statement:

SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL 
SELECT a.first_name, a.last_name
FROM actor a 
WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
UNION
SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

This compound query includes three queries that return sets of non unique names; the first and second queries are separated with the union all operator, while the second and third queries are separated with the union operator. While it might not seem to make much difference where the union and union all operators are placed, it does, in fact, make a difference. Here’s the same compound query with the set operators reversed:

SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION 
SELECT a.first_name, a.last_name
FROM actor a 
WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
UNION ALL 
SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

Looking at the results, it’s obvious that it does make a difference how the compound query is arranged when using different set operators. In general, compound queries containing three or more queries are evaluated in order from top to bottom, but with the following caveats:

The ANSI SQL specification calls for the intersect operator to have precedence over the other set operators.

You may dictate the order in which queries are combined by enclosing multiple queries in parentheses.

MySQL does not yet allow parentheses in compound queries, but if you are using a different database server, you can wrap adjoining queries in parentheses to override the default top-to-bottom processing of compound queries.

SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION 
(SELECT a.first_name, a.last_name
FROM actor a 
WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
UNION ALL 
SELECT c.first_name, c.last_name
FROM customer c 
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
);

For this compound query, the second and third queries would be combined using the union all operator, then the results would be combined with the first query using the union operator.

Rating: 1 out of 5.

Leave a Reply