Defining Datasets from Existing Datasets

In previous chapters, you have learned how to write extraction queries against a single table. In real-world usage scenarios, the data you are interested in needs multiple layers of preprocessing before you can present it, or is spread across multiple tables. For example, you may only be interested in active customers who made purchases in the past three years, or you would like to know the addresses of both customers and dealerships. In the former case, a common request is to reuse the query that preprocesses the data, either within the same statement or across different statements. In the latter case, you need to bring related tables together.

SQL provides multiple approaches to both requests. To reuse queries, you can use a subquery, a common table expression (CTE), or a view. To utilize data from multiple tables, you can either join these tables if you need to merge the columns of records from different tables, or union these tables...