0

I am attempting to extract all data where the column "Drive_Status" is not equal to "Success" any help on the below statement would be much appreciated.

 SELECT * FROM [Main$] WHERE (Drive_Status <> 'Success')

The issue with the statement above is data that is either Null or tagged "Error" in the "Drive_Status" column is not being returned.

The database I am using is Excel Database

Dharman
  • 30,962
  • 25
  • 85
  • 135
Sean Bailey
  • 375
  • 3
  • 15
  • 33
  • 3
    [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Cid Jan 12 '21 at 14:18
  • 1
    `WHERE Drive_Status IS DISTINCT FROM 'Success'` – jarlh Jan 12 '21 at 14:30
  • 1
    [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Jan 12 '21 at 14:33

2 Answers2

2

NULL is not a value so you cannot use <> to check against NULL.

Instead, try the following:

SELECT * FROM [Main$] WHERE Drive_Status <> 'Success' OR Drive_Status IS NULL
D M
  • 5,769
  • 4
  • 12
  • 27
  • thanks for the response, when i enter this query an error message is thrown "Expecting a select statement" – Sean Bailey Jan 12 '21 at 14:38
  • Which [DBMS](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) are you using? – D M Jan 12 '21 at 14:40
1

If you want to include NULL values as "not equal" then you need a NULL safe operator. Otherwise, the <> returns NULL, which is treated as "false" in a WHERE clause.

You haven't specified your database, but standard SQL provides one:

where Drive_Status is distinct from 'Success'

If Drive_Status is NULL, then this returns TRUE rather than NULL.

Not all databases support this. Some use <=> as NULL-safe equality (that is NULL <=> NULL evaluates to true). In these, you can use:

where not Drive_Status <=> 'Success'

And in others, you need to be more specific:

where Drive_Status <> 'Success' or Drive_Status is null
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786