Outer Join in oracle

Can anyone give me an example of outer join in oracle? Please give me 1 or 2 examples to clear my doubts.

 

Let's use the following table structure to do some examples:

tblUsers
------------------------------
userID int (pk)
username varchar(35)
------------------------------

tblPosts
------------------------------
postID int (pk)
userID int (fk)
postContent text
------------------------------

Let's also assume the following data:

tblUsers
userID username
------------------------------
1 fred
2 bob
3 cat
4 wooly
5 darkness

tblPosts
postID userID postContent
------------------------------
1 2 aaaaa
2 4 bbbbb
3 4 ccccc
4 1 ddddd
5 2 eeeee
6 6 fffff

Note that tblPosts includes a record for a userID that either has not been created or has been deleted. This will be important later. In my examples, I show SQL for Oracle 8i and 9i. The 8i queries should work in 9i, but it wasn't until 9i that joins were ANSI compliant in Oracle. So, the 9i queries will not work in 8i.

An inner join returns rows from two tables where there are rows in both tables that meet the join criteria.

Inner Join
8i SQL> SELECT u.username, p.postContent FROM tblUsers u, tblPosts p WHERE u.userID = p.userID;
9i SQL> SELECT u.username, p.postCount FROM tblUsers u INNER JOIN tblPosts p ON u.userID = p.userID;

Results>
fred : ddddd
bob : aaaaa
bob : eeeee
wooly : bbbbb
wooly : ccccc

Note, cat and darkness were not returned because there was no matching row in tblPosts. Also note that fffff was not included because there was no matching record in tblUsers.

Outer joins are unidirectional. An outer join returns all rows from the selected table regardless of whether there is a matching row in the other table. It does not return rows from the other table if there is no match in the selected table. A table is selected by putting a '(+)' after the join criteria from that table. First I will do an outer join selecting tblUsers, then an outer join selecting tblPosts.

Outer Join on tblUsers
8i SQL> SELECT u.username, p.postContent FROM tblUsers u, tblPosts p WHERE u.userID (+) = p.userID;
9i SQL> SELECT u.username, p.postCount FROM tblUsers u LEFT OUTER JOIN tblPosts p ON u.userID = p.userID;

Results>
fred : ddddd
bob : aaaaa
bob : eeeee
cat :
wooly : bbbbb
wooly : ccccc
darkness :

Note how cat and darkness were included this time, even thought there are no posts by them.

Outer Join on tblPosts
8i SQL> SELECT u.username, p.postContent FROM tblUsers u, tblPosts p WHERE u.userID = p.userID (+);
9i SQL> SELECT u.username, p.postCount FROM tblUsers u RIGHT OUTER JOIN tblPosts p ON u.userID = p.userID;

Results>
fred : ddddd
bob : aaaaa
bob : eeeee
wooly : bbbbb
wooly : ccccc
: fffff

Note how fffff was included even though there was no user associated with that post.

There is also a full outer join. Full outer joins became available in Oracle9i. If you are running an earlier version of Oracle you will not be able to run this code. A full outer join will get rows from tables regardless of whether there is a match either way. This is a bidirectional join.

Full Outer Join
9i SQL> SELECT u.username, p.postCount FROM tblUsers u FULL OUTER JOIN tblPosts p ON u.userID = p.userID;

Results>
fred : ddddd
bob : aaaaa
bob : eeeee
cat :
wooly : bbbbb
wooly : ccccc
darkness :
: fffff

Note that now all rows in both tables are accounted for. It is possible to get the same results in Oracle 8i using the following syntax:

8i SQL>
SELECT u.username, p.postContent FROM tblUsers u, tblPosts p WHERE u.userID (+) = p.userID
UNION
SELECT u.username, p.postContent FROM tblUsers u, tblPosts p WHERE u.userID = p.userID (+);

The union essentially joins the two join statements. In 8i this must be done because the (+) operator is unidirectional and will throw an error if you try to apply it to both criteria of the join. This query should give you the same results as a full outer join.

My result sets are accurate in what data they would have, but the order may be off.