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.

A UNION operator combines the results of two or more selects, giving all the rows returned by each select as one single result table.

                                                        UNION syntax:

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

UNION …….. establishes which result 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.

> Access, DB2, Oracle, PostgreSQL, and SQL Server support UNION and UNION ALL.

> MySQL does not support UNION or UNION ALL.

A FULL OUTER JOIN includes all rows from the left table and all rows from the right table. Any table rows without matching table rows from the other table will have nulls returned for the missing selected values.

                                                  FULL OUTER JOIN syntax:

                                      SELECT t1.column1 AS ‘title1′, 
                                                   t2.column2 AS ‘title2′, …
                                         FROM left_table                          [AS] ‘t1′
                                          FULL [OUTER] JOIN right_table [AS] ‘t2′
                                             ON left_t1.column1 oper right_t2.column2[, ...]
                                     [GROUP BY t1.column1]
                                     [HAVING      t1.column1]
                                     [ORDER BY t1.column1 [ASC|DESC]]

If the tables have common column names, those column names must be qualified with the table name or alias as follows: [left_table.]column oper [right_table.]column.

> While FULL OUTER JOINs can be done occasionally without the key word OUTER, the word OUTER should normally be included in the OUTER JOIN statement for clarity of understanding.

> Access and MySQL do not support FULL OUTER JOIN, but it can be done with a UNION ALL.

> Oracle 9i and later supports the FULL OUTER JOIN, but it also has the non-standard (+) outer join operator.

> SQL Server supports FULL OUTER JOIN, but it also has the non-standard outer join * in the WHERE syntax.

A RIGHT OUTER JOIN includes all rows from the right table and all matching rows from the left table. Any right table rows without matching left table rows will have nulls returned for the missing selected values. The order of tables in a RIGHT OUTER JOIN are critical and can not be rearranged.

                                              RIGHT OUTER JOIN syntax:

                                    SELECT t1.column1 AS ‘title1′,
                                                 t2.column2 AS ‘title2′, …
                                       FROM left_table [AS] ‘t1′
                                      RIGHT [OUTER] JOIN right_table [AS] ‘t2′
                                           ON left_t1.column1 oper right_t2.column2[, ...]
                                   [GROUP BY t1.column1] 
                                   [HAVING      t1.column1] 
                                   [ORDER BY t1.column1 [ASC|DESC]]

If the tables have common column names, those column names must be qualified with the table name or alias as follows: [left_table.]column oper [right_table.]column.

> While RIGHT OUTER JOINs can be done occasionally without the key word OUTER, the word OUTER should normally be included in the OUTER JOIN statement for clarity of understanding.

> Oracle 9i and later supports the OUTER JOIN, but it also has the non-standard (+) outer join operator.

> SQL Server supports OUTER JOIN, but it also has the non-standard outer join * in the WHERE syntax.

A LEFT OUTER JOIN includes all rows from the left table and all matching rows from the right table. Any left-table rows without matching right-table rows will have nulls returned for the missing selected values. The order of tables in a LEFT OUTER JOIN are critical and can not be rearranged.

                                               LEFT OUTER JOIN syntax:

                                   SELECT t1.column1 AS ‘title1′,
                                                t2.column2 AS ‘title2′, …
                                      FROM left_table                         [AS] ‘t1′
                                       LEFT [OUTER] JOIN right_table [AS] ‘t2′
                                          ON left_t1.column1 oper right_t2.column2[, ...]
                                  [GROUP BY t1.column1]
                                  [HAVING      t1.column1]
                                  [ORDER BY t1.column1 [ASC|DESC]]

If the tables have common column names, those column names must be qualified with the table name or alias as follows: [left_table.]column oper [right_table.]column.

> While LEFT OUTER JOINs can be done occasionally without the key word OUTER, the word OUTER should normally be included in the OUTER JOIN statement for clarity of understanding.

> Oracle 9i and later supports the LEFT OUTER JOIN, but it also has the non-standard (+) outer join operator.

> SQL Server supports LEFT OUTER JOIN, but it also has the non-standard outer join * in the WHERE syntax.

You have learned that an inner join only happens when there are identical matching values between tables. An INNER JOIN eliminates the rows that are in only one table but have no matching values in the other table.

An OUTER JOIN returns all rows from one of the tables, provided they meet the WHERE or HAVING conditions. The order in which you specify the tables is important in an OUTER JOIN, so you specify the left table or the right table.

 LEFT OUTER JOIN: includes all rows from the left table and all matching rows from the right table. Any left table rows without matching right table rows will have nulls returned for the missing selected values.

RIGHT OUTER JOIN: includes all rows from the right table and all matching rows from the left table. Any left table rows without matching right table rows will have nulls returned for the missing selected values.

FULL OUTER JOIN: includes all rows from the left and right tables. Any table rows not matching the other table rows will have nulls returned for the missing selected values.

There are two ways to do a CROSS JOIN. You can use the key words CROSS JOIN or do an implied cross join syntax.

          CROSS JOIN syntax format:                     Implied cross join example:

          SELECT *                                                 SELECT *
             FROM table1                                           FROM VENDOR_TBL,
           CROSS JOIN table2;                                            INVENTORY_TBL;

These statements result in a Cartesian product, also known as a cross product.
This means that every row of the first table is attached to every row of the second table.

If the first table has one thousand rows and the second table has one thousand rows, then the result table will have 1,000 X 1,000 = 1,000,000, or one million rows.

 > A CROSS JOIN is of little value, rarely used, and is resource intensive, giving prodigous results. For this reason, you should be very careful not to use a SELECT * with more than one table, unless you include other clauses.

> Access and DB2 only support CROSS JOINs with a WHERE clause.

> Oracle does not support JOIN syntax.

A self join is a normal INNER JOIN that joins one or more columns in the same table. Like any JOIN, you need two tables, but a self join means that two instances of the same table are specified.

                    Self Join syntax:                                            WHERE syntax:

        SELECT t1.column1  AS ‘title1′,                     SELECT t1.column1 AS ‘title1′,
                     t2.column1  AS ‘title2′, …                              t2.column1 AS ‘title2′, …
           FROM table1         [AS] ‘t1′                           FROM table1         [AS] ‘t1′,
          INNER JOIN table1 [AS] ‘t2′                                     table1         [AS] ‘t2′
               ON t1.column1 oper t2.column1           WHERE t1.column1 oper t2.column1
       [GROUP BY t1.column1]                               [GROUP BY t1.column1]
       [HAVING BY t1.column1]                              [HAVING BY t1.column1]
       [ORDER BY t1.column1 [ASC|DESC]]           [ORDER BY t1.column1 [ASC|DESC]]

When you are using the same column name, the column name must be qualified with the table name or alias as follows: t1.column1 oper t2.column1.

> WHERE syntax joins can only be done with AND. WHERE syntax joins using OR are not legal.