Table Joins in SQL – Part III

Moving on, let us see the different types of Oracle joins - 

• Oracle INNER JOIN (or sometimes called simple join)
• Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

So let's discuss Oracle JOIN syntax, look at visual illustrations of Oracle JOINS, and explore them with help of examples.

INNER JOIN (SIMPLE JOIN)

Chances are, you've already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.

select *

from   per_all_people_f papf,

          per_all_assignments_f paaf

where papf.person_id = paaf.person_id

This Oracle INNER JOIN example would return all rows from the people and assignment tables where there is a matching person_id value in both the people and assignment tables.

OUTER JOINS

Outer joins extend the capacity of Oracle queries to include handling of situations where you want to see information from tables even when no corresponding records exist in the common column.  The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values. A (+) operator is required either on left or right side of the join condition depending on “NULL” values to be fetched from which table.

LEFT OUTER JOIN (or simply LEFT JOIN)

This type of join returns all rows from the LEFT-hand table specified in the condition and only those rows from the other table where the joined fields are equal (join condition is met).

select 

paaf.assignment_number,

paaf.job_id "assg_job_id",

paaf.person_id,

pj.job_id,

pj.name "designation"

from  per_jobs pj,

        per_all_assignments_f paaf

where pj.job_id = paaf.job_id(+)

order by paaf.assignment_number

The above query will return all rows of assignment table and only those rows of per_jobs table where the join condition is met

RIGHT OUTER JOIN (or simply RIGHT JOIN)

This type of join returns all rows from the RIGHT-hand table specified in the condition and only those rows from the other table where the joined fields are equal (join condition is met).

select 

paaf.assignment_number,

paaf.location_id “assg_location_id”,

paaf.person_id,

hla.location_id,

hla.location_code “Location Name”

from  per_all_assignments_f paaf,

        hr_locations_all hla

where paaf.location_id = hla.location_id (+)

order by paaf.assignment_number

The above query will return all rows of location table and only those rows of assignment table where the join condition is met

FULL OUTER JOIN (or simply FULL JOIN)

This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.The FULL OUTER JOIN example can only be written using a UNION query

.

select location_id from per_all_assignments_f

UNION

select location_id from hr_locations_all

The above query will return all records of both the tables 

PREVIOUS POST || NEXT POST