2

I am trying to simplify the following query (as shown in the docs):

const { Op } = require("sequelize");
User.findAll({
  where: {
    [Op.not]: [
      { email: '' },
      { email: null }
    ]
  }
});

to

const { Op } = require("sequelize");
User.findAll({
  where: {
    email: {
      [Op.not]: ['', null]
    }
  }
});

However, the two queries do not yield the same result. The latter yields an empty array. How can this be?

Vitalynx
  • 946
  • 1
  • 8
  • 17
  • Try changing null to some value and if it's work, check this one out. https://stackoverflow.com/q/129077/2956135 – Emma Jan 30 '21 at 00:04
  • 1
    also the first one you are putting 2 objects in `[Op.not]` which by default it makes AND query. `NOT (email = '' AND email IS NULL)`. Do you meant to have OR query? `NOT (email = '' OR email IS NULL)`? – Emma Jan 30 '21 at 00:14
  • Let’s say the email is an empty string. That would give: `NOT(true AND false) = NOT(false) = TRUE`. This explains why I get a full array with the first one. So you are right, for what I am trying to achieve I should be using an OR. Still strange that these two do not yield the same result though (when the first one is just a simplification of the second one) – Vitalynx Jan 30 '21 at 23:49
  • The first one yields to `NOT (email = '' AND/OR email IS NULL)` but the second one yields to `NOT IN ('', NULL)`. And the second syntax gives you empty result due to "null in NOT IN". The link I posted above has good explanation of this behavior. To avoid this, you can stick with the first syntax or if your DB has `ansi_nulls` option, you can look into turning this off. – Emma Jan 31 '21 at 04:04

0 Answers0