• RawNotes
• database

Author

Christopher Marshall (christopherlmarshall@yahoo.com)

Raw Notes on Database Techniques

# table t1 with columns a, b and c
t1
a b c
a 1 1
a 2 2
a 3 3

# table t2 with columns b, c and d
t2
b c d
2 4 d
4 1 d
5 5 d


# a full outer join on the condition that t1.b==t2.b:
select * from t1 full outer join t2 on t1.b==t2.b;
# yields:
t1    t2
a b c a b c
a 1 1 - - -
a 2 2 2 4 d
a 3 3 - - -
- - - 4 1 d
- - - 5 5 d

# while a full outer join on the condition that t1.c==t2.c:
select * from t1 full outer join t2 on t1.b==t2.b;
# yields:
t1    t2
a b c a b c
a 1 1 4 1 d
a 2 2 - - -
a 3 3 - - -
- - - 2 4 d
- - - 5 5 d

# so the join condition affects which rows of the cross-join are combined into
# a single row


# standard sql syntax for:
select * from t1 left outer join t2 on t1.b==t2.b;
# becomes, in oracle syntax:
select * from t1,t2 where t1.b=t2.b(+)
# so you put a (+) after the table that you may not be including all the rows of