Table Joins in SQL - Part I

In Oracle as data is not stored in a single table, we usually have to draw data from multiple tables together in a meaningful way. To show this data from multiple tables in one query, Oracle allows us to perform table joins. 

Here are the two rules you need to remember for table joins. Data from two (or more) tables can be joined, if the same column/s (under the same or a different name) appears in both tables, and the column/s is the primary key (or part of that key) in one of the tables. Having a common column in two tables implies a relationship between the two tables. The nature of that relationship is determined by which table uses the column/s as a primary key. This begs the question, what is a primary key? A primary key is a column in a table used for identifying the uniqueness of each row in a table. The table in which the column appears as a primary key is referred to as the parent table in this relationship (sometimes also called the master table), whereas the column that references the other table in the relationship is often called the child table (sometimes also called the detail table). The common column appearing in the child table is referred to as a foreign key.

To illustrate this, let us look two of the most frequently used tables in Oracle HRMS viz – PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F. As highlighted the table per_all_people_f has the combination of person_id, effective_start_date and effective_end_date as its primary key. Table per_all_assignments_f has assignment_id, effective_start_date and effective_end_date as its primary key. Here the common column person_id is foreign key in the child table per_all_assignments_f.

JOIN SYNTAX

Let's now look at a simple join statement using the Oracle traditional syntax, where we join the contents of the people and assignment tables together to obtain a listing of all employees, along with their assignment details

select *

from    per_all_people_f papf,

          per_all_assignments_f paaf

where paaf.person_id = papf.person_id

Note the many important components in this table join. Listing two tables in the ‘from’ clause clearly indicates that a table join is taking place. Note also that each table name is followed by a word: papf for people table and paaf for assignment table. This demonstrates an interesting concept—just as columns can have aliases, so too can tables. The aliases serve an important purpose—they prevent Oracle from getting confused about which table to use when listing the data in the common columns. Remember, both people and assignment tables have several columns with same name like – effective_start_date, effective_end_date, person_id, etc. You can also avoid ambiguity in table joins by prefixing references to the columns with the table names, but this often requires extra coding. You can also give the column two different names, but then you might forget that the relationship exists between the two tables. It's just better to use aliases! 

PREVIOUS POST || NEXT POST