Forum Stats

  • 3,759,253 Users
  • 2,251,515 Discussions
  • 7,870,551 Comments

Discussions

sql query with bind variable NULL

ora_1978
ora_1978 Member Posts: 486 Bronze Badge

create table null_test (eno number, ename varchar2(20), salary number);


insert into null_test values (1, 'Peter',1000);

insert into null_test values (null, 'john',null);

insert into null_test values (3, null,3000);


commit;


Query Required:


One single select query


select * from null_test where eno = :p_eno ; -- :p_eno when assigned NULL ,I want all three records to get displayed

select * from null_test where eno = :p_eno ; -- :p_eno when 1.I want all one record with eno 1 should get displayed


same for varchar2 fields ename


Regards,

Vinodh

Tagged:

Best Answer

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    Hi @ora_1978 ,

    here's my test which works as expected:

    Be aware, that the second query is case sensitive. There is no ename 'peter' in the table. You have to search for 'Peter' to get the row. Of course you can modify the query so it is no longer case sensitive.

    Best regards

    Jan

Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @ora_1978 ,

    check this out:

    SELECT *
     FROM null_test
     WHERE eno = :p_eno OR :p_eno IS NULL;
    
    SELECT *
     FROM null_test
     WHERE ename = :p_ename OR :p_ename IS NULL;
    

    Best regards

    Jan

  • ora_1978
    ora_1978 Member Posts: 486 Bronze Badge

    No records coming while passing NULL to bind variable and no records coming when i pass value example peter .

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    Hi @ora_1978 ,

    here's my test which works as expected:

    Be aware, that the second query is case sensitive. There is no ename 'peter' in the table. You have to search for 'Peter' to get the row. Of course you can modify the query so it is no longer case sensitive.

    Best regards

    Jan

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited Sep 4, 2021 12:10PM
    WHERE eno = :p_eno OR :p_eno IS NULL;
    

    will not use index on eno, assuming there is one:

    SQL> create index null_test_idx1 on null_test(eno);
    
    
    Index created.
    
    
    SQL> variable p_eno number
    SQL> explain plan for select * from null_test where eno = :p_eno or :p_eno is null;
    
    
    Explained.
    
    
    SQL> select * from table(dbms_xplan.display);
    
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------
    Plan hash value: 3898976153
    
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |           |     1 |    68 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS STORAGE FULL| NULL_TEST |     1 |    68 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       1 - storage(:P_ENO IS NULL OR "ENO"=TO_NUMBER(:P_ENO))
           filter(:P_ENO IS NULL OR "ENO"=TO_NUMBER(:P_ENO))
    
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    
    18 rows selected.
    
    
    SQL>
    

    Compare with:

    SQL> explain plan for select * from null_test where eno = :p_eno union all select * from null_test where :p_eno is null;
    
    
    Explained.
    
    
    SQL> select * from table(dbms_xplan.display);
    
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------
    Plan hash value: 3654053033
    
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |                |     4 |   272 |     5   (0)| 00:00:01 |
    |   1 |  UNION-ALL                           |                |       |       |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| NULL_TEST      |     1 |    68 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN                  | NULL_TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
    |*  4 |   FILTER                             |                |       |       |            |          |
    |   5 |    TABLE ACCESS STORAGE FULL         | NULL_TEST      |     3 |   204 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       3 - access("ENO"=TO_NUMBER(:P_ENO))
       4 - filter(:P_ENO IS NULL)
    
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    
    22 rows selected.
    
    
    SQL>
    

    Or we could use hint use_concat with or_predicates argument, however I don't recall if it is yet documented:

    SQL> explain plan for select /*+ use_concat(or_predicates(1)) */ * from null_test where eno = :p_eno or :p_eno is null;
    
    
    Explained.
    
    
    SQL> select * from table(dbms_xplan.display);
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------
    Plan hash value: 3906413706
    
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                |     4 |   272 |     3   (0)| 00:00:01 |
    |   1 |  CONCATENATION                        |                |       |       |            |          |
    |*  2 |   FILTER                              |                |       |       |            |          |
    |   3 |    TABLE ACCESS STORAGE FULL          | NULL_TEST      |     3 |   204 |     2   (0)| 00:00:01 |
    |*  4 |   FILTER                              |                |       |       |            |          |
    |   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| NULL_TEST      |     1 |    68 |     1   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN                  | NULL_TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       2 - filter(:P_ENO IS NULL)
       4 - filter(LNNVL(:P_ENO IS NULL))
       6 - access("ENO"=TO_NUMBER(:P_ENO))
    
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    
    24 rows selected.
    
    
    SQL>
    

    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @ora_1978

    No records coming while passing NULL to bind variable and no records coming when i pass value example peter .

    The solution Jan posted works when I try it. Post a complete test script (including how you define and initialize the bind variable) that fails.

    Another way is:

    SELECT  *
    FROM	null_test
    WHERE	DECODE (:p_eno,  eno, 1,   NULL, 1) = 1
    ;