SQL EXISTS, ALL & ANY

EXISTS uses a subquery as a condition, where the condition is True if the subquery returns any rows, and False if the subquery does not return any rows; this is a nonintuitive feature with few unique uses. However, if a prospective customer wanted to see the list of Owners only if the shop dealt in Chairs, try:

SELECT OWNERFIRSTNAME, OWNERLASTNAME FROM ANTIQUEOWNERS 
WHERE EXISTS (SELECT * FROM ANTIQUES WHERE ITEM = 'Chair');

If there are any Chairs in the Antiques column, the subquery would return a row or rows, making the EXISTS clause true, causing SQL to list the Antique Owners. If there had been no Chairs, no rows would have been returned by the outside query.

ALL is another unusual feature, as ALL queries can usually be done with different, and possibly simpler methods; let's take a look at an example query:

SELECT BUYERID, ITEM FROM ANTIQUES
WHERE PRICE >= ALL (SELECT PRICE FROM ANTIQUES);

This will return the largest priced item (or more than one item if there is a tie), and its buyer. The subquery returns a list of all Prices in the Antiques table, and the outer query goes through each row of the Antiques table, and if its Price is greater than or equal to every (or ALL) Prices in the list, it is listed, giving the highest priced Item. The reason ">=" must be used is that the highest priced item will be equal to the highest price on the list, because this Item is in the Price list.

ANY

ANY works in the same way as ALL but evaluates to true if the condition is true of any single value returned by the subquery. For example, the query:

SELECT ColumnA FROM TableA
WHERE ColumnA > ANY (SELECT ColumnB FROM TableB);

will return all the rows from TableA where the value in ColumnA is greater than any one of the individual values in ColumnB of TableB.