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.

> Joins exist only for the duration of the query. They are not part of the database.

> Joined columns don’t need the same column name (except when doing a natural join).

> If a joining column contains a null, the null never joins.

> The joining columns must be compatible, such as (INTEGER, NUMERIC, FLOAT), (CHAR, VARCHAR), (DATE, TIME, TIMESTAMP).

There are two ways to do a join. You can use a JOIN or a WHERE syntax. WHERE joins were the only way to do a join until SQL-92 introduced the JOIN syntax. As a result, you will find both forms in use.

                  JOIN syntax format:                                         Example:

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

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

Join_type is the type of join to be performed, such as INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, NATURAL JOIN, and CROSS JOIN.

ON is not allowed for CROSS JOIN or NATURAL JOIN.

Join_condition shows the conditions for one or more joined rows.
The format is [table1.]column oper [table2.]column where oper is =, <, <=, >, >=, <>, AND, OR, or NOT.

The WHERE, GROUP BY, HAVING, and ORDER BY have been discussed in previous topics.

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

You can create table aliases just as you did column aliases. The difference is that with column aliases, the key word AS is required and it changes the resulting column name. With the table alias, the key word AS is optional and the table alias is used only to associate columns with tables — the resulting table is unchanged.In a FROM or JOIN clause, format: table_name [AS] alias_name

Alias is a single word, without quotes, and contains only letters, digits, or underscores. You can not use key words, spaces, punctuation, or special characters.

> Each table’s alias name must be unique within the SQL statement.> Besides being used to associate specific column names with tables, table aliases are used to reduce statement clutter which makes the statement more easy to understood.

> A table alias does not change the table name in the table definition and it exists only for the duration of the statement.

> An alias name negates the table name for the duration of the statement. Therefore, all references to the table must use the alias name in all qualifying references.

> Alias names can also be used for table views.

As mentioned earlier, column names must be unique within each table. However, the same column name can be used in other tables. To uniquely identify names that are the same in multiple tables, we use a qualifying name.A qualified name is a table name followed by a period and the name of the column in the table.

                       Format:                         table_name.column_name

Tables must have unique names within a database. Therefore, a qualified name is unique within the database.

These two statements are functionally identical:

                    SELECT VNDR_ID,                          SELECT VENDOR_TBL.VNDR_ID
                                 VENDOR_NAME                              VENDOR_TBL.VENDOR_NAME
                       FROM VENDOR_TBL;                      FROM VENDOR_TBL;

> A column name does not need to be qualified if it is a unique name among all the column names of all the tables
used in a statement.> Qualified and unqualified names may be intermixed in a statement.

> It is a good idea to use qualified names. Then, if a matching name is added to another table in the future,
there will not be a problem with an unqualified name.

> Some RDBMS systems might require a hierarchy of qualification such as server, database, schema, owner, table.

> SQL Server requires server.database.owner.table.