Database Reference
In-Depth Information
results shown. Incidentally, we used the
CONCAT()
function toconcatenate together the
member's first and last name for the
Birder
field in the results.
There are other types of joins besides a plain
JOIN
. Let's do another
SELECT
using an-
other type of
JOIN
. For an example of this, we'll get a list of Egrets and their conserva-
tion status. Enter the following SQL statement:
SELECT common_name AS 'Bird',
conservation_state AS 'Status'
FROM birds
LEFT JOIN conservation_status USING(conservation_status_id)
WHERE common_name LIKE '%Egret%'
ORDER BY Status, Bird;
+--------------------+-----------------+
| Bird | Status |
+--------------------+-----------------+
| Great Egret | NULL |
| Cattle Egret | Least Concern |
| Intermediate Egret | Least Concern |
| Little Egret | Least Concern |
| Snowy Egret | Least Concern |
| Reddish Egret | Near Threatened |
| Chinese Egret | Vulnerable |
| Slaty Egret | Vulnerable |
+--------------------+-----------------+
This
SELECT
statement is like the previous examples, except that instead of using a
JOIN
, we're using a
LEFT JOIN
. Thistype of join selects rows in the table on the left
(i.e.,
birds
) regardless of whether there is a matching row in the table on the right (i.e.,
conservation_status
). Because there is no match on the right, MySQL returns a
NULL value for columns it cannot reconcile from the table on the right. You can see this
in the results. The
Great Egret
has a value of NULL for its
Status
. This is because
no value was entered in the
conservation_status_id
column of the row related to
that bird species. It would return NULL if the value of that column is NULL, blank if the
column was set to empty (e.g.,
''
), or any value that does not match in the right table.
Because of the
LEFT JOIN
, the results show all birds with the word
Egret
in the com-
mon name even if we don't know their conservation status. It also indicates which Egrets
need to set the value of
conservation_status_id
. We'll need to update that row
and others like it.An
UPDATE
statement with this same
LEFT JOIN
can easily do that.
We'll show a couple in the nextsection.
