In the past few chapters, we examined ways to join tables based on the way they relate to one another. Every query we’ve written has had a single SELECT statement. But this chapter will show how to write a query with multiple SELECT statements and combine the results into a single set of data.
This technique can be useful when we need to evaluate results that require different conditions, such as querying values in different tables with no key to join them. Although we’ve seen that null values are excluded from results when we use joins, we’ll see how to use SQL to include null values if those values exist in two data sets and we want to include them in our results.
10.1 Using set operators
We’ve written a lot of queries that start with SELECT, and each resulted in a single result set. That’s what SELECT queries do: produce a set of results. More specifically, they produce a set of rows that meet the various conditions of our queries.
At times, though, we want to combine or evaluate two or more result sets, and to do this, we need to use special keywords known as set operators. Though SQL doesn’t have many set operators, all of them use the same syntax to evaluate two result sets:
SELECT <some column>, <another column> FROM <some table> WHERE <some condition> <set operator> SELECT <some column>, <another column> FROM <some table> WHERE <another condition>;