Home » SQL & PL/SQL » SQL & PL/SQL » Outer joins and NVL function
| Outer joins and NVL function [message #17400] |
Tue, 11 January 2005 00:05  |
Will
Messages: 32
Registered: January 2000
|
Member |
|
|
Hi there,
just wondering how would the following request behave :
select *
from my_table_1,
my_table_2
where my_table_1.column_1(+)=my_table_2.column_1
and nvl(my_table_1.column_2,'X')=nvl(my_table_2.column_2,'X');
That is, an outer join with a regular join (except for the nvl function) right after it. I don't think one can mix nvl function with outer joins (i.e nvl(x,'blah')(+)=nvl(y,'blih')), am I right ?
|
|
|
|
|
|
| Re: Outer joins and NVL function [message #17404 is a reply to message #17400] |
Tue, 11 January 2005 03:07  |
Tony Andrews
Messages: 29
Registered: January 2005
|
Junior Member |
|
|
The query will work, as Preeti has already said.
This is what it will do:
1) Outer join to my_table_1 based on column_1 only
2) Restrict the result from step 1 to rows where nvl(my_table_1.column_2,'X')=nvl(my_table_2.column_2,'X')
my_table_1.column_2 will be NULL for any my_table_2 rows that didn't have a match in my_table_1, so the NVL will change these to 'X'.
|
|
|
|
| Re: Outer joins and NVL function [message #17412 is a reply to message #17400] |
Tue, 11 January 2005 08:48 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You cannot do an outer join on one column of a table and a regular join on another column of the same table legitimately. If you do, it has the effect of negating the outer join and turning the whole thing into a regular join. So, the query that you provided would produce the same results as without the outer join, in other words the same results as this query, which has the net effect of saying that the values of column_1 must be equal and either the values of column_2 must be equal or one or both values must be null.
select *
from my_table_1,
my_table_2
where my_table_1.column_1 = my_table_2.column_1
and nvl (my_table_1.column_2, 'X') = nvl (my_table_2.column_2, 'X');
You can combine nvl with an outer join, so the query below would produce an outer join with entirely different results:
select *
from my_table_1,
my_table_2
where my_table_1.column_1 (+) = my_table_2.column_1
and nvl (my_table_1.column_2 (+), 'X') = nvl (my_table_2.column_2, 'X');
When in doubt, just do a simple test to find out, making sure that you provide queries and data that will test all of the possibilities that you are interested in. Please see the demonstration below, which first shows your original query, then shows the same results with the outer join removed, demonstrating that outer joinining on only one column and doing a regular join on another of the same table, negates the outer join, producing a regular join, followed lastly by an outer join on both columns, producing a true outer join. I have used set null null to make it easier to see the null values.
-- test data:
scott@ORA92> set null null
scott@ORA92> select * from my_table_1
2 /
COLUMN_1 COLUMN_2
---------- --------
1 X
1 Y
1 null
2 X
2 Y
3 X
3 null
4 Y
4 null
5 X
6 Y
7 null
12 rows selected.
scott@ORA92> select * from my_table_2
2 /
COLUMN_1 COLUMN_2
---------- --------
1 X
1 Y
1 null
2 X
2 Y
2 null
3 X
3 Y
3 null
4 X
4 Y
4 null
5 X
5 Y
5 null
6 X
6 Y
6 null
7 X
7 Y
7 null
21 rows selected.
-- your original query with an outer join on one column
-- and a regular join on another column,
-- resulting in only a regular join:
scott@ORA92> select *
2 from my_table_1,
3 my_table_2
4 where my_table_1.column_1 (+) = my_table_2.column_1
5 and nvl (my_table_1.column_2, 'X') = nvl (my_table_2.column_2, 'X')
6 /
COLUMN_1 COLUMN_2 COLUMN_1 COLUMN_2
---------- -------- ---------- --------
1 X 1 X
1 null 1 X
1 Y 1 Y
1 X 1 null
1 null 1 null
2 X 2 X
2 Y 2 Y
2 X 2 null
3 X 3 X
3 null 3 X
3 X 3 null
3 null 3 null
4 null 4 X
4 Y 4 Y
4 null 4 null
5 X 5 X
5 X 5 null
6 Y 6 Y
7 null 7 X
7 null 7 null
20 rows selected.
-- no outer join, producing the same results as your original query,
-- demonstrating that the outer join is negated,
-- resulting in only a regular join:
scott@ORA92> select *
2 from my_table_1,
3 my_table_2
4 where my_table_1.column_1 = my_table_2.column_1
5 and nvl (my_table_1.column_2, 'X') = nvl (my_table_2.column_2, 'X')
6 /
COLUMN_1 COLUMN_2 COLUMN_1 COLUMN_2
---------- -------- ---------- --------
1 X 1 X
1 null 1 X
1 X 1 null
1 null 1 null
1 Y 1 Y
2 X 2 X
2 X 2 null
2 Y 2 Y
3 X 3 X
3 null 3 X
3 X 3 null
3 null 3 null
4 null 4 X
4 null 4 null
4 Y 4 Y
5 X 5 X
5 X 5 null
6 Y 6 Y
7 null 7 X
7 null 7 null
20 rows selected.
-- true outer join on both columns:
scott@ORA92> select *
2 from my_table_1,
3 my_table_2
4 where my_table_1.column_1 (+) = my_table_2.column_1
5 and nvl (my_table_1.column_2 (+), 'X') = nvl (my_table_2.column_2, 'X')
6 /
COLUMN_1 COLUMN_2 COLUMN_1 COLUMN_2
---------- -------- ---------- --------
1 X 1 X
1 null 1 X
1 X 1 null
1 null 1 null
1 Y 1 Y
2 X 2 X
2 X 2 null
2 Y 2 Y
3 X 3 X
3 null 3 X
3 X 3 null
3 null 3 null
null null 3 Y
4 null 4 X
4 null 4 null
4 Y 4 Y
5 X 5 X
5 X 5 null
null null 5 Y
null null 6 X
null null 6 null
6 Y 6 Y
7 null 7 X
7 null 7 null
null null 7 Y
25 rows selected.
|
|
|
|
| Re: Outer joins and NVL function [message #17429 is a reply to message #17412] |
Wed, 12 January 2005 05:58 |
Will
Messages: 32
Registered: January 2000
|
Member |
|
|
Thank you for this excellent explanation. I was looking for the "nvl(my_table1.column1(+),'X')" syntax.
I had been trying nvl(my_table1.column1,'X')(+) instead for several months with no luck.
|
|
|
|
Goto Forum:
Current Time: Thu May 15 19:21:46 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
