Change theme: 
Search for :    
Previous Blogs
Transformers Blogs
Misc Blogs
Computer/Tech Logs
Movie Reviews
Popular Blogs
ASCII chart
Comment from Tamera on December 24, 2015
Configuring Speedstream 6520 For Modem Only Mode
Comment from Janet on September 13, 2015
Transformers Reviews: MP-19 Smokescreen
Comment from Silagra on August 1, 2015
Transformers Reviews: MP-21 Masterpiece (Bumble) Bee
Comment from google authorship check on June 26, 2015
Aww...It's a Cat...Meow
Comment from vxt on June 4, 2015


RSS Facebook Twitter Instagram
My Tweets

Oracle Left Outer Join With Additional Conditions Using (+)
Sometimes you might want to add a second condition to a left outer join. This is pretty straight forward if you know how to do SQL queries. However, Oracle has a short hand notation in which you can use the (+), which is not so straight forward.

Lets say you have two tables:

table1

AB
11
12
13
22
31
41
1null

table2

AB
11
22
31

This is how you would normally do a left outer join with an additional condition:

select t1.a as t1a, t1.b as t1b, t2.a as t2a, t2.b as t2b from table1 t1
left join table2 t2
on t1.a=t2.a
and t2.b=2

Results:

T1AT1BT2AT2B
11nullnull
12nullnull
13nullnull
2222
31nullnull
41nullnull
1nullnullnull

What this will do is select all the rows in table1, regardless of whether it finds a t2.a that equals t1.a. If it doesn't find a t2.a that matches t1.a for a specifc row, all columns for table2 will return null values. But, there is a second condition, t2.b=2. What this does is that it filters for only where t2.b has a value of 2 but still maintains the left outer join.

To do this with the (+) notation in Oracle:

select t1.a as t1a, t1.b as t1b, t2.a as t2a, t2.b as t2b from table1 t1, table2 t2
where t1.a=t2.a(+)
and t2.b(+)=2

If you ran the above query without the (+) in the second condition:

select t1.a as t1a, t1.b as t1b, t2.a as t2a, t2.b as t2b from table1 t1, table2 t2
where t1.a=t2.a(+)
and t2.b=2

Results:

T1AT1BT2AT2B
2222

The above is actually equivalent to:

select t1.a as t1a, t1.b as t1b, t2.a as t2a, t2.b as t2b from table1 t1
left join table2 t2
on t1.a=t2.a
where t2.b=2

The difference between the two is that the above query pretty much negates the left join because after the left join, it applies the where condition which will remove any rows that do not contain t2.b=2.
Comments

Post a Comment

Name:    
Email:    
Website:    
Commentssmileys
Hmm... type|in|the number 96.Please answer the question shown

Copyright © Viet Tran. All Rights Reserved. Powered by Content ManagerTM | Top