[Tip] Comparison of NULL Fields

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Sat, 15 May 2004 7:14 pm

We know that when a variable or a field in Oracle is NULL, it cannot simply be compared to another value, because the result of the comparison will also be false!

Code: Select all
declare
  a number:=null;
  b number:=null;
begin
  if a=b
  then dbms_output.put_line('SIM');
  else  dbms_output.put_line('não'); 
  end if;
end;

SQL> /
não
INEFFECTIVE
To " remedy " this problem, you can use NVL, which is not a good practice. We will see why:
Code: Select all
if nvl(a, 123456789) = nvl(b, 123456789)
then --seu código
this works, if the value is not equal to 123456789. If the value is equal, screw the comparison!

VERY EFFICIENT
the best possible practice is a little cumbersome, but always work:
Code: Select all
if a<>b
or (a is null and b is not null)
or (a is not null and b is null)
then --seu codigo
dr_gori
Location: Portland, OR USA

Poston Thu, 19 Aug 2004 11:24 am

As you can see Null is not the same as, or different from null, or larger or smaller.

When you compare Null, must always be IS NULL or IS NOT NULL when you use NVL in queries of type:
Code: Select all
select 1 from  TABELA
WHERE codigo = NVL(VARIAVEL, codigo  )
That works to bring all the values from the table when the variable is equal to the code, but if the code is Null, will not work ... prefer to use:
Code: Select all
select 1 from  TABELA
WHERE codigo = VARIAVEL
OR       (codigo is null and VARIAVEL is null)
the code below does not show anything.
Code: Select all
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  A NUMBER DEFAULT NULL;
  3  B NUMBER DEFAULT NULL;
  4  BEGIN
  5    IF A = B THEN
  6       DBMS_OUTPUT.PUT_LINE('NULL É IGUAL A NULL');
  7    ELSIF A != B THEN
  8       DBMS_OUTPUT.PUT_LINE('NULL É DIFERENTE DE NULL');
  9    END IF;
10  END;
11  /
PL/SQL procedure successfully completed.
charlesmafra
Location: Angola

Charles Mafra
Oracle Developer

Poston Tue, 07 Jul 2009 8:12 am

Bottom line: ALWAYS, any comparison with NULL returns FALSE.
Knowing that, just use the functions or IS NULL/IS NOT NULL to compare.

Falow ...
alexandre5150
Location: NOVO HAMBURGO - RS

Poston Fri, 17 Jul 2009 12:15 pm

Good tip!!!

A lot of people end up forgetting these details while coding.
rodcapella
Location: Rio de Janeiro - RJ

_________________
Rodrigo Póvoa
Analista de Sistemas Júnior
Oracle Developer


  • See also
    Replies
    Views
    Last Post


    Return to PL/SQL

    Who is online

    Users browsing this forum: No registered users and 6 guests