Multi-Table Queries with Oracle 10G XE

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.

The strength of relational databases is in providing answers to such cases where several tables may be employed. Whereas writing a SELECT query against a table is quite straightforward, to write a query addressing several tables requires joins or sub-queries. This tutorial looks at multiple table queries using joins. The discussions are mostly in relation to the Oracle 10G XE, the little Hercules, and its graphical interface for building queries. The GUI generated SQL statement uses Oracle’s traditional syntax for the joins.

This tutorial assumes that the following tutorials have been reviewed by the reader as some of the related information has already been described:

Tables from Oracle 10G XE used in this tutorial

This tutorial will be using the Employees, Departments, Locations and Countries tables in the hr database to show how multiple tables are queried. The tutorial will mostly look at the SELECT cause which must always be accompanied by the FROM clause. The SELECT clause can select columns, columns with aliases, expressions, aggregates, and so forth. The FROM Clause shows in which of the table(s) the columns are to be found, the columns that participate in the aggregates, and so on. In the case of columns coming from multiple tables, there are two ways to retrieve them, the traditional way (Oracle Syntax), or the ISO/ANSI SQL 1999 standard. These differ in the way the join — or the manner in which the relationship that exists between the tables — is leveraged in retrieving the columns. Although only tables have been mentioned, it is also possible to include views in the queries.

We will begin with single table queries and develop multiple table queries after we get used to working with Oracle 10G XE’s Query Builder. But first, let us look at some of the tables used in our tutorial. The next four pictures show the design view of the four tables. Pay attention to the Primary keys and Foreign keys in the different tables. It is these keys that relate the tables and help us in identifying common columns that relate the tables.

Employees Table

The Employees table has Employee_ID as the primary key and possibly a couple of other foreign keys with the ID suffix. These are possible primary keys of other tables.

Departments Table

The Departments table has DEPARTMENT_ID as the primary key and possibly a couple of other foreign keys with the ID suffix. These are possible primary keys of other tables. The Departments table has a column labeled LOCATION_ID and in the next screen shot, if you were to click on the Constraints tab, you will see that it is a foreign key.

Locations table

The Locations table has LOCATION_ID as the primary key. In the Departments table the LOCATION_ID is a foreign key.

{mospagebreak title=Example 1. Single Table queries}

Using the Employees table in the hr database in Oracle 10G XE, the single table query will be demonstrated. Queries can be designed in Oracle 10g XE by the query builder which may be accessed as shown from the Home page.

The single table query will use the built-in Query designer which can be accessed from Home–>SQL–>Query Builder as shown in the following picture. You may choose the columns you want to return in the result as well as any filtering and ordering you want by making appropriate changes in the designer as shown. After making the changes run the query by hitting the RUN button. If you are developing queries that may end up in something complicated, it may be a good idea to save them and make changes to the saved queries as you go along.

The corresponding SQL statement generated by the machine is shown in the next paragraph. The double reference to the "Employees" is because of the lack of support for setting the table alias in the GUI. However, the SQL generated by the GUI adds the table alias. Although Oracle 10G XE shows the table alias for a single table without joins, it is not usual to show the alias.

select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
	 "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
	 "EMPLOYEES"."LAST_NAME" as "LAST_NAME",
	 "EMPLOYEES"."PHONE_NUMBER" as "PHONE_NUMBER",
	 "EMPLOYEES"."SALARY" as "SALARY" 
 from "EMPLOYEES" "EMPLOYEES" where 	 "EMPLOYEES"."SALARY" >9000
order by EMPLOYEES.LAST_NAME DESC

The result returned by this query is shown in the next picture as you may verify for yourself. The filtering and the ordering are in accordance with your choice.

In addition to the table alias which is implicit, you may also use the column alias, which may be used for column ordering but not in the column condition as shown in the SQL statement generated by the GUI. Note that although the GUI generated the alias for the columns in the SQL, it retained the original column names in the order by clause. Ordering is specified over multiple columns — in this case two columns.

select	 "EMPLOYEES"."EMPLOYEE_ID" as "Emp_ID",
	 "EMPLOYEES"."FIRST_NAME" as "Fname",
	 "EMPLOYEES"."LAST_NAME" as "Lname", 
"EMPLOYEES"."PHONE_NUMBER" as "Phone", "EMPLOYEES"."SALARY" as "Salary" from "EMPLOYEES" "EMPLOYEES" where "EMPLOYEES"."SALARY" >9000 order by EMPLOYEES.LAST_NAME DESC, EMPLOYEES.FIRST_NAME DESC

The result returned by the query is shown in the next picture.

The GUI does not substitute the column alias in the order by clause, but you may run this select statement in the SQL Command window by substituting "EMPLOYEES"."LAST_NAME by "Lname", and "EMPLOYEES" "FIRST_NAME" by "Fname" to obtain the same result.

{mospagebreak title=Example 2. Two Tables: Join Employees and Departments tables}

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.

{mospagebreak title=Example 3. Three tables: Employees, Departments, and Locations}

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

{mospagebreak title=Example 4. Four tables: Employees, Departments, Locations and Countries}

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.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye