Database Reference
In-Depth Information
Row Subqueries
Row subqueriesretrieve a single row of data that is then used by the outer query. It's used
ina
WHERE
clause to compare one row of columns to one row of columns selected in the
subquery. Let's consider an example of this and then we'll discuss it more. Suppose an-
other bird-watcher site closes, this one in Eastern Europe. They send us their database,
which contains a table with the names of their members, and another table with informa-
tion members provided related to birds they spotted. We put both of these tables in the
birdwatchers
database to import into our tables. In the process of importing these
members into our
humans
table, we discover people who are already members of our
site. That's OK: we know how to avoid importing the duplicates. Now we want to import
the table of birds spottings. Because there were duplicate members, maybe those members
have logged information on birds they saw in the wild on this Eastern European site. So
we want to check that each entry is not a duplicate and then import it. Look at this SQL
statement:
INSERT INTO
bird_sightings
(
bird_id
,
human_id
,
time_seen
,
location_gps
)
VALUES
(
SELECT
birds
.
bird_id
,
humans
.
human_id
,
date_spotted
,
gps_coordinates
FROM
(
SELECT
personal_name
,
family_name
,
science_name
,
date_spotted
,
CONCAT
(
latitude
,
'; '
,
longitude
)
AS
gps_coordinates
FROM
eastern_birders
JOIN
eastern_birders_spottings
USING
(
birder_id
)
WHERE
(
personal_name
,
family_name
,
science_name
,
CONCAT
(
latitude
,
'; '
,
longitude
) )
NOT IN
(
SELECT
name_first
,
name_last
,
scientific_name
,
location_gps
FROM
humans
JOIN
bird_sightings
USING
(
human_id
)
JOIN
rookery
.
birds
USING
(
bird_id
) ) )
AS
derived_1
JOIN
humans
ON
(
personal_name
=
name_first
AND
family_name
=
name_last
)
JOIN
rookery
.
birds
ON
(
scientific_name
=
science_name
) );
This looks very complicated and can be difficult to understand or construct correctly. Let's
discern the major elements here. Look first at the subquery in parentheses, the nested sub-
query. We're selecting data from tables in our database: the names of each person, the bird
