Multi-Table Queries with Oracle 10G XE - Example 2. Two Tables: Join Employees and Departments tables
(Page 3 of 5 )
If you review the Employees table and the Departments table you will observe that the column common to both of them is the DEPARTMENT_ID. It is the primary key of the Departments table and the foreign key of the Employees table. This means that many employees may belong to the same department. Since two tables are involved, the FROM clause lists both tables, showing their aliases as well. The relationship in the query builder is as shown in the next picture. In the GUI the connecting line may not actually show the two DEPARTMENT_IDs as being joined, but you may place the mouse on the connecting line which shows the join condition.
The query which is configured in the query Builder is shown in the next paragraph as a machine generated SQL select statement. It also has the additional where clause for filtering employees with salary > 4800. This is an example of a Complex join. A Simple join, also known as an inner join, returns just the rows for which the equality of the join condition is satisfied.
select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."EMAIL" as "EMAIL",
"EMPLOYEES"."PHONE_NUMBER" as "PHONE_NUMBER",
"EMPLOYEES"."HIRE_DATE" as "HIRE_DATE",
"EMPLOYEES"."SALARY" as "SALARY",
"DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"DEPARTMENTS"."MANAGER_ID" as "MANAGER_ID"
from "DEPARTMENTS" "DEPARTMENTS",
"EMPLOYEES" "EMPLOYEES"
where "EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
and "EMPLOYEES"."SALARY" > 4800
Also as described in the earlier tutorials you may create the left outer and the right outer joins. Depending on the data in the tables you may get different number of rows returned by the queries. For example with the left outer join you get 57 rows, but for the right outer join you will get 58 rows. To understand the outer joins the Venn diagrams are very useful. This is especially true if a greater number of tables are involved in the join. We will consider the various kinds of possible joins including the 'Full Outer' join when we return to this topic using the ANSI syntax.
Next: Example 3. Three tables: Employees, Departments, and Locations >>
More Oracle Articles
More By Jayaram Krishnaswamy
