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.