|
Another common usage
of subqueries involves the use of
operators to allow a Where condition
to include the Select output of a
subquery. First, list the buyers who
purchased an expensive item (the Price
of the item is $100 greater than the
average price of all items purchased):
SELECT BUYERID
FROM ANTIQUES
WHERE PRICE > (SELECT
AVG(PRICE) + 100
FROM ANTIQUES);
The subquery calculates
the average Price, plus $100, and
using that figure, an OwnerID is printed
for every item costing over that figure.
One could use DISTINCT BUYERID, to
eliminate duplicates.
List the Last Names of those in the
AntiqueOwners table, ONLY if they
have bought an item:
SELECT OWNERLASTNAME
FROM ANTIQUEOWNERS
WHERE OWNERID IN (SELECT
DISTINCT BUYERID
FROM ANTIQUES);
The subquery returns
a list of buyers, and the Last Name
is printed for an Antique Owner if
and only if the Owner's ID appears
in the subquery list (sometimes called
a candidate list). Note:
on some DBMS's, equals can be used
instead of IN, but for clarity's sake,
since a set is returned from the subquery,
IN is the better choice.
For an Update example,
we know that the gentleman who bought
the bookcase has the wrong First Name
in the database...it should be John:
UPDATE ANTIQUEOWNERS
SET OWNERFIRSTNAME = 'John'
WHERE OWNERID = (SELECT
BUYERID
FROM ANTIQUES
WHERE ITEM = 'Bookcase');
First, the subquery
finds the BuyerID for the person(s)
who bought the Bookcase, then the
outer query updates his First Name.
Remember this
rule about subqueries: when you
have a subquery as part of a WHERE
condition, the Select clause in the
subquery must have columns that match
in number and type to those in the
Where clause of the outer query. In
other words, if you have "WHERE
ColumnName = (SELECT...);", the
Select must have only one column in
it, to match the ColumnName in the
outer Where clause, and they
must match in type (both being integers,
both being character strings, etc.).
|
