SQL-Lesson 12. Combined queries (UNION)

Most SQL queries use a single statement that returns data from one or more tables. SQL also allows you to run multiple separate queries at the same time and display the result as a single data set. Such combined queries are usually called joins or complex queries.

1. Using the UNION operator

SQL queries are combined using the UNION operator. To do this, you need to specify each request SELECT and place the keyword UNION between them. There is no limit to the number of uses of the UNION operator in one general query. In the previous section, we noted that MS Access does not have the ability to create a full outer union, now we will see how this can be achieved through the UNION operator.

Run SQLSELECT *
FROM Sumproduct 
LEFT JOIN Sellers2 
ON Sumproduct.City = Sellers2.City
UNION
SELECT * 
FROM Sumproduct 
RIGHT JOIN Sellers2 
ON Sumproduct.City = Sellers2.City

We see that the query displayed both all columns from the first table and from the second, regardless of whether all records have counterparts in another table.

It is also worth noting that in many cases, instead of UNION, we can use the WHERE clause with many conditions, and get a similar result. However, through UNION, the records look more concise and clear.

It is also necessary to follow certain rules when writing combined queries:

the UNION request must include two or more SELECT operators separated by the UNION keyword (that is, if four SELECT operators are used in the request, then there must be three UNION keywords);

each query in the UNION operator must have the same columns, expressions, or statistical functions, which, moreover, must be listed in the same order;

column data types must be compatible. They do not necessarily have to be of the same type, but they must have a similar type so that the DBMS can unambiguously convert them (for example, they can be different numeric data types or different date types).

2. Include or exclude repeated lines

A query with UNION automatically removes all duplicate rows from the query result set (in other words, behaves like a WHERE clause with multiple conditions in a single SELECT statement). This is the default behavior of the UNION operator, but we can change it if we want. For this we should use the operator UNION ALL instead of UNION.

3. Sorting the results of combined queries

The results of the SELECT statement are sorted using the ORDER BY statement. When combining queries using UNION, only one ORDER BY clause can be used, and it must be placed in the last SELECT statement. Indeed, in practice, it does not make much sense to sort part of the results in one order, and the other part in another. Therefore, multiple sentences ORDER BY are not allowed.