Feature highlight: Subqueries using Any, Some, All Logical Conditions
Credit: Imagenation

 Source: IntlEmp database using Borland's InterBase 6.5

Description:
As we have seen Subqueries are very powerful retrieval tools. Mastering the In, Not In, Any/Some, All clauses when using Subqueries will add savvy to your SQL retrievals. Here are the basics.

1)In - where regionID in ( "NE", "E", "SE") - demonstrates that the In list can be enumerated and does not have to be a subquery. But a more typical case is:
  select distinct employeeid from orders where orderid in ( select orderid from orderdetails where discount >= 10)
This query lists all the employees who had to use discounts greater than 10% to get at least one order. The subquery generates a list from the Orderdetails table of all order items given a discount of 10% or more. Then from the Orders tables we get all the employees who authorized these discounts because we match the orderid from the Orders table to the list generated by the subquery. Now the sales manager can ask why the need for large discounts to the pertinent employees.

2)Not In - where regionID not in ("NE", "N") - will select all regions except "NE" and "N". Of course, Not In can also take a subquery.Note just as in all subqueries, only one column or field may be returned by the subquery. One could not have select nextDate, prevDate from salaries - as a subquery, even though the datatypes of the two fields are the same.

3 )Any/Some - select custID from orders where regionID != "E" and discount > any (select discount from orders where regionID = "E" and discount > 3)
This query lists the customers who are getting discounts greater than any offered in the East region. The Any qualifier works just like the In except it allows for >, >=, <, <= as well as the = (In) and != (Not In) operators. Thus, in our example, the subquery generates a list of all discounts in the East greater than the standard 3% discount. We then compare all the other regions - regionID != "E" - to see if any of the discounts there are greater than ANY discount in the East. This tells our sale manager which customers are getting discounts in other parts of the country greater than any offered in the EAST where a special product promotion and discounts are being offered.

In most SQL databases Any and Some are treated as equivalents; however Postgres makes a distinction.

4 )In our graphical example for All using Interbase 6.5:

This is almost identically the same as our Any qualified query with one key exception - now the total_value of a sale must be greater than ALL the values returned in our subquery. Now we know which customers are consistently buying orders bigger than our largest software sales - and so the sales manager can see what's up and try to emulate that success elsewhere.

5)Exists - acts as a qualifying subquery and thus almost always has a filtering where clause. If any value is returned by the subquery it is deemed successful or true. This means that the record in the main query will be retrieved. If no value is returned then the subquery returns the value false. Here is an example:

select pub.name from publishers pub where exists (select pubid from titles where titles.pubid = pub.pubid and toLowerCase(titles.subject) like "%psychology%");

So if there is just one reference in the titles.subject field to the substring "psychology" for a particular publisher - then that publisher will be listed/retrieved. Now some astute readers will point out that a simple inner join with condition will also work:

SELECT DISTINCT p1.pub_name FROM titles INNER JOIN
publishers p1 ON titles.pub_id = p1.pub_id WHERE (titles.type LIKE '%psychology%');

This is true; but there can be optimization advantages to putting the join in the subquery. Also this is not an unfrequent occurence in SQL, two quite different retrievals produce identically the same results.