HomeOracle Page 5 - Multi-Table Queries with Oracle 10G XE
Example 4. Four tables: Employees, Departments, Locations and Countries - Oracle
Queries are quantitative questions you pose to a database. Questions such as "how many employees make more than $20,000.00 a year?" or "which employee has worked for more than two years?" are typical examples. In some cases, to answer questions like the ones above, you may go no further than looking it up in one table. However some questions may need more than one table, and it is not uncommon that several tables must be used to provide the answer.
By similar reasoning, Oracle successively takes two tables at a time and creates the join as shown in the next picture. Observe the circuitous route to find the country name associated with an employee.
select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."SALARY" as "SALARY",
"DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"LOCATIONS"."CITY" as "CITY",
"COUNTRIES"."COUNTRY_NAME" as "COUNTRY_NAME"
from "COUNTRIES" "COUNTRIES",
"LOCATIONS" "LOCATIONS",
"DEPARTMENTS" "DEPARTMENTS",
"EMPLOYEES" "EMPLOYEES"
where "DEPARTMENTS"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID" and
"DEPARTMENTS"."LOCATION_ID"="LOCATIONS"."LOCATION_ID" and
"LOCATIONS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID" and
"EMPLOYEES"."SALARY" > 9000
and "LOCATIONS"."CITY" !='Seattle'
Summary
Querying multiple tables was discussed using the Query Builder tool in Oracle 10G XE. Oracle's traditional syntax for the join was discussed, which places the join in the WHERE clause. This is also the syntax for joins generated by the Query Builder tool. Table aliases are implied in the queries built using the Query Builder. In order to use shortened names for table aliases the GUI generated code may have to be modified. While column aliases can be configured, the column names instead of the aliases are generated by the code in the order by clause. The use of Venn diagrams will aid considerably in understanding the joins.
/ 14 
