Advanced Select
Single value returns may be used in direct comparison with the object of the condition
syntax of such a WHERE clause is:
WHERE column_name condition (SELECT statement)
where the column_name may be a table column, expression or function and the condition may be:
| = | Equal to |
| != or <> | !or<> Not equal to |
| < | Less than |
| <= | Less than or equal to |
| > | Greater than = |
| >= | Greater than or equal to |
For example
SELECT c0001title, c0001firstname, c0001surname
FROM t0001customer
WHERE c0001max_rent = (SELECT MAX(max c0001rent) FROM t0001customer);
In the above example, the subquery calculates a value that requires the
search of 1 customer table before it can re-search the table to
find any matching values of max_rent
Any conditions based on the value of an Aggregate Function -
COUNT, SUM, AVG, MIN or
MAX - must be used in a subquery.