I execute an SQL with Left Outer-Join condition
on several common fields.
one of those fields is of type string ("VarChar2"),
and may include null-strings.
Now, as long as there are equal strings in records
of the 2 tables, the join condition works as I expect
and matches the records.
The problem is that I expect records with null string
in both tables to be "Matched" as well,
but it doesn't happen.
I'll demonstrate it with a simple example: SELECT T2.SomeField
FROM T1, T2
WHERE (T1.JoinStrField = T2.JoinStrField (+))
AND (T1.OtherJoinField = T2.OtherJoinField(+))
Suppose T1 has the record JoinStrField = <Null> ; OtherJoinField = 1
and T2 has the record JoinStrField = <Null> ; OtherJoinField = 1 ; SomeField = "Something"
The result-set will be one record: SomeField = <Null>
What I expect that record to be: SomeField = "Something"
If anyone can help me to improve my SQL ,
I would really appreciate it!
Just one comment,
I use "Outer-Join" in order to achieve fast performance (very big tables),
so I would like to avoid of any kind of "slow" solutions such as sub-queries.
Thanks for responding my thread,
but unfortunately the query you propose does not
solve my problem.
I need the outer join on the right table, not left.
Anyway, it doesn't matter if the join is right or left,
using this kind of SQL "Null" values in the Joined fields
are anyway not matched.
First things ... First. You have said that you are using a Right Join, but your code uses a Left join.
Second. You may find it a bit easier to actually supply a test case. i.e. supplky create table and insert scripts to duplicate your issue. Then tell us what results you expect based on the test case you provided.
Thanks for your correction & suggestions,
I found them quite helpful!
I just corrected my mistake about the JOIN ("left" instead of "right").
Regarding your suggestion to supply "Create Table" & "Insert"
statements, I'll keep doing that on my next threads.
(unless my explanation on the current thread is not clear enough,
then I'll supply such scripts to current thread as well...)
SQL> SELECT T2.SOME_FIELD
2 FROM T1 , T2
3 WHERE (T1.OTHER_JOIN_FIELD = T2.OTHER_JOIN_FIELD(+) )
4 AND (T1.JOIN_STR_FIELD = T2.JOIN_STR_FIELD(+) );
I would imagine that this is because you are over simplifying the situation (Littlefoot's solution from earlier would give you your desired results if this were an accurate reflection of your situation.)
Beaten to it every time Littlefoot
Littlefoot's solution from earlier would give you your desired results if this were an accurate reflection of your situation.
Beaten to it every time Littlefoot
Littlefoot's solution indeed gave me the desired results,
but with the wrong join...
He just swapped the "(+)" from one side to the other,
so obviously "SomeField" will result a Not-Null value since it
doesn't function as a lookup-field anymore.
The whole idea is to lookup "SomeField" using a one-side
Outer-Join where T1 is the Master-Table and T2 is the Lookup-Table.
Please execute the test case I submitted before and tell me
if you understand my problem.
By the way , I tried to work-around this problem using "NVL" as following:
FROM T1 , T2
WHERE (T1.OTHER_JOIN_FIELD = T2.OTHER_JOIN_FIELD (+))
AND (NVL (T1.JOIN_STR_FIELD,'x') = NVL(T2.JOIN_STR_FIELD,'x') (+))
But then I get an error-message :
"ORA00936 - Missing Expression"
If I only omit the "(+)" from the query it works, but then
it's an Inner-Join which I do NOT desire.
All I need is a simple Outer-Join that uses T1
as the master table and T2 as the lookup table,
And although one of the join fields ("Join_Str_Field") allows "Null",
I want those "Nulls" to pass the Join test.
They currently don't!