Database Reference
In-Depth Information
teria given to it will be deleted, and any rows found in the
humans
table for which there
isn't a match in the
prize_winners
table, but which match the criteria of the
WHERE
clause will be deleted also. This prevents what are known as orphaned rows.
For general maintenance, we should check occasionally to see if there are rows in the
prize_winners
table that don't have matching rows in the
humans
table, and then
delete them. Someone might have had us delete their account, but we may have forgotten
to remove entries for them in related tables. To handle that possibility,we could use
RIGHT JOIN
instead of
LEFT JOIN
. We could enter something like this:
DELETE FROM
prize_winners
USING
humans
RIGHT JOIN
prize_winners
ON
humans
.
human_id
=
prize_winners
.
human_id
WHERE
humans
.
human_id
IS
NULL
;
In this
DELETE
statement, we listed only the
prize_winners
table in the
FROM
clause
because that's the only one from which we want to delete rows. It's a good policy not to
list tables that are not to be affectedin the
FROM
clause of a
DELETE
statement, even if
you think there's no possible way that there is a row that would be deleted in the other
tables.
Because we put the
humans
table first in the
USING
clause and the
prize_winners
table second, we're doing a
RIGHT JOIN
so that columns from the table on the right
(
prize_winners
) will be deleted even if there is no value in the table on the left. If we
reversed the order of the tables, we would then need a
LEFT JOIN
for this task.
It's worth focusing for a moment on the final clause of the previous
DELETE
statement, a
WHERE
clause checking for NULLs in one column. As we saw earlier, a
LEFT JOIN
or
RIGHT JOIN
can return rows where there was nothing in the column you're doing the
join on. The results contain NULL for the missing value. So in the
WHERE
clause here,
we're using that as the condition for finding the orphaned rows in the
prize_winners
table.
There are many contortions to the
JOIN
clause. The basic
JOIN
syntaxes that we covered
in
Selecting a Basic Join
are worth learning well; they will be the ones you will use
primarily. You will sometimes have a need for using a
LEFT JOIN
or a
RIGHT JOIN
.
Let's move on to a related topic that can be valuable in many situations:subqueries.
