Joining Tables on Common Columns

Accessing Multiple Tables › Joining Tables on Common Columns

Joining Tables on Common Columns

To join two tables, you must associate one or more columns in one table to one or more columns in a second table. The joining columns must:

Exist in both tables
Have data of equivalent type
Appear in the WHERE clause

Normally, a row from one table is joined with a row from the other when the common columns contain equal values.

Statement Used

The statement used to join tables is composed of:

The columns to be displayed listed after SELECT
A FROM clause identifying the tables from which data is being retrieved
A WHERE clause indicating the column to be matched and any additional search conditions

Note: If you are joining tables from different schemas, you mustpreface the table name with the schema name.

In the WHERE clause, you specify a column name from one table, a comparison operator (usually =), and a column name from the other table.

How It's Done

Periodically, the Human Resources department produces a list of the employees who head departments.

The ID of the employee who heads a particular department is found in the DEPARTMENT table. The employee's name is found in the EMPLOYEE table. You need to join these two tables to get all the information for the list.

To join the DEPARTMENT and EMPLOYEE tables based on the head of the department, you use the DEPT_HEAD_ID column in the DEPARTMENT table and the EMP_ID column in the EMPLOYEE table. The columns have different names, but both contain employee IDs.

To join the two tables, enter:

select emp_id, emp_lname, emp_fname, dept_name
       from employee, department
       where dept_head_id = emp_id;

In this statement, all the join columns have unique names. The SELECT statement specifies that you want to see employee ID, employee last name and first name, and name of department. This information is going to come from two different tables. The common columns are matched in the WHERE clause.

The result looks like this:

EMP_ID EMP_LNAME EMP_FNAME DEPT_NAME ------ --------- --------- --------- 2004 Johnson Eleanor PURCHASING - SERVICE 1003 Baldwin James LEASING - NEW CARS 2466 Bennett Patricia MIS 2010 Parker Cora SALES - NEW CARS 3769 Donelson Julie APPRAISAL NEW CARS 2466 Bennett Patricia CORPORATE ACCOUNTING 3222 Voltmer Louise HUMAN RESOURCES 2096 Carlson Thomas MAINTENANCE 2180 Albertini Joan SALES - USED CARS 2598 Jacobs Mary BILLING 2461 Anderson Alice CORPORATE ADMINISTRATION 2209 Smith Michael APPRAISAL - SERVICE 1003 Baldwin James LEGAL 3082 Brooks John APPRAISAL - USED CARS 2246 Hamel Marylou PURCHASING - USED CARS 2894 Griffin William CORPORATE MARKETING 1765 Alexander David PURCHASING - NEW CARS 17 rows processed