A value in a comparison can be expressed as a column name, an arithmetic expression, a constant,
or a subselect.

A subselect comparison test uses the same six operators (=, <, <=, >, >=, <>) discussed previously.

Format restriction:

It is suggested that you keep subselects to the right of comparison operators for portability.

The subselect must return only one value. A subselect that gives multiple values will cause an error.

If the subselect returns zero rows, the comparison test will evaluate to FALSE.:

                                                  SELECT select_columns
                                                     FROM tables
                                                   WHERE value operator (subselect);

The SQL1 standard specified that a subselect can only be on the right of the comparison operator.

                                              value > (subselect)           preferred format.
                                              (subselect) < value           not recommended.

While the SQL2 standard relaxed this:

> DB2, Oracle, Postgre SQL, and SQL Server support comparison subselects in the WHERE and HAVING clauses.

> MySQL supports comparison subselects in the WHERE clause only.