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:

An EXCEPT operator matches two or more selects, giving the rows in the first select that are not in the other selects.
In other words, return no rows EXCEPT those that are only found in the first select.

                                                        EXCEPT syntax:

                                                   SELECT statement_1
                                                   EXCEPT [ALL | DISTINCT]
                                                   SELECT statement_2
                                                   EXCEPT [ALL | DISTINCT]
                                                   SELECT statement_3 …

EXCEPT …… sets which rows are only in the first select.
                    Duplicate rows and nulls are excluded by default.

ALL …………. means that duplicate rows are not to be excluded.

DISTINCT … means duplicate rows and nulls are to be excluded.
                    If neither ALL or DISTINCT are specified, then DISTINCT is the default.

> The multiple selects must have the same number of columns (column names, functions, or arithmetic expressions).

> The order of columns must match in data type or be convertible to the same data type.

> If the name in a column is the same in all selects, then this name is used in the result. If this is not true, then most RDBMS systems take the name from the first select. If you want to create a different column name, use an AS clause in the first select for a column alias.

> ORDER BY can only be used in the last select, but it is applied to the entire result table.

> GROUP BY and HAVING can be used in each select statement, but do not apply to the final result table.

> DB2 and PostgreSQL support EXCEPT and EXCEPT ALL.

> Access, Oracle, MySQL, and SQL Server do not support EXCEPT and EXCEPT ALL.

An INTERSECT operator combines the results of two or more selects, giving all the rows that match between the selects.
While UNION acts like a logical OR between selects, an INTERSECT acts like a logical AND between selects.

                                                       INTERSECT syntax:

                                                      SELECT statement_1
                                                 INTERSECT [ALL | DISTINCT]
                                                      SELECT statement_2
                                                 INTERSECT [ALL | DISTINCT]
                                                      SELECT statement_3 …

INTERSECT … sets which sets will be combined into one result set.
                      Duplicate rows and nulls are excluded by default.

ALL ……………. means that duplicate rows are not to be excluded.

DISTINCT …… means duplicate rows and nulls are to be excluded.
                       If neither ALL or DISTINCT are specified, then DISTINCT is the default.

> The multiple selects must have the same number of columns (column names, functions, or arithmetic expressions).

> The order of columns must match in data type or be convertible to the same data type.

> If the name in a column is the same in all selects, then this name is used in the result. If this is not true, then most RDBMS systems take the name from the first select. If you want to create a different column name, use an AS clause in the first select for a column alias.

> ORDER BY can only be used in the last select, but it is applied to the entire result table.

> GROUP BY and HAVING can be used in each select statement, but do not apply to the final result table.

> DB2, Oracle, PostgreSQL, and SQL Server 2005 support INTERSECT and INTERSECT ALL.

> Access, and MySQL do not support INTERSECT and INTERSECT ALL.