Where Clause – Joins

Description: The WHERE Clause servers two different purposes. One is to join two or more tables, view, or materialized views for DML.

SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev;
 
SELECT p.last_name, r.role_name
FROM person p, person_role_ie i, person_role r
WHERE p.person_id = i.person_id
AND i.role_id = r.role_id;
 
Left Outer Join
 
SELECT p.last_name, t.title_name
FROM person p, role r
WHERE p.title_1 = r.title(+);
 
Returns ALL records FROM role TABLE AND ALL matching records FROM person TABLE
 
 
 
Right Outer Join
 
SELECT p.last_name, t.title_name
FROM person p, role r
WHERE p.title_1 (+)= r.title;
 
Returns ALL records FROM person TABLE AND ALL matching records FROM role TABLE

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.