I have a table Products and around 58000 records in it. I run two queries are as follows.
Product.where(description: 'swiss').count
It returns 0 products out of 58000 products. But when I run query below.
Product.where.not(description: 'swiss').count
it returns 2932 products out of 58000 products. I think it should return all 58000 products, because it is the reverse of first query. I did not understand why it returns only 2932 products.
If you have NULL values in your columns, this could happen, because NULL always compares as NULL, even to itself, but WHERE expression must be true to include a result.
'a' = 'a'; True
'a' = 'b'; False
'a' = NULL; Results in NULL (not false!)
NULL = NULL; Results in NULL (not true!)
'a' != 'a'; False
'a' != 'b'; True
'a' != NULL; NULL
e.g. consider the table null_test containing
id | str
----+-----
1 | <NULL>
2 | a
3 | b
When looking for a column equal to some value, the NULL is never equal, so this will just return row 2.
SELECT id FROM null_test WHERE str = 'a';
But the same applies looking for a column not equal to some value, the NULL is never not equal (its NULL), so this will just return row 3.
SELECT id FROM null_test WHERE str != 'a';
Thus the total of your = and != is not all the rows, because you never included the NULL rows. This is where IS NULL and IS NOT NULL come in (they work like you might expect = NULL and != NULL to work).
SELECT id FROM null_test WHERE str != 'a' OR str IS NULL;
Now you get rows 1 and 3, making this the opposite of str = 'a'.
For ActiveRecord, it treats the Ruby nil value like the SQL NULL and creates the IS NULL checks in certain situations.
NullTest.where(str: 'a')
SELECT * FROM "null_test" WHERE "str" = 'a'
NullTest.where.not(str: 'a')
SELECT * FROM "null_test" WHERE "str" != 'a'
NullTest.where(str: nil)
SELECT * FROM "null_test" WHERE "str" IS NULL
NullTest.where.not(str: nil)
SELECT * FROM "null_test" WHERE "str" IS NOT NULL
NullTest.where(str: nil).or(NullTest.where.not(str: 'a'))
SELECT * FROM "null_test" WHERE "str" IS NULL OR "str" != 'a'
You have likely many records where description is nil and those are not included in the not.
A way to include the 'nil' records as well would be...
Product.where('description <> ? OR description IS NULL', 'swiss')
Or alternatively
Product.where.not(description: 'swiss').or(Product.where(description: nil))
