Joining columns in two or more tables

You can use a SELECT statement to join columns in two or more tables.

The SQL statement in the following figure uses data from the Q.STAFF and Q.ORG tables to select all the clerks in the Eastern division.

If you check the sample tables, you see that the department numbers are found in both tables, the division name is in the Q.ORG table, and the job title is in the Q.STAFF table. In Q.ORG, the department number is in the DEPTNUMB column, and in Q.STAFF, the department number is in the DEPT column. You will join the tables by these two columns.

Specify all the columns you want to display on the report in the SELECT clause. Use the FROM clause to specify the tables you want to join. Specify the columns whose values are equal in the WHERE clause, separated by an equal (=) sign.

Figure 1. A SQL query that joins the Q.STAFF and Q.ORG tables

SELECT DIVISION, ID, LOCATION, NAME
  FROM Q.STAFF, Q.ORG
  WHERE DIVISION = 'EASTERN'
    AND JOB='CLERK'
    AND DEPTNUMB = DEPT
  ORDER BY ID

The report in the following figure displays when you run the query. If you do not specify a common column when you join two tables, each row in the first table is joined to each row in the second table. The resulting report might contain duplicate data and might be very large.

Figure 2. The report shows the data from both tables.

   DIVISION        ID  LOCATION       NAME
   ----------  ------  -------------  ---------
   EASTERN         80  WASHINGTON     JAMES
   EASTERN        110  BOSTON         NGAN
   EASTERN        120  ATLANTA        NAUGHTON
   EASTERN        170  BOSTON         KERMISCH
   EASTERN        180  ATLANTA        ABRAHAMS
   EASTERN        190  WASHINGTON     SNEIDER

The columns in the tables you are joining might have the same name. Use one of the following methods to distinguish between columns with the same name:

Add a qualifier to the column name
Specify a correlation name to identify a column with a particular table

Using qualifiers to distinguish between column names

You can add a qualifier to identical column names to identify the table from which you selected the column.

For example, to distinguish between the PRODNUM column in the Q.PRODUCTS table, and the PRODNUM column in the Q.PROJECT table, add the following qualifiers to the column names:

Add Q.PRODUCTS to the PRODNUM column from the PRODUCTS table
Add Q.PROJECT to the PRODNUM column from the PROJECT table

The SQL statement in the following figure selects all the product numbers in both the Q.PRODUCTS and Q.PROJECT tables, as well as the project numbers, departments, and product prices.

You only need to specify one of the duplicate column names when you select columns because the two columns are combined in the report. Use a qualifier for duplicate column names everywhere you refer to them in the query.

Figure 3. This SQL query selects data from two columns with the same name.

  SELECT PROJNO, Q.PRODUCTS.PRODNUM, DEPT, PRODPRICE
   FROM Q.PROJECT, Q.PRODUCTS
   WHERE Q.PRODUCTS.PRODNUM < 100 AND
   Q.PRODUCTS.PRODNUM = Q.PROJECT.PRODNUM

Using correlation names to distinguish between column names

Correlation names are used to identify the tables or views from which you selected columns when more than one column has the same name.

For example, to distinguish between the PRODNUM column in the Q.PRODUCTS table and the PRODNUM column in the Q.PROJECT table, you could specify a correlation name of P for Q.PROJECT and a correlation name of S for Q.PRODUCTS.

Use the correlation name as a prefix to the column name wherever you refer to that column. The query in the following figure shows examples of using correlation names:

Figure 4. Using correlation names in a query

SELECT PROJNO, S.PRODNUM, DEPT, PRODPRICE
  FROM Q.PROJECT P, Q.PRODUCTS S
  WHERE S.PRODNUM < 100 AND
  S.PRODNUM = P.PRODNUM