Home arrow Oracle arrow Page 3 - Multi-Table Queries with Oracle 10G XE

Example 2. Two Tables: Join Employees and Departments tables - 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.

TABLE OF CONTENTS:
  1. Multi-Table Queries with Oracle 10G XE
  2. Example 1. Single Table queries
  3. Example 2. Two Tables: Join Employees and Departments tables
  4. Example 3. Three tables: Employees, Departments, and Locations
  5. Example 4. Four tables: Employees, Departments, Locations and Countries
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 14
November 29, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jayaram Krishnaswamy
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: