SQLite User Forum

Not Equal Operator
Login
Hierarchical

Not Equal Operator

(1) By anonymous on 2020-10-19 10:02:26 [link] [source]

Hi, I'm having issues with not equal operator. It always returns 0 records. However when it is replaced with equal operator it works fine.

Here is an example; I'm getting same result with Node.js and DB Browser for SQLite (3.11.2, SQLLite: 3.31.1)

Table: CREATE TABLE "testTable" ( "recordId" INTEGER NOT NULL, "testColumn" VARCHAR(36), PRIMARY KEY("recordId") )

Records: recordId testColumn 1 testValue 2 <NULL> 3 testValue 4 <NULL> 5 testValue 6 <NULL>

Not Equal Query: SELECT recordId FROM "testTable" WHERE ("testColumn" != "testValue") ORDER BY recordId ASC;

Not Equal Query Result: Result: 0 rows returned in 8ms

Equal Query: SELECT recordId FROM "testTable" WHERE ("testColumn" == "testValue") ORDER BY recordId ASC;

Equal Query Result: recordId 1 3 5

As you can see equal operator working as expected but not equal operator return 0 rows instead of 3. This is a really simple query but i cannot point my finger. Is it me or sqlite (probably not) ?

(2.1) By Gunter Hick (gunter_hick) on 2020-10-19 10:49:35 edited from 2.0 in reply to 1 [link] [source] 

You are misusing quotes.

Double quotes are for names that contain whitespace. Neither your table name nor any of the column names require them. Sometimes SQLite will accept a literal enclosed in double quotes, because doing so makes sense in the context and SQLite is being nice. For best results, don't use double quotes unless you are being force fed multi word table and/or column names. 

Single quotes are for string literals. Always use single quotes for string literals.

Also, comparing NULL to anything results in NULL, which is NOT TRUE. Check out the IS operator.

BTW: VARCHAR(36) is read as type TEXT, check https://sqlite.org/datatype3.html

(3) By anonymous on 2020-10-19 11:07:25 in reply to 2.0 [link] [source]

You are misusing quotes. Thanks for the tip.

Double quotes are for names that contain whitespace. Neither your table name nor >any of the column names require them. Sometimes SQLite will accept a literal >enclosed in double quotes, because doing so makes sense in the context and SQLite >is being nice. For best results, don't use double quotes unless you are being >force fed multi word table and/or column names.

Actually this is output of sql statement generator. In real app identifiers are UUID (testTable, testColumn, etc.) Therefore, generator adds quotation to all identifiers because of minus character. I've converted generated statement's UUID identifiers to names in here for sake of readability,

Single quotes are for string literals. Always use single quotes for string literals.

Actually I'm using query parameters (?) for that in code. So I don't get involved with string literals actually. SQLite connection library handles that. for me. I have changed ? with value for this post.

Also, comparing NULL to anything results in NULL, which is NOT TRUE. NULL comparisons are done by IS operator. No problem there. Test result regarding NULL types are fine.

BTW: VARCHAR(36) is read as type TEXT, check https://sqlite.org/datatype3.html

Yea I'm aware of that I add them like type annotation that help me figure out the types of that columns. It's kinda for debugging.

Can you see anything problematic with regards to Not Equal operator's odd behavior ?

(4) By Orcun (orcuan) on 2020-10-19 11:20:18 in reply to 2.1 [link] [source]

I've created an account.

Also, comparing NULL to anything results in NULL, which is NOT TRUE. Check out the IS operator.

Do you mean NULL != 'Any Value' equals false ?

In that case I have to add (testColumn != 'testValue' OR testColumn IS NULL)

Himm, it works now. Isn't this weird ?

(5) By Warren Young (wyoung) on 2020-10-19 15:09:13 in reply to 4 [link] [source]

You might find this document helpful.

(6) By Gunter Hick (gunter_hick) on 2020-10-19 15:38:29 in reply to 4 [source] 

NULL != 'any Value' evaluates to NULL
NULL == 'any Value' evaluates to NULL
NULL IS NOT 'any Value' evaluates to TRUE

you are looking for the expression

testColumn IS NOT 'testValue'

to correctly handle NULLs in testColumn

(7) By David Raymond (dvdraymond) on 2020-10-19 15:40:55 in reply to 4 [link] [source]

You can also use IS, and IS NOT.

https://www.sqlite.org/lang_expr.html#operators

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.