Command Discussion

A join operation pulls data from two or more tables listed in the FROM clause. These tables represent the source of the data to be joined.
The WHERE clause specifies the relationship between the tables to be joined. This relationship represents the join condition. Typically, the join condition expresses a relationship between rows from each table that match on a common attribute.
When the tables to be joined have the same column name, the column name is prefixed with a table name in order for Texis to know from which table the column comes. Texis uses the notation:
```
table-name.column-name
```
The table name in front of the column name is referred to as a qualifier.
The common attributes in the join condition need not have the same column name, but they should represent the same kind of information. For example, where the attribute representing names of people submitting resumes was named RNAME in table 1, and the attribute for names of employees was named ENAME in table 2, you could still join the tables on the common character field by specifying:
```
WHERE table-name1.RNAME = table-name2.ENAME
```
While the above is true, it is still a good rule of thumb in database design to give the same name to all columns referring to data of the same type and meaning. Columns which are designed to be a key, and intended as the basis for joining tables would normally be given the same name.
If a row from one of the tables never satisfies the join condition, that row will not appear in the joined table.
The tables are joined together, and then Texis extracts the data, or columns, listed in the SELECT clause.
Although tables can be combined if you omit the WHERE clause, this would result in a table of all possible combinations of rows from the tables in the FROM clause. This output is usually not intended, nor meaningful, and can waste much computer processing time. Therefore, be careful in forming queries that involve multiple tables.

Example: The corporate librarian wants to distribute a list of authors who have contributed reports to the corporate library, along with the name of that author's department. To fulfill this request, data from both the REPORT table (author) and the DEPARTMENT table (department name) are needed.

You would enter this statement:

SELECT   AUTHOR, DNAME
     FROM     REPORT, DEPARTMENT
     WHERE    REPORT.DEPT = DEPARTMENT.DEPT ;

Syntax Notes:

REPORT and DEPARTMENT indicate the tables to be joined.
The WHERE clause statement defines the condition for the join.
The notation "REPORT." in "REPORT.DEPT", and "DEPARTMENT." in "DEPARTMENT.DEPT" are the qualifiers which indicate from which table to find the column.

This statement will result in the following joined table:

AUTHOR                   DNAME
  Jackson, Herbert         Research and Development
  Sanchez, Carla           Product Marketing and Sales
  Price, Stella            Finance and Accounting
  Smith, Roberta           Research and Development
  Aster, John A.           Product Marketing and Sales
  Jackson, Herbert         Research and Development
  Barrington, Kyle         Management and Administration

In this query, we are joining data from the REPORT and the DEPARTMENT tables. The common attribute in these two tables is the department code. The conditional expression:

REPORT.DEPT = DEPARTMENT.DEPT

is used to describe how the rows in the two tables are to be matched. Each row of the joined table is the result of combining a row from the REPORT table and a row from the DEPARTMENT table for each comparison with matching codes.

To further illustrate how the join works, look at the rows in the REPORT table below where DEPT is "MKT":

TITLE                    AUTHOR           DEPT FILENAME
  Disappearing Ink         Jackson, Herbert RND  /data/rnd/ink.txt
> INK PROMOTIONAL CAMPAIGN SANCHEZ, CARLA   MKT  /data/MKT/PROMO.RPT
  Budget for 4Q 92         Price, Stella    FIN  /data/ad/4q.rpt
  Round Widgets            Smith, Roberta   RND  /data/rnd/widge.txt
> PAPERCLIPS               ASTER, JOHN A.   MKT  /data/MKT/CLIP.RPT
  Color Panorama           Jackson, Herbert RND  /data/rnd/color.txt
  Meeting Schedule         Barrington, Kyle MGT  /data/mgt/when.rpt

Now look at the rows in the DEPARTMENT table below where DEPT is "MKT". These are matching rows since the department code ("MKT") is the same.

DEPT DNAME                               DHEAD      DIV  BUDGET
  MGT  Management and Administration       Barrington CORP 22000
  FIN  Finance and Accounting              Price      CORP 26000
  LEG  Corporate Legal Support             Thomas     CORP 28000
  SUP  Supplies and Procurement            Sweet      CORP 10500
  REC  Recruitment and Personnel           Harris     CORP 15000
  RND  Research and Development            Jones      PROD 27500
  MFG  Manufacturing                       Washington PROD 32000
  CSS  Customer Support and Service        Ferrer     PROD 11000
> MKT  PRODUCT MARKETING AND SALES         BROWN      PROD 25000
  ISM  Information Systems Management      Dedrich    INFO 22500
  LIB  Corporate Library                   Krinski    INFO 18500
  SPI  Strategic Planning and Intelligence Peters     INFO 28500

The matching rows can be conceptualized as combining a row from the REPORT table with a matching row from the DEPARTMENT table. Below is a sample of rows from both tables, matched on the department code "MKT":

DEPT DNAME     DHEAD DIV  BUDGET TITLE      AUTHOR  FILENAME
MKT  Marketing Brown PROD 25000  Ink        Sanchez /data/mkt/promo.rpt
MKT  Marketing Brown PROD 25000  Paperclips Aster   /data/mkt/clip.rpt

This operation is carried out for all matching rows; i.e., each row in the REPORT table is combined, or matched, with a row having the same department code in the DEPARTMENT table:

DEPT DNAME      DHEAD DIV  BUDGET TITLE      AUTHOR  FILENAME
RND  Research   Jones PROD 27500  Ink        Jackson /data/rnd/ink.txt
MKT  Marketing  Brown PROD 25000  Ink Promo  Sanchez /data/mkt/promo.rpt
FIN  Finance    Price CORP 26000  Budget     Price   /data/ad/4q.rpt
RND  Research   Jones PROD 27500  Widgets    Smith   /data/rnd/widge.txt
MKT  Marketing  Brown PROD 25000  Paperclips Aster   /data/mkt/clip.rpt
RND  Research   Jones PROD 27500  Panorama   Jackson /data/rnd/color.txt
MGT  Management Barri CORP 22000  Schedule   Barring /data/mgt/when.rpt

The columns requested in the SELECT statement determine the final output for the joined table:

AUTHOR                   DNAME
  Jackson, Herbert         Research and Development
  Sanchez, Carla           Product Marketing and Sales
  Price, Stella            Finance and Accounting
  Smith, Roberta           Research and Development
  Aster, John A.           Product Marketing and Sales
  Jackson, Herbert         Research and Development
  Barrington, Kyle         Management and Administration

Observe that the joined table does not include any data on several departments from the DEPARTMENT table, where that department did not produce any contributing authors as listed in the REPORT table. The joined table includes only rows where a match has occurred between rows in both tables. If a row in either table does not match any row in the other table, the row is not included in the joined table.

In addition, notice that the DEPT column is not included in the final joined table. Only two columns are included in the joined table since just two columns are listed in the SELECT clause, and DEPT is not one of them.

The next example illustrates that conditions other than the join condition can be used in the WHERE clause. It also shows that even though the results come from a single table, the solution may require that data from two or more tables be joined in the WHERE clause.

Example: Assume that you cannot remember the department code for Research and Development, but you want to know the titles of all reports submitted from that department.

Enter this statement:

SELECT   TITLE
     FROM     DEPARTMENT, REPORT
     WHERE    DNAME = 'RESEARCH AND DEVELOPMENT'
       AND    REPORT.DEPT = DEPARTMENT.DEPT ;

Syntax Notes:

The tables to be joined are listed after FROM.
The condition for the join operation is specified after AND (as part of WHERE).

The results follow:

TITLE
  Innovations in Disappearing Ink
  Improvements in Round Widgets
  Ink Color Panorama

Since you don't know Research and Development's department code, you use the department name found in the DEPARTMENT table in order to find the row that stores Research and Development's code, which is `RND'. Conceptually, visualize the join operation to occur as follows:

The conditional expression DNAME = 'RESEARCH AND DEVELOPMENT' references one row from the DEPARTMENT table; i.e., the `RND' row.
Now that the RND code is known, this row in the DEPARTMENT table is joined with the rows in the REPORT table that have DEPT = RND. The joined table represents the titles of the reports submitted by authors from the Research and Development department.

As the next example illustrates, more than two tables can be joined together.

Example: Provide a list of salaries paid to those people in the Product Division who contributed reports to the Corporate Library. The report should include the author's name, department name, and annual salary.

You would enter this statement:

SELECT   AUTHOR, DNAME, SALARY
     FROM     REPORT, DEPARTMENT, EMPLOYEE
     WHERE    DEPARTMENT.DIV = 'PROD'
       AND    REPORT.DEPT = DEPARTMENT.DEPT
       AND    REPORT.DEPT = EMPLOYEE.DEPT ;

Syntax Notes:

The order of the joins in the WHERE clause is not important.
The three tables to be joined are listed after FROM.
The first AND statement (in WHERE clause) is the condition for joining the REPORT and DEPARTMENT tables.
The second AND statement (in WHERE clause) is the condition for joining the REPORT and EMPLOYEE tables.
While department code happens to be a column which all three tables have in common, it would be possible to join two tables with a common column, and the other two tables with a different common column, such as ENAME in the EMPLOYEE table and AUTHOR in the REPORT table. (The latter would not be as efficient, nor as reliable, so department name was chosen instead.)

The results would be:

AUTHOR               DNAME                           SALARY
  Jackson, Herbert     Research and Development        30000
  Sanchez, Carla       Product Marketing and Sales     35000
  Smith, Roberta       Research and Development        25000
  Aster, John A.       Product Marketing and Sales     32000

In this example, data from three tables (REPORT, DEPARTMENT, EMPLOYEE) are joined together.

Conceptually, the DEPARTMENT table references the rows that contain PROD; this gives us the departments in the Product Division. The departments in the Product Division (RND, MFG, CSS, MKT) are matched against the departments in the DEPT column of the REPORT table. The tables are joined for the Research and Development (RND) and Product Marketing and Sales (MKT) departments. This yields an intermediate table containing all the columns from both the DEPARTMENT and REPORT tables for RND and MKT rows.

This intermediate table is joined with the EMPLOYEE table, based on the second join condition REPORT.DEPT = EMPLOYEE.DEPT to form a combination of columns from all 3 tables, for the matching rows.

Finally, the SELECT clause indicates which columns in the intermediate joined table that you want displayed. Thus the author, department name, and annual salary are shown as in the above example.

As a final point, the order in which you place the conditions in the WHERE clause does not affect the way Texis accesses the data. Texis contains an "optimizer" in its underlying software, which chooses the best access path to the data based on factors such as index availability, size of tables involved, number of unique values in an indexed column, and other statistical information. Thus, the results would not be affected by writing the same query in the following order:

SELECT   AUTHOR, DNAME, SALARY
     FROM     REPORT, DEPARTMENT, EMPLOYEE
     WHERE    REPORT.DEPT = EMPLOYEE.DEPT
       AND    REPORT.DEPT = DEPARTMENT.DEPT
       AND    DEPARTMENT.DIV = 'PROD' ;