A very common issue that I see on my experience is that  “NOT IN” clause not working.

Here is a quick repro:

I create a PRODUCTS table with PRODUCT_ID and PRODUCT_NAME columns. I insert three records in it.

CREATE COLUMN TABLE PRODUCTS (PRODUCT_ID INT, PRODUCT_NAME NVARCHAR (50));

INSERT INTO PRODUCTS VALUES (101,’P1′);

INSERT INTO PRODUCTS VALUES (102,’P2′);

INSERT INTO PRODUCTS (PRODUCT_ID) VALUES (103);   

Note that a NULL gets inserted in the third record for PRODUCT_NAME columns. This is intentional.

/wp-content/uploads/2016/09/11_1040356.png

This is what the PRODUCTS table looks like, note the NULL there

Now my final query: Give me all PRODUCT_ID, PRODUCT_NAME the from PRODUCT table that do not exist these records ‘P1’, ‘P2’

SELECT PRODUCT_ID, PRODUCT_NAME

     FROM PRODUCTS

     WHERE PRODUCT_NAME NOT IN (‘P1’, ‘P2’)                                                    

And I get an empty result set.

/wp-content/uploads/2016/09/11_1040356.png

The technical issue is:

When the comparison is done with NULL, it returns UNKNOWN because NULL is an unknown value and anything compared with unknown can only result in unknown. And UNKNOWN in this case of Logical comparison is treated as FALSE and an empty result set is being returned.


Solution: Put an additional filter.

SELECT PRODUCT_ID, PRODUCT_NAME FROM PRODUCTS

WHERE (PRODUCT_NAME NOT IN (‘P1’, ‘P2’) OR PRODUCT_NAME IS NULL);  

/wp-content/uploads/2016/09/11_1040356.png

Hope it helps to every one.

Best Regards

Srinivas

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Gopinath Kolli

    nice one.

    For a record i wanna explain my scenario.

    In a CV I have a filter (product != X) and product has null values in it.

    when i see data preview one day i saw there are no null values in the output, but after some days i got null values. I don’t know what happened in the back ground.

    Now my question is: Is this applicable only for SQL statements? Any insight in my scenario would be helpful.

    (0) 
        1. SrinivasuluReddy Tanguturi Post author

          Yes Gopinath , Its applicable to Graphical as Well.

          A simple Scenario i did try .

          I did add simple products table into Projection node in graphical Calculation view .

          applied Filter (“PRODUCT_NAME” !=’P2′)

          /wp-content/uploads/2016/09/11_1040375.png

          I didn’t see null values in the CV Data preview’/wp-content/uploads/2016/09/11_1040375.png

          (0) 
  2. Lars Breddemann

    While this is correct, it’s not at all a SAP HANA ‘issue’ but simply the way the SQL standard defines how NULL values are handled.

    This is the very same behaviour you should get with all SQL compliant DBMS.

    (SQL Fiddle)

    So, besides the fact that this post states what should be very well understood and known if you work with NULL values, it’s really not about SAP HANA.

    Other than that I like that it is well prepared and written. If you fix the typo in the solution query (table PRODUCT instead PRODUCTS) it’s “ready for printing” 😉

    (0) 

Leave a Reply