Joining different tables

Joining Tables or Database Records › Joining Tables › Joining different tables

Joining different tables

Associate one column with another

To join tables together, each table must have at least one column that corresponds to a column in another table. You join tables together by equating these columns in the WHERE clause of the SELECT statement. The WHERE clause defines the join condition. This figure joins the EMP and DEPT tables by equating the department ID values in both tables:

EMP table                              DEPT table
┌──────────────────────────────────┐    ┌────────────────────────────────────┐
│ EMPNAME                  DEPTID  │    │ DEPTID    DEPTNAME                 │
│                                  │    │                                    │
│ Jennifer Lanzarotta      3100    │    │ 3100     Internal Software         │
│ Bart Elopoulos           3200    │    │ 3200     Computer Operations       │
│ Reginald Mahoney         4000    │    │ 4000     Public Relations          │
│ Duc Ho                   2000    │    │ 2000     Accounting and Payroll    │
│ Daniel Ortega            1000    │    │ 1000     Personnel                 │
│ Edith Jones              5100    │    │ 5100     Brainstorming             │
│ Anthea Poznanski         0100    │    │ 0100     Executive Administration  │
│ Ankur Sahu               5300    │    │ 5300     Blue Skies                │
│ William Sternbach        5200    │    │ 5200     Thermoregulation          │
└─────────────────┬────────────────┘    └─────────────────┬──────────────────┘
                  │                                       │
                  └─────────────────┬─────────────────────┘
                                    │
                                    │
                                    │
                                    │
Join rows in EMP table              │
and DEPT table that have DEPTID     │
equal to 5300 to list information   │
about department 5300 and its       │
employees                           │
                                    │
                                    │
        ┌───────────────────────────▼────────────────────────────┐
        │ EMPNAME                  DEPTID             DEPTNAME   │
        │                                                        │
        │ Ankur Sahu               5300               Blue Skies │
        └────────────────────────────────────────────────────────┘

Which columns can you use?

The columns should contain comparable data. For example, you could compare the EMPID column in table EMP with the MGRID column in table DEPT. Both columns contain employee ID values.

Choosing a column

In some cases, the tables you want to join will have more than one corresponding column. For example, you can join the EMP and DEPT tables by comparing:

The DEPTID column defined in both tables, or
The EMPID column defined in the EMP table and the MGRID column defined in the DEPT table

If you compare the department ID values, CA OLQ retrieves information about all employees and their departments. If you compare employee and manager ID values, CA OLQ retrieves information about all employees who are department managers and the departments they manage.

Qualifying column names

When the tables you want to join have the same names for some or all of the columns (like the DEPTID column in the EMP and DEPT tables), qualify the column names by specifying the table name, followed by a period and the column name: dept.deptid. In fact, its a good idea to qualify all column names in join operations to make the SELECT statement easier to read.

Coding the SELECT statement

To join tables in a SELECT statement:

Name selected columns from any or all of the tables in the column list following the SELECT keyword: dept.deptid, emp.lastname.
Name the tables, separated by a comma, in the FROM clause of the SELECT statement: dept, emp. The order of the tables is not important.
Compare the values of the associated columns in the WHERE clause: dept.deptid = emp.deptid. The WHERE clause can contain more than one comparison expression, as shown in Example 2 below.

Example 1— Name the department managers

List information about each department manager. The SELECT statement joins the DEPT and EMP tables by getting rows from both tables where the manager ID in the department table is the same as the employee ID in the EMP table:

select dept.deptid, dept.deptname, emp.firstname, emp.lastname
  from dept, emp
  where dept.mgrid = emp.empid ! display

DEPT/EMP REPORT mm/dd/yy DEPTID DEPTNAME FIRSTNAME LASTNAME ------ --------------------------------------------- ---------- --------------- 3100 INTERNAL SOFTWARE JENNIFER GARFIELD 4000 PUBLIC RELATIONS MONTE BANK 6666 EXECUTIVE ADMINISTRATION HENRIETTA HENDON END OF REPORT

Example 2— Name employees hired before their manager

Assuming that employee IDs are assigned sequentially, list all employees who have worked at the company longer than their manager; that is, those employees who have a lower ID than that of the department's manager. The SELECT statement joins the EMP and DEPT tables by retrieving all rows where:

The employee's department is the same as the manager's department, and

The employee's ID is less than the manager's ID

select emp.empid, emp.lastname, emp.firstname, dept.mgrid
  from emp, dept
  where (dept.deptid = emp.deptid)
  and (emp.empid < dept.mgrid) ! display

EMP/DEPT REPORT mm/dd/yy EMPID LASTNAME FIRSTNAME MGRID ------ ------------------ ----------- ------ 0001 RUPEE JOHN 0030 END OF REPORT

Joining more than two tables

If you need to join more than two tables, specify a join condition for each pair of tables. That is, to join three tables, you'll need at least two join conditions. For example, to join the EMP, DEPT, and JOBCLASS tables, you could join the tables this way:

Tables	Join condition
EMP and DEPT	dept.deptid = emp.deptid
EMP and JOBCLASS	emp.class = jobclass.class

The resulting report would contain information about each employee's department and job class.

Example— Join three tables

List job information about all employees who earn the minimum salary for their job class. The SELECT statement joins three tables: EMP, JOBLIST, and JOBCLASS. CA OLQ retrieves all rows where:

The employee ID in the EMP table matches the employee ID assigned to a job in the JOBLIST table, and
The employee's job class matches the class assigned in the JOBCLASS table, and

The employee's salary equals the minimum salary in the JOBCLASS table

select emp.empid, emp.lastname,
joblist.title, jobclass.class, emp.salary
  from emp, joblist, jobclass
  where joblist.empid=emp.empid and
  jobclass.class = emp.class and
  emp.salary = jobclass.minsalary ! display

EMP/JOBLIST/JOBCLASS REPORT mm/dd/yy EMPID LASTNAME TITLE CLASS SALARY ----- --------------- -------------------- ----- ----------- 0001 RUPEE DIR OPERATIONS 72 76000.00 0024 DOUGH PROGRAMMER/ANALYST 42 33000.00 0027 HEAROWITZ PROGRAMMER/ANALYST 42 33000.00 0029 GALLWAY PROGRAMMER/ANALYST 42 33000.00 0120 ANGELO ILLUSTRATOR 21 18000.00 0120 ANGELO PASTE-UP ARTIST 21 18000.00 0127 MCDOUGALL PASTE-UP ARTIST 21 18000.00 0476 ZEDI PR WRITER 33 37000.00 END OF REPORT