All of these statements show the many ways to solve the same problems and give the same results.         

          SELECT DISTINCT V.VENDOR_NAME
            FROM VENDOR_TBL V
           INNER JOIN INVENTORY_TBL I
                ON V.VNDR_ID = I.VNDR_ID;

          SELECT DISTINCT V.VENDOR_NAME
            FROM VENDOR_TBL V, INVENTORY_TBL I
          WHERE V.VNDR_ID = I.VNDR_ID;

          SELECT VENDOR_NAME
            FROM VENDOR_TBL V
          WHERE VNDR_ID IN
                     (SELECT VNDR_ID
                        FROM INVENTORY_TBL);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL
          WHERE VNDR_ID = ANY
                     (SELECT VNDR_ID
                        FROM INVENTORY_TBL);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL
          WHERE VNDR_ID = ALL
                     (SELECT VNDR_ID
                        FROM INVENTORY_TBL);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL V
           WHERE EXISTS
                       (SELECT *
                           FROM INVENTORY_TBL I
                         WHERE V.VNDR_ID = I.VNDR_ID);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL V
          WHERE 0 <
                      (SELECT COUNT(*)
                         FROM INVENTORY_TBL I
                       WHERE V.VNDR_ID = I.VNDR_ID);

With all of these choices, don’t use confusing statements to show how smart you are.
Use the simplest and most understandable solution for easy maintenance.
Trust me, your employer will appreciate your simple solutions and how quickly you can make changes.

You want to know what vehicles are available from the ‘SPACE TRANSPORTS INC’ store that are below the store’s average price.

                    SELECT DESCRIPTION, PRICE
                      FROM INVENTORY_TBL
                    WHERE PRICE <
                               (SELECT AVG(PRICE)
                                  FROM INVENTORY_TBL
                                WHERE STOR_ID IN
                                           (SELECT STOR_ID
                                              FROM STORE_TBL
                                            WHERE STORE_NAME =
                                                        ‘SPACE TRANSPORTS INC’));

First, the innermost select finds the STOR_ID for the STORE_NAME = ‘SPACE TRANSPORTS INC’.

Result: ‘S001′

The next outer select finds the average price for vehicles in store ‘S001′.

                        960,400.00
                        128,200.00
                        569,500.00
                    + 417,000.00
                     2,075,100.00 / 4 = Result: 518,775.00

The outer select retrieves the DESCRIPTION and PRICE, where the price is below the average price of 518,775.00.

Result:

Most RDBMS systems have no limit on the number of subselect levels.

EXISTS does not try to compare on values, so it has no comparison operators.

It is common to use SELECT * in an EXISTS subselect, because the columns values do not matter.
EXISTS is simply testing for the existence of any rows, so format the subselect any way you like.
It does not matter how many columns or rows are returned by the subselect — one or a thousand.

If the subselect returns at least one row, EXISTS is true and NOT EXISTS is false.

If the subselect returns no rows, EXISTS is false and NOT EXISTS is true.

If the subselect returns a row that is null, EXISTS is true and NOT EXISTS is false.

> While EXISTS can have a simple subselect, it is normally associated with a Correlated Subselect.
See correlated subselect.

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.

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.

A simple subselect (also known as a non-correlated subselect) is a subselect that can be evaluated without the use of the outer select.

When this is true, the subselect is processed first and only processed once, independently of the outer select, giving a temporary result table.

Then, the temporary result table is passed to the outer select for further processing to give the final result.

Simple subselects with scalar functions can only follow a comparison operator if they return just one value.
That is, the subselect must return only one row containing only one column.

Simple subselects may return multiple values, but are usable only with multivalue expressions like [NOT] IN, ANY, ALL, SOME, or [NOT] EXISTS.

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