A subquery comparison test uses the same six operators (=, <, <=, >, >=, <>) discussed previously.
The subquery can include only one column name or expression.
= ALL is rarely used, since every value returned by the subselect must be identical and equal to the test value.

The ALL operator returns a TRUE value:
> when every row in a subselect matches the value comparison,
> or when no rows in a subselect match the value comparison.
This may seem backward, but if the subselect returns no rows, the ALL condition is TRUE.

The ALL operator returns a FALSE value:
> when some rows in a subselect do not match the value comparison.

The ALL operator returns a NULL value:
> when any rows in a subselect are NULL, then the operation evaluates to NULL (not TRUE or FALSE).

< ALL (subselect) means less than every value in the subselect and is equivalent to < MIN (subselect values).
> ALL (subselect) means greater than every value in the subselect and is equivalent to > MAX (subselect values).

                     Format:           SELECT select_columns
                                              FROM tables
                                            WHERE value operator ALL (subselect);

> DB2, Oracle, Postgre SQL, and SQL Server support ALL.

> MySQL does not support ALL.

We use IN to compare one value with a set of values.

If that value is IN the list returned by the subselect, then the WHERE clause returns a true value and the specified columns from the table row being processed are added to the result table.

                    Format:           SELECT select_columns
                                              FROM tables
                                            WHERE value [NOT] IN (subselect);

> Oracle, and SQL Server support IN.

> DB2 supports IN with some variations.

> MySQL, and Postgre SQL do not support IN.

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.