SQL Exists/Not Exists

I have a table in 2 schemas. I need to find records in sc1 not in sc2. This table has five unique keys and any of them can be null. This is the script I ran:

Select * from sc1.table a
Where not exists (Select 'X' from sc2.table b where a.f1 = b.f1
and a.f2 = b.f2 and a.f3 = b.f3 and a.f4 = b.f4 and a.f5 = b.f5);

Now this selects all records from sc2.table. I assume that is because at least one of the fields is null and the = comarision in the exist statement for that field is returning a false.
(EX: sc1.table f1 is null and sc2.table f1 is null but the a.f1 = b.f1 is returning a false since fields are null)

I need this to be part of an insert statement like this:

Insert into sc2.table (*)
Select * from sc1.table a
Where not exists (Select 'X' from sc2.table b where a.f1 = b.f1
and a.f2 = b.f2 and a.f3 = b.f3 and a.f4 = b.f4 and a.f5 = b.f5);

Is there an easy way to fix this. I don't want to run a minus statement like:

Insert into sc2.table (*)
Select * from sc1.table a
where (f1, f2, f3, f4, f5) not in
(select f1, f2, f3, f4, f5 from sc1.table
minus
select f1,f2,f3,f4,f5 from sc2.table)

Plus I don't think the NOT IN will pick up the null values anyway.

Is there any simple way to reslove this issue? I have do this for over 400 tables and not sure how many have this issue.

Ok I found this works:

Insert into sc2.table (*)
Select * from sc1.table a
Where not exists (Select 'X' from sc2.table b where
(a.f1 = b.f1 or (a.f1 is null and b.f1 is null)) and
(a.f2 = b.f2 or (a.f2 is null and b.f2 is null)) and
(a.f3 = b.f3 or (a.f3 is null and b.f3 is null)) and
(a.f4 = b.f4 or (a.f4 is null and b.f4 is null))) and
(a.f5 = b.f5 or (a.f5 is null and b.f5 is null)));

Is there a simpler solution like a way to make Oracle substitue a value if null is returned for a field on a select?

I think you need to get acquainted with the NVL function.

(a.f1 = b.f1 or (a.f1 is null and b.f1 is null))

can be written as:

( nvl(a.f1,10001) = nvl(b.f1, 10001) )

Please place a number which is not used by a.f1 or b.f1 instead of 10001.

Regards,
Dan

Thanks for the input but the NVL function makes scripts run 3 times as long and takes more resources then the or is null and is null comparison. I also found another way to run this and it is the fastest:

Insert into sc2.table (*)
Select * from sc1.table a
where (f1||f2||f3||f4||f5) not in
(select f1||f2||f3||f4||f5 from sc1.table
minus
select f1||f2||f3||f4||f5 from sc2.table)

Performance and minimizing systems resources is key for SQL scripts that are to be run often. This is much more important then the ease of writting or readability of the scripts.

maybe NVL() slowed your query because it made oracle ignore the indexes you had on those columns? you could try adding function based indexes on NVL() for those columns. that could be quite fast.

mike