Multi-Table Queries with Oracle 10G XE - Example 3. Three tables: Employees, Departments, and Locations
(Page 4 of 5 )
In the case of three or more tables, Oracle joins two tables based on the join condition. The result of this will be joined with the third tables based on a different join condition as shown in the next listing. In the first join, it used the common column DEPARTMENT_ID; in joining the result with the Location tables it makes use of the common column LOCATION_ID as shown in the diagram.
select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."SALARY" as "SALARY",
"DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"LOCATIONS"."POSTAL_CODE" as "POSTAL_CODE",
"LOCATIONS"."CITY" as "CITY"
from "LOCATIONS" "LOCATIONS",
"DEPARTMENTS" "DEPARTMENTS",
"EMPLOYEES" "EMPLOYEES"
where "EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
and "DEPARTMENTS"."LOCATION_ID"="LOCATIONS"."LOCATION_ID" and
"EMPLOYEES"."SALARY" > 9000
The amount of text to be typed for queries can be greatly reduced by using table and column aliases. Aliases make the code easier to read. The result of either of these yields the same result shown in the next picture. In the present case all the tables are from the same schema. When they come from different schemas, the tables have to be referred to together with the schema name, as schemaName.tableName. For referring to the columns in this case, you prefix the table and schema names, schemaName.tableName.columnName, provided the table name has been defined with its schema name. However, when the table alias is used, it is wrong to use the column name with the table name.
In the following query the letters e, d, and l (aliases) stand respectively for the tables "EMPLOYEES," "DEPARTMENTS" and "LOCATIONS."
select e."EMPLOYEE_ID" as "EMPLOYEE_ID",
e."FIRST_NAME" as "FIRST_NAME",
e."LAST_NAME" as "LAST_NAME",
e."SALARY" as "SALARY",
d."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
l."POSTAL_CODE" as "POSTAL_CODE",
l."CITY" as "CITY"
from "LOCATIONS" l,
"DEPARTMENTS" d,
"EMPLOYEES" e
where e."DEPARTMENT_ID"=d."DEPARTMENT_ID"
and d."LOCATION_ID"=l."LOCATION_ID"
and e."SALARY" > 9000
Next: Example 4. Four tables: Employees, Departments, Locations and Countries >>
More Oracle Articles
More By Jayaram Krishnaswamy
