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.