Snippet Name: 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.
Also see:
» BETWEEN Condition
» LIKE Condition
» Combining the AND and OR Conditions
» OR Condition
» AND Condition
» WHERE Clause: Conditions
» Having Clause
Comment: (none)
Language:
Highlight Mode: PLSQL
Last Modified: March 07th, 2009
|
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
|
