Nulls in a subselect can produce unexpected results.

When nulls are in a comparison of a subselect, no rows will be returned, because NULL means that there is no value. When this happens, no rows are returned because the operation evaluated to NULL, not TRUE or FALSE.

To avoid this, test the value of the subselect with WHERE value IS NOT NULL or HAVING value IS NOT NULL.

This test is not necessary if NOT NULL is specified in the table definition of this column.

                    Format:      SELECT select_columns
                                         FROM tables
                                      WHERE value operator
                                                  (SELECT select_columns
                                                      FROM tables
                                                   WHERE conditions
                                                        AND column IS NOT NULL);

Earlier, we discussed Qualifying Column Names under the discussion on JOINs.

Any column names can be explicitly qualified. Within a statement with a subselect, column names are implicitly qualified by the table referenced in the FROM statement. Inner column names are implied to be qualified by the inner FROM clause and outer column names are implied to be qualified by the outer FROM clause.

You can explicitly qualify column names to override SQL’s assumptions about which tables are associated with which column names.

If the same column name is in two or more tables in the same statement, it is best to explicitly qualify these column names for clarity.

For correlated subselects, references in an inner subselect to a column name in a table of the outer subselect most be explicitly qualified.

A simple subselect (or non-correlated subselect) is a subselect that can be evaluated without the use of the outer select. The simple subselect is processed first, before the outer select is processed, and it is processed only once, handing its results to the outer select for processing.In a correlated subselect, the subselect contains a reference to a value from a higher reference, or outer select. This is called the correlated reference, and these columns must use qualified column names to reference the table in the outer select. It is not possible to execute the subselect just once. Instead it must be executed repeatedly, once for each row from the outer select, because the value of the correlated reference will change with each row.

Correlated subselects are more difficult to write and evaluate, but they solve problems that can not be solved in any other way.

Simple subselects and correlated subselects can have the inner and outer selects reference the same table or a different table.

> Go on, and you will see how each type of subselect works.

Subselects are located within a WHERE or HAVING clause. This statement is called the outer query.The subselect (also known as a subquery) is enclosed in parentheses and is called the inner query.

For simplicity, we will show it in the WHERE clause in our examples.

Remember, whatever we say about using it in the WHERE clause also applies to the HAVING clause.

> Some subqueries give a list of values that are used as input to the outer query.

> Some subqueries produce a value that the outer query uses with a comparison operator.

> Some subqueries return a value of true or false.
A subselect must:

> Be a single select statement (no JOINS or UNIONS), but may contain another subselect.

> Start and end with left and right parentheses without a semicolon (;) between the parentheses. The entire statement from select to subselect must be terminated with only one semicolon at the end.

> Reference only column names in the subselect FROM table or FROM tables of outer selects.

> Have no ORDER BY in the subselect since a subselect produces only intermediate results.
If a table appears in an inner select, but does not appear in the outer select (such as in the FROM clause), then columns from that table can not be included in the final result.

Sample WHERE formats are:

                    WHERE test_variable operator (subquery)
                    WHERE test_variable [NOT] IN (subquery)
                    WHERE test_variable operator ANY (subquery)
                    WHERE test_variable operator ALL (subquery)
                    WHERE [NOT] EXISTS (subquery)

You need to get a DESCRIPTION of vehicles made by vendors from the STATE of ‘CA’.If you have a VENDOR_TBL:

 

And an INVENTORY_TBL:

Result Table:

Now, instead of an INNER JOIN (first example), you can do a subselect (second example) to get the same results.

          SELECT I.DESCRIPTION                               SELECT DESCRIPTION
             FROM INVENTORY_TBL I,                            FROM INVENTORY_TBL
                       VENDOR_TBL      V              or        WHERE VNDR_ID =
          WHERE I.VNDR_ID = V.VNDR_ID                        (SELECT VNDR_ID
               AND V.STATE = ‘CA’;                                          FROM VENDOR_TBL
                                                                                       WHERE STATE = ‘CA’);

> All RDBMS systems process the inner query first. Then they use that data to run the outer query for the result.

> Remember that subselects can be nested within other subselects, so a subselect (inner query) can be an outer query for another nested subselect.

A subselect or subquery is a nested select inside of another select.Sometimes you can’t answer one question until you answer another question. You might think, I need to get a DESCRIPTION of vehicles made by vendors from the STATE of ‘CA’. At first glance you think, I will first find all vendors from ‘CA’ and then I will use that information to get the vehicle DESCRIPTION. This would be a two step process.

Your second thought might be to do an INNER JOIN between these two tables. This would be a simpler one step process. The first example is the JOIN format and the second example is the WHERE format for a join.

             SELECT I.DESCRIPTION                                 SELECT I.DESCRIPTION
               FROM INVENTORY_TBL     I                             FROM INVENTORY_TBL I,
              INNER JOIN VENDOR_TBL V               or                     VENDOR_TBL     V,
                   ON I.VNDR_ID = V.VNDR_ID                     WHERE I.VNDR_ID = V.VNDR_ID
            WHERE V.STATE = ‘CA’;                                        AND V.STATE = ‘CA’;

> Go on and you will see how to use a subselect.

If you have a VENDOR_TBL:

And an INVENTORY_TBL:

Result Table: