CODEWITHSUNDEEP
sqlruby-on-railspostgresqlarel
nicholas a. evans
nicholas a. evans

Reputation: 2233

What is the difference between "x IS NULL" and "NOT (x IS NOT NULL)"?

Is there a significant difference in postgresql's execution, performance, or logic between

SELECT "users".* FROM "users"  WHERE ("users"."deleted_at" IS NULL)

and

SELECT "users".* FROM "users"  WHERE (NOT ("users"."deleted_at" IS NOT NULL))

Obviously, if written by hand, the first expression is the one I would write (who would intentionally write a double negative?!). But in this case, I'm using ruby's arel library to dynamically create both versions, sort of like so:

def generate_query(search_terms, negated=false, users=User)
  where_clause = arel_for_one_of_many_possible_queries(search_terms)
  where_clause = where_clause.not if negated
  users.where(where_clause)
end

And, for the "deleted" search_term, the where_clause will be arel_table[:deleted_at].not_eq(nil), but for other search_terms it could be a variety of clauses, including compound clauses and subselects. Adding the .not to the end, arel will always generate SQL of the second form. I could generate the first form by special casing my NULL checks and manually generating .eq or .not_eq as the case may be, but I'd want some clear benefit to doing that before I make my code more verbose.

Upvotes: 4

Views: 214

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 127406

Use EXPLAIN to see the difference, if there is any.

I think the query rewriter will optimize this, but I didn't check the source code for this example.

Edit: I was wrong, this is not optimized at all. Where ("users"."deleted_at" IS NULL) can use an index, the (NOT ("users"."deleted_at" IS NOT NULL)) condition results in a sequential disk scan.

Upvotes: 7

Related Questions