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.

You can join more than two tables.

                      JOIN syntax:                                                   WHERE syntax:

         SELECT t1.column1   AS ‘title1′,                      SELECT t1.column1 AS ‘title1′,
                      t2.column2   AS ‘title2′,                                   t2.column2 AS ‘title2′,
                      t3.column3   AS ‘title3′                                    t3.column3 AS ‘title3′
            FROM table1          [AS] ‘t1′                              FROM table1       [AS] ‘t1′,
            INNER JOIN table2 [AS] ‘t2′                                        table2       [AS] ‘t2′,
                 ON t1.column1 oper t2.column2,                          table3       [AS] ‘t3′
            INNER JOIN table3 [AS] ‘t3′                          WHERE t1.column1 oper t2.column2,
                 ON t2.column2 oper t3.column3,           AND|OR t2.column2 oper t3.column3,
          WHERE condition1                                        AND|OR condition1
         AND|OR condition2;                                       AND|OR condition2;

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

> In a three-table join, only one table will be used to bridge to the other two tables. However, it really does not matter which table is picked to serve this function.

You can use WHERE clause conditions other than = using <, <=, >, >=, <>, NOT column =, NOT NULL, LIKE, BETWEEN, IN, etc.

                       JOIN syntax:                                                   WHERE syntax:

          SELECT t1.column1 AS ‘title1′,                       SELECT t1.column1 AS ‘title1′,
                       t2.column2 AS ‘title2′, …                                 t2.column2 AS ‘title2′, …
             FROM table1        [AS] ‘t1′                              FROM table1         [AS] ‘t1′,
     INNER JOIN table2       [AS] ‘t2′                                        table2         [AS] ‘t2′
                  ON t1.column1 oper t2.column1[,          WHERE t1.column1 oper t2.column1[,
          AND|OR t1.column2 oper t2.column2]          AND|OR t1.column2 oper t2.column2];
           WHERE condition1
         [AND|OR condition2];

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

You can use the WHERE clause to limit your selection.

                       JOIN syntax:                                                  WHERE syntax:

          SELECT t1.column1 AS ‘title1′,                      SELECT t1.column1 AS ‘title1′,
                       t2.column2 AS ‘title2′, …                                t2.column2 AS ‘title2′, …
             FROM table1 [AS] ‘t1′                                    FROM table1 [AS] ‘t1′,
             INNER JOIN table2 [AS] ‘t2′                                     table2 [AS] ‘t2′
                  ON t1.column1 oper t2.column1[,         WHERE t1.column1 oper t2.column1[,
          AND|OR t1.column2 oper t2.column2]          AND|OR t1.column2 oper t2.column2];
           WHERE condition1
         [AND|OR condition2];

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

You can use multiple conditions to match tables.

                       JOIN syntax:                                                 WHERE syntax:

          SELECT t1.column1   AS ‘title1′,                   SELECT t1.column1 AS ‘title1′,
                       t2.column2   AS ‘title2′, …                             t2.column2 AS ‘title2′, …
             FROM table1          [AS] ‘t1′                           FROM table1        [AS] ‘t1′,
             INNER JOIN table2 [AS] ‘t2′                                     table2        [AS] ‘t2′
                  ON t1.column1 = t2.column1[,              WHERE t1.column1 = t2.column1[,
          AND|OR t1.column2 = t2.column2];             AND|OR t1.column2 = t2.column2];

> You can do matching on any number of tables and columns.

The most important and most used JOIN is the INNER JOIN or equijoin. This joins two tables with common columns that match equally. These matching columns are usually a part of the primary key. The format is:

                       JOIN syntax:                                                WHERE syntax:

          SELECT t1.column1 AS ‘title1′,                       SELECT t1.column1 AS ‘title1′,
                       t2.column2 AS ‘title2′, …                                t2.column2 AS ‘title2′, …
             FROM table1 [AS] ‘t1′                                     FROM table1 [AS] ‘t1′,
             INNER JOIN table2 [AS] ‘t2′                                      table2 [AS] ‘t2′
                  ON t1.column1 = t2.column2[, ...]          WHERE t1.column1 = t2.column2[, ...]
          [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]]

The join is called an INNER JOIN because only those rows that meet the join condition in both tables are described as “inside” the join.

> JOIN is synonymous with INNER JOIN.

> The WHERE syntax is simpler and easier to understand for INNER JOINs. However, both syntax formats are shown in each topic so that you can understand and use either format.

> You can substitute the USING clause for the ON clause.
Access, DB2, and SQL Server do not support the USING clause.
MySQL requires the USING columns to be qualified by the table name.

Now, instead of using the key word JOIN, we can do a join with the WHERE clause.

              WHERE syntax format:                              Example:

          SELECT select_columns                     SELECT V.VNDR_ID,
             FROM table1, table2                                     V.VENDOR_NAME,
         [WHERE join_condition]                                   I.DESCRIPTION
          [GROUP BY grouping_columns]            FROM VENDOR_TBL      V,
          [HAVING search_condition]                              INVENTORY_TBL I
          [ORDER BY sort_columns];                WHERE V.VNDR_ID = I.VNDR_ID;

Select_columns is one or more selected columns separated by a comma from table1 and table2.

The WHERE clause reflects the conditions for joining the two tables.

The results of this join, using WHERE, will produce the same results as the previous JOIN.

> The WHERE clause can also include non-join search conditions for filtering.

> The WHERE syntax is simpler and easier to understand for JOINs and most examples in this course are done using this syntax. However, both syntax formats are shown in each topic so that you can understand and use either format.