When a subquery is 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.).
SELECT ... FROM ... WHERE expression operator [ANY/ALL] (Subquery)
•Only one subquery is allowed per query.
•The subquery is executed first, then the main query.
•ANY/ALL are fully supported. If not specified, default is ANY.
•Logical operators >, >=, <, <=, =, <>, IN, can be used in WHERE clause
•The subquery must be enclosed in parenthesis.
•SOME keyword not supported although it is generally equivalent to ANY.
•EXISTS predicate not yet supported. However, if you have a single column, you can use the IN clause. For example:
SELECT O.OrderNo, O.CustNo
FROM Orders O
WHERE EXISTS
(SELECT C.CustNo
FROM Customer C
WHERE (C.CustNo = O.CustNo))
can be changed to:
SELECT O.OrderNo, O.CustNo
FROM Orders O
WHERE O.CustNo IN
(SELECT C.CustNo
FROM Customer C
WHERE (C.CustNo = O.CustNo))
Examples:
SELECT * FROM Orders WHERE AmountPaid > (SELECT AVG(AmoungPaid) FROM Orders
WHERE OrderNo > 1300)
SELECT * FROM Orders WHERE AmountPaid > ANY (SELECT AmountPaid FROM Orders
WHERE OrderNo BETWEEN 1000 AND 3000)
SELECT * FROM Orders WHERE AmountPaid > ALL (SELECT AmountPaid FROM Orders
WHERE OrderNo BETWEEN 1000 AND 3000)
SELECT * FROM Orders WHERE (OrderNo > 1000) And
(AmountPaid > ALL (SELECT AmountPaid FROM Orders
WHERE OrderNo BETWEEN 1000 AND 3000))
SELECT * FROM Customer WHERE custno >= ALL (SELECT CustNo FROM customer
WHERE City IN ('Freeport', 'Christiansted', 'Kailua-Kona', 'Giribaldi',
'Kitchener', 'Negril'))
SELECT * FROM Files WHERE fileID IN (SELECT fileID FROM FilesAut
WHERE AuthorID = 2912)
Another common usage of subqueries involves the use of logical operators to allow a WHERE condition to include the SELECT output of a subquery.
For example, to 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 OWNERID FROM ANTIQUES
WHERE PRICE >(SELECT AVG(PRICE) FROM ANTIQUES)
The subquery calculates the average Price, and using that figure, an OwnerID is printed for every item costing over that figure. You could also use DISTINCT OWNERID, to eliminate duplicates.
The following subquery list the Last Names of those in the AntiqueOwners table, ONLY if they have bought an item:
SELECT OWNERLASTNAME FROM ANTIQUEOWNERS
WHERE OWNERID = (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).
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.