The EXISTS operator tests a subselect for the existance of one or more rows.

The EXISTS operator returns a TRUE value:
> when even one row in a subselect matches the value comparison in the subselect.

The EXISTS operator returns a FALSE value:
> when no rows in a subselect match the value comparison in the subselect.

The NOT EXISTS operator returns a TRUE value:
> when no rows in a subselect match the value comparison in the subselect.

The NOT EXISTS operator returns a FALSE value:

> when even one row in a subselect matches the value comparison in the subselect.

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

> DB2, MySQL, Oracle, Postgre SQL, and SQL Server support EXISTS.

A correlated subselect is a subselect that can not be evaluated without the use of the outer select, because it depends on the results of the outer select for its values.

The correlated subselect refers to entries in the FROM clause of the outer select. Therefore, references to columns in the correlated subselect must be qualified column names to explicitly specify their table of origin. Since subselects can be nested many levels deep, a correlated subselect may reference any level higher than its own level.

Execution starts with the outer select, and with each value returned, the inner correlated subselect is executed until processing of both the outer and inner selects is complete.

The subselect may reference the same table as the outer select, or it may reference a different table.

          Format:           SELECT outer_columns
                                   FROM outer_tables
                                 WHERE outer_value operator
                                            (SELECT inner_columns
                                               FROM inner_tables
                                             WHERE inner_column operator outer_column);

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

> MySQL supports correlated subselects in the WHERE clause only.

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

In many RDBMS systems, ANY and SOME are synonymous in meaning and perform the same function.
Therefore, we will use ANY in our descriptions, and it is understood that SOME serves the same purpose.

The ANY and SOME operators return a TRUE value:
> when at least one row in a subselect matches the value comparison.

The ANY and SOME operators return a FALSE value:
> when no rows in a subselect match the value comparison,
> or when a subselect returns an empty result set.

The ANY and SOME operators return a NULL value:
> when any rows in a subselect are NULL, then the operation evaluates to NULL (not TRUE or FALSE).

                    Format:           SELECT select_columns
                                              FROM tables
                                            WHERE value operator [ANY|SOME] (subselect);

The expression < ANY is the same as column function < MAX.

The expression > ANY is the same as column function > MIN.

> DB2, Oracle, Postgre SQL, and SQL Server support ANY or SOME.

> MySQL does not support ANY or SOME.