1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What special operators does oracle provide for dealing with nulls?

Discussion in 'SQL PL/SQL' started by indupriya1, Apr 24, 2017.

  1. indupriya1

    indupriya1 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Hyderabad
    What special operators does Oracle provide for dealing with NULLs?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    146
    Trophy Points:
    830
    Location:
    Russian Federation
    for example : nvl,nvl2,coalesce,case
    what are you need?
     
  3. Shreeshail Nirale

    Shreeshail Nirale Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Pune
    Hi,
    You cannot use normal comparison operators like <, == and > with NULL value.

    Oracle provides SQL functions like NVL and Comparison operator like IS NULL and IS NOT NULL to deal with NULL value.
    To know how to use these operators, you can look at Oracle SQL NULL Operators
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    146
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    NVL is not only a SQL function.
    Oracle , like many other databases, allows only two comparison operators with null : "IS NULL" or " IS NOT NULL".
    If you need to compare the value of columns or variables that can have NULL values, use the functions that I wrote earlier.
    The oracle documentation is all fairly well documented.
     
    Shreeshail Nirale likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,601
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That depends upon what you are doing with the NULLs. Do you need to provide a default value? NVL() and NVL2() will do that:

    Code (SQL):
    SQL>
    SQL> --
    SQL> -- Convert NULL to constant value
    SQL> --
    SQL>
    SQL> SELECT isntnull, nvl(couldbenull,'Yep') couldbenull
      2  FROM nulltest;

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
             1 1xyztq                                                              
             2 2xyztq                                                              
             3 3xyztq                                                              
             4 4xyztq                                                              
             5 5xyztq                                                              
             6 6xyztq                                                              
             7 7xyztq                                                              
             8 8xyztq                                                              
             9 9xyztq                                                              
            10 10xyztq                                                            
            11 11xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            12 12xyztq                                                            
            13 Yep                                                                
            14 14xyztq                                                            
            15 15xyztq                                                            
            16 16xyztq                                                            
            17 17xyztq                                                            
            18 18xyztq                                                            
            19 19xyztq                                                            
            20 20xyztq                                                            
            21 21xyztq                                                            
            22 22xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            23 23xyztq                                                            
            24 24xyztq                                                            
            25 25xyztq                                                            
            26 Yep                                                                
            27 27xyztq                                                            
            28 28xyztq                                                            
            29 29xyztq                                                            
            30 30xyztq                                                            
            31 31xyztq                                                            
            32 32xyztq                                                            
            33 33xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            34 34xyztq                                                            
            35 35xyztq                                                            
            36 36xyztq                                                            
            37 37xyztq                                                            
            38 38xyztq                                                            
            39 Yep                                                                
            40 40xyztq                                                            
            41 41xyztq                                                            
            42 42xyztq                                                            
            43 43xyztq                                                            
            44 44xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            45 45xyztq                                                            
            46 46xyztq                                                            
            47 47xyztq                                                            
            48 48xyztq                                                            
            49 49xyztq                                                            
            50 50xyztq                                                            
            51 51xyztq                                                            
            52 Yep                                                                
            53 53xyztq                                                            
            54 54xyztq                                                            
            55 55xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            56 56xyztq                                                            
            57 57xyztq                                                            
            58 58xyztq                                                            
            59 59xyztq                                                            
            60 60xyztq                                                            
            61 61xyztq                                                            
            62 62xyztq                                                            
            63 63xyztq                                                            
            64 64xyztq                                                            
            65 Yep                                                                
            66 66xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            67 67xyztq                                                            
            68 68xyztq                                                            
            69 69xyztq                                                            
            70 70xyztq                                                            
            71 71xyztq                                                            
            72 72xyztq                                                            
            73 73xyztq                                                            
            74 74xyztq                                                            
            75 75xyztq                                                            
            76 76xyztq                                                            
            77 77xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            78 Yep                                                                
            79 79xyztq                                                            
            80 80xyztq                                                            
            81 81xyztq                                                            
            82 82xyztq                                                            
            83 83xyztq                                                            
            84 84xyztq                                                            
            85 85xyztq                                                            
            86 86xyztq                                                            
            87 87xyztq                                                            
            88 88xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            89 89xyztq                                                            
            90 90xyztq                                                            
            91 Yep                                                                
            92 92xyztq                                                            
            93 93xyztq                                                            
            94 94xyztq                                                            
            95 95xyztq                                                            
            96 96xyztq                                                            
            97 97xyztq                                                            
            98 98xyztq                                                            
            99 99xyztq                                                            

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
           100 100xyztq                                                            

    100 ROWS selected.

    SQL>
    SQL> --
    SQL> -- Change values in nullable column
    SQL> --
    SQL>
    SQL> SELECT isntnull, nvl2(couldbenull,'Nope','Yep') couldbenull
      2  FROM nulltest;

      ISNTNULL COUL                                                                
    ---------- ----                                                                
             1 Nope                                                                
             2 Nope                                                                
             3 Nope                                                                
             4 Nope                                                                
             5 Nope                                                                
             6 Nope                                                                
             7 Nope                                                                
             8 Nope                                                                
             9 Nope                                                                
            10 Nope                                                                
            11 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            12 Nope                                                                
            13 Yep                                                                
            14 Nope                                                                
            15 Nope                                                                
            16 Nope                                                                
            17 Nope                                                                
            18 Nope                                                                
            19 Nope                                                                
            20 Nope                                                                
            21 Nope                                                                
            22 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            23 Nope                                                                
            24 Nope                                                                
            25 Nope                                                                
            26 Yep                                                                
            27 Nope                                                                
            28 Nope                                                                
            29 Nope                                                                
            30 Nope                                                                
            31 Nope                                                                
            32 Nope                                                                
            33 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            34 Nope                                                                
            35 Nope                                                                
            36 Nope                                                                
            37 Nope                                                                
            38 Nope                                                                
            39 Yep                                                                
            40 Nope                                                                
            41 Nope                                                                
            42 Nope                                                                
            43 Nope                                                                
            44 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            45 Nope                                                                
            46 Nope                                                                
            47 Nope                                                                
            48 Nope                                                                
            49 Nope                                                                
            50 Nope                                                                
            51 Nope                                                                
            52 Yep                                                                
            53 Nope                                                                
            54 Nope                                                                
            55 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            56 Nope                                                                
            57 Nope                                                                
            58 Nope                                                                
            59 Nope                                                                
            60 Nope                                                                
            61 Nope                                                                
            62 Nope                                                                
            63 Nope                                                                
            64 Nope                                                                
            65 Yep                                                                
            66 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            67 Nope                                                                
            68 Nope                                                                
            69 Nope                                                                
            70 Nope                                                                
            71 Nope                                                                
            72 Nope                                                                
            73 Nope                                                                
            74 Nope                                                                
            75 Nope                                                                
            76 Nope                                                                
            77 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            78 Yep                                                                
            79 Nope                                                                
            80 Nope                                                                
            81 Nope                                                                
            82 Nope                                                                
            83 Nope                                                                
            84 Nope                                                                
            85 Nope                                                                
            86 Nope                                                                
            87 Nope                                                                
            88 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
            89 Nope                                                                
            90 Nope                                                                
            91 Yep                                                                
            92 Nope                                                                
            93 Nope                                                                
            94 Nope                                                                
            95 Nope                                                                
            96 Nope                                                                
            97 Nope                                                                
            98 Nope                                                                
            99 Nope                                                                

      ISNTNULL COUL                                                                
    ---------- ----                                                                
           100 Nope                                                                

    100 ROWS selected.

    SQL>
     
    Or do you want to return rows with NULLS?

    Code (SQL):
    SQL>
    SQL> SELECT isntnull, couldbenull
      2  FROM nulltest
      3  WHERE couldbenull IS NULL;

      ISNTNULL COULDBENULL                                                        
    ---------- ------------                                                        
            13                                                                    
            26                                                                    
            39                                                                    
            52                                                                    
            65                                                                    
            78                                                                    
            91                                                                    

    7 ROWS selected.

    SQL>
     
    Just asking about functions may not provide the answer you really need.
     
  6. santhoshreddy

    santhoshreddy Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    india
    Hi indupriya1,
    Answer for your question depends on your requirement.
    but below i explained how Oracle handles NULL Values.In Oracle Context NULL Means Unknown or Unknown Defined, No Value, always NULL not Equal to NULL in all scenarios except DECODE function and compound keys.
    all Operators Return NULL Value when we given a null operand Except concatenation operator(||).
    For Example
    SELECT 2+NULL FROM Dual; Return's NULL

    To compare NULL Values Oracle Provided only two comparison condition IS NULL and IS NOT NULL.
    Oracle Provided some special Functions to Handle NULL Values
    COALESCE
    LNNVL
    NULLIF
    NVL
    NVL2
    Below are the links to understand more about NULL and Operators in Oracle
    --Operators
    https://docs.oracle.com/cd/B28359_01/server.111/b28286/operators.htm#SQLRF003
    --Functions (Refer NULL-Related Functions )
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm
    --NULLS
    https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm

    I hope this helps,please let me know if you want to know more about them