SQL for Data Scientists

CHAPTER 11More Advanced Query Structures

Most of this book is targeted at beginners, but because beginners can quickly become more advanced in developing SQL, I wanted to give you some ideas of what is possible when you think a little more creatively and go beyond the simplest SELECT statements. SQL is a powerful way to shape and summarize data into a wide variety forms that can be used for many types of analyses. This chapter includes a few examples of more complex query structures.

UNIONs

One query structure that I haven't yet covered in this book, but which certainly deserves a mention, is the UNION query. Using a UNION , you can combine any two queries that result in the same number of columns with the same data types. The columns must be in the same order in both queries. There are many possible use cases for UNION queries, but the syntax is simple: write two queries with the same number and type of fields, and put a UNION keyword between them:

SELECT market_year, MIN(market_date) AS first_market_date
FROM farmers_market.market_date_info
WHERE market_year = '2019'
 
UNION
 
SELECT market_year, MIN(market_date) AS first_market_date
FROM farmers_market.market_date_info
WHERE market_year = '2020'

Of course, this isn't a sensible use case, because you could just write one query, GROUP BY market_year , and filter to WHERE market_year IN (‘2019’,’2020’) and get the same output. There are always multiple ways to write queries, but sometimes combining two queries with identical ...