1

I try to filter my data in a simple way:

SELECT * FROM INFO_CA
WHERE DATE_FACT > TO_DATE('20160101', 'YYYYMMDD')
AND ACCOUNT_SOC <> 'BELGIUM'

This query return 111 926 records.

  • There're 10 457 262 records in my table INFO_CA after filtering on the date.
  • There're 296 records with 'BELGIUM' in the column ACCOUNT_SOC
  • There're 111 926 records with something different than BELGIUM in ACCOUNT_SOC
  • And there're 10 345 040 with null value in ACCOUNT_SOC.

If I use this 2nd query, I have what I expect (10 456 966 records = 10 345 040 null + 111 926 with something else than BELGIUM)

SELECT * FROM INFO_CA
WHERE DATE_FACT > TO_DATE('20160101', 'YYYYMMDD')
AND (ACCOUNT_SOC <> 'BELGIUM' OR ACCOUNT_SOC IS NULL)

I'm using Oracle 12 (SQL Developer) + Talend. Even in Talend my first query don't return the null values.

So why ? Why my first query only returns records with a value different from BELGIUM or null. Why do I have to specify that I want the records with ACCOUNT_SOC null ?

The datatype of ACCOUNT_SOC is VARCHAR2(255 BYTE).

Thanks :)

Mayot
  • 41
  • 7
  • because `null` is never `=` or `!=` any value. to compare with `null` you shoul always use `is null` or `is not null`. check this documentation https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm – hotfix Aug 22 '18 at 13:33
  • Thanks for your answer @hotfix However, I find this post : https://stackoverflow.com/questions/8036691/sql-not-displaying-null-values-on-a-not-equals-query – Mayot Aug 22 '18 at 13:38

2 Answers2

2

NULL means that the value is not known. Therefore a value that is null in the ACCOUNT_SOC field will not qualify under the condition ACCOUNT_SOC <> 'BELGIUM' because the in the case of NULLS the ACCOUNT_SOC is not known. It isn't BELGIUM and it isn't NOT BELGIUM - its not known.

In summary NULLS can be tricky so your best bet is to do a little research so you know what effects they can have.

You could also do

NVL(ACCOUNT_SOC,'XXX') <> 'BELGIUM'

Which will convert a NULL value to 'XXX' which you can then reliably check

BriteSponge
  • 1,034
  • 9
  • 15
0

Alternate way using NOT EXISTS There is tricky when dealing with NOT IN with NULLS, by using NOT EXISTS with JOIN of key columns will bring NULLs in result.

SELECT * FROM INFO_CA a
WHERE NOT EXISTS (SELECT 1 FROM  INFO_CA b WHERE /*join of key columns*/ a.PK_col=b.PKcol AND b.ACCOUNT_SOC = 'BELGIUM' )
AND a.DATE_FACT > TO_DATE('20160101', 'YYYYMMDD')
kanagaraj
  • 442
  • 1
  • 3
  • 8