HomeOracle Page 4 - Multi-Table Queries with Oracle 10G XE
Example 3. Three tables: Employees, Departments, and Locations - 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.
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 withthe 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
/ 14 
