How to compare multiple columns under same row in a table?

Question

Following columns of a table should not be equal in my where clause.

• cd_delivery_address
• cd_mail_delivery_address
• cd_st_code
• cd_mail_st_code
• cd_zip
• cd_mail_zip

Please find my code snippet to achieve this:

     select * from table cd
     where
(
(cd_mail_delivery_address <> cd_delivery_address or    
(cd_mail_delivery_address is null and cd_delivery_address is not null) or
(cd_mail_delivery_address is not null and cd_delivery_address is null)
)
and (
cd.cd_city <> cd.cd_mail_city or 
(cd.cd_city is null and cd_mail_city is not null) or 
(cd_city is not null and cd_mail_city is null))
and (
cd.st_code <> cd.cd_mail_st_code or 
(cd.st_code is null and cd_mail_st_code is not null) or 
(st_code is not null and cd_mail_st_code is null)
)
and (
cd.cd_zip <> cd.cd_mail_zip or 
(cd.cd_zip is null and cd_mail_zip is not null) or 
(cd_zip is not null and cd_mail_zip is null)
)
)

All columns are varchar2 and i get correct output for this code. But is it a better way to compare multiple columns in pl sql? can i improve this code? Any suggestion would be helpful.

Answer 1

You could replace your null checks with NVL function something like this:

...
NVL(cd_mail_delivery_address,'_') <> NVL(cd_delivery_address,'_')
...

it's definitively more readable but I'm not sure about query efficency

Answer 2

I have done it for two columns using a join:

select a.cd_delivery_address,b.cd_mail_delivery_address 
from cd a inner join cd b 
where a.cd_delivery_address <> b.cd_mail_delivery_address and
a.cd_delivery_address = b.cd_delivery_address

Here null checking condition will be omitted and will reduce the number of conditions, but there is a performance impact since join is involved.