highlight: Subqueries using Any, Some, All Logical Conditions
IntlEmp database using Borland's InterBase 6.5
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
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
select distinct employeeid from orders
where orderid in ( select orderid from orderdetails where discount >=
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.
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:
pub.name from publishers pub where exists (select pubid from
titles where titles.pubid = pub.pubid and toLowerCase(titles.subject) like
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:
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.