Multi-Table Queries with Oracle 10G XE - Example 4. Four tables: Employees, Departments, Locations and Countries

(Page 5 of 5 )

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Multi-Table Queries with Oracle 10G XE

· This is a continuation of the usage of the query builder tool in Oracle 10g XE....   >>> Post your comment now!