Joining and Subquerying Data - Learning MySQL and MariaDB - page 186

Here is how the first of these two syntax looks using a JOIN :
SELECT book_id , title , status_name
FROM books
JOIN status_names ON ( status = status_id );
This is the same example as before, but without the WHERE clause. It doesn't need it, be-
cause it uses ON instead to indicate the join point. If we were to alter the books table to
modify the name of the status column to be status_id , so that the names of both
columns on which we join these two tables are the same, we could do the join like this:
SELECT book_id , title , status_name
FROM books
JOIN status_names USING ( status_id );
Here we use thekeyword USING in the JOIN clause to indicate the identical column by
which to join.
These syntaxes are only two of a few possible with the JOIN . They show how you might
construct a SELECT statement using a JOIN . It's basically the same for the UPDATE and
DELETE statements. In the next subsections, we'll consider the methods for using JOIN
with each of these three SQL statements, and look at some examples foreach.
Selecting a Basic Join
Suppose we want to get a list ofspecies of Geese whose existence is Threatened —
that's a category of conservation states. We will need to construct a SELECT statement
that takes data from the birds table and the conservation_status table. The
shared data in the birds and the conservation_status tables is the conserva-
tion_status_id column of each table. We didn't have to give the column the same
name in each table, but doing so makes it easier to know where to join them.
Enter the following in the mysql client:
SELECT common_name, conservation_state
FROM birds
JOIN conservation_status
ON(birds.conservation_status_id =
conservation_status.conservation_status_id)
WHERE conservation_category = 'Threatened'
AND common_name LIKE '%Goose%';
+----------------------------+--------------------+
| common_name | conservation_state |
+----------------------------+--------------------+