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.

There are two ways to do a NATURAL JOIN. You can use a NATURAL JOIN or a WHERE syntax.

          NATURAL JOIN syntax format:                   Implied natural join example:

          SELECT columns                                        SELECT V.*,
             FROM table1                                                        I.DESCRIPTION
       NATURAL JOIN table2[,                                    FROM VENDOR_TBL V,
       NATURAL JOIN table3];                                               INVENTORY_TBL I
                                                                              WHERE V.VNDR_ID = I.VNDR_ID;

When NATURAL JOIN is explicitly stated, all columns in one table that have the same names, same data types, and same lengths as corresponding columns in the second table are compared for equality. This creates a NATURAL INNER JOIN.

> Be careful when doing explicit NATURAL JOINS. The data names, data types, and lengths must be exactly alike. This statement will unexpectedly return different results if someone else decides to add, change, rename, or delete the columns involved in the join.

> Access, DB2, and SQL Server do not support NATURAL JOIN syntax.

> MySQL only supports a NATURAL JOIN on an OUTER JOIN, not on an INNER JOIN. It also uses the non-standard STRAIGHT_JOIN.

> Oracle supports the JOIN syntax in version 9 and beyond.

> PostgreSQL supports all JOINs.

You can combine a column function with an INNER JOIN using a GROUP BY clause. The HAVING clause limits the number of groups retrieved for the GROUP BY clause.

                       JOIN syntax:                                                  WHERE syntax:

          SELECT t1.column1 AS ‘title1′,                       SELECT t1.column1 AS ‘title1′,
                       COUNT(t2.column2)                                      COUNT(t2.column2)
             FROM table1          [AS] ‘t1′                            FROM table1 [AS] ‘t1′,
            INNER JOIN table2   [AS] ‘t2′                                     table2 [AS] ‘t2′,
                 ON t1.column1 oper t2.column2,            WHERE t1.column1 oper t2.column2,
          GROUP BY t1.column1                                    GROUP BY t1.column1
          HAVING COUNT(t2.column2) condition           HAVING COUNT(t2.column2) condition
          ORDER BY t2.column2;                                  ORDER BY t2.column2;

Where oper is a comparison operation and WHERE is used for additional filtering.

You can combine a column function with an INNER JOIN, using a GROUP BY clause. These column functions can include mathematical formulas.

                      JOIN syntax:                                                WHERE syntax:

          SELECT t1.column1   AS ‘title1′,                  SELECT t1.column1 AS ‘title1′,
                       COUNT(t2.column2)                                    COUNT(t2.column2)
             FROM table1          [AS] ‘t1′                          FROM table1       [AS] ‘t1′,
             INNER JOIN table2 [AS] ‘t2′                                    table2       [AS] ‘t2′,
                  ON t1.column1 oper t2.column2,         WHERE t1.column1 oper t2.column2,
            GROUP BY t1.column1                                GROUP BY t1.column1
            ORDER BY t2.column2;                              ORDER BY t2.column2;

Where oper is a comparison operation and WHERE is used for additional filtering.

You can combine a column function with an INNER JOIN, using a GROUP BY clause. 

                      JOIN syntax:                                                 WHERE syntax:

          SELECT t1.column1 AS ‘title1′,                     SELECT t1.column1 AS ‘title1′,
                       COUNT(t2.column2)                                    COUNT(t2.column2)
             FROM table1 [AS] ‘t1′                                   FROM table1 [AS] ‘t1′,
             INNER JOIN table2 [AS] ‘t2′                                    table2 [AS] ‘t2′,
                  ON t1.column1 oper t2.column2,         WHERE t1.column1 oper t2.column2,
           GROUP BY t1.column1                                 GROUP BY t1.column1
           ORDER BY t2.column2;                               ORDER BY t2.column2;

Where oper is a comparison operation and WHERE is used for additional filtering.

See Column Functions.