excel - SQL Statement - Select Where * Not equal to

Question

All,

New to SQL Statements (Probably a really basic question) 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')

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

***EDIT - The database I am using is Excel Database

Thanks in advance

Answer 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