Learning MySQL and MariaDB

Chapter 9. Joining and Subquerying Data

Most of the examples used in this book thus far have intentionally involved one table per SQL statement in order to allow you to focus on the basic syntax of each SQL statement. When developing a MySQL or MariaDB database, though, you will often query multiple tables. There are a few methods by which you may do that—you’ve seen some simple examples of them in previous chapters. This chapter covers how to merge results from multiple SQL statements, how to join tables, and how to use subqueries to achieve similar results.

Unifying Results

Let’s start this chapter by looking at a simple method of unifying results from multiple SQL statements. There may be times when you just want the unified results of two SELECT statements that don’t interact with each other. In this situation, you can use the UNION operator, which merges two SELECT statements to form a unified results set. You can merge many SELECT statements together simply by placing the UNION between them in a chain. Let’s look at an example.

In Counting and Grouping Results, we queried the birds table to get a count of the number of birds in the Pelecanidae family (i.e., Pelicans). Suppose we want to also know how many birds are in the Ardeidae family (i.e., Herons). That’s easy to do: we’d use a copy of the same SELECT, but change the value in the WHERE clause. Suppose further that we want to merge the results of the SELECT statement counting Pelicans with the results of a SELECT counting ...