2

I have a simple performance related question regarding Equality and Inequality. from the past experience I learnt that any Eq conditions work better and Or conditions cause some perf issues.

When I look at the query plan for both when executed side by side both had a Scan type of EQ

did same query plan. does it make a difference if I have the query formatted like NOT(A=B and C=D) or Just work with (A <> B or C<> D)

what is the best approach?

Did not think about nulls here, but here is the difference.

SELECT 1 WHERE NOT(2=1 AND NULL=2)
SELECT 1 WHERE NOT(2=1 AND 3=NULL)
SELECT 1 WHERE NOT(2=1 AND NULL=2)
SELECT 1 WHERE NOT(1=1 AND 3=NULL)
SELECT 1 WHERE NOT(NULL=1 AND 2=2)
SELECT 1 WHERE NOT(NULL=null AND 2=2)   --Point to note is his one. False, True
SELECT 1 WHERE NOT(1=1 AND 3=3)

SELECT 1 WHERE (2<>1 OR NULL<>2)
SELECT 1 WHERE (2<>1 OR 3<>NULL)
SELECT 1 WHERE (2<>1 OR NULL<>2)
SELECT 1 WHERE (1<>1 OR 3<>NULL)
SELECT 1 WHERE (NULL<>1 OR 2<>2)
SELECT 1 WHERE (NULL<>null or 2=2)      --Point to note is his one. True, True
SELECT 1 WHERE (1<>1 OR 3<>3)
bhushanvinay
  • 449
  • 1
  • 5
  • 21
  • Welcome to wonderful world of three-valued logic https://en.wikipedia.org/wiki/Three-valued_logic :-) Btw, your 2nd note should also read *False, True* – dnoeth Jan 16 '16 at 15:09
  • You should really be checking for NULL'ness by `x IS NULL` or `x IS NOT NULL` instead of comparing directly to `NULL` in statements like you edited into your question. *And* in tables, derived tables, common table expression or views, if there's a possibility of them containing `NULL` values you should always take extra care when you do comparisons eg in you `WHERE` clause, `JOIN` conditions etc. – TT. Jan 16 '16 at 16:46
  • 1
    @dnoeth http://stackoverflow.com/a/34654738/5070879 3rd comment :) – Lukasz Szozda Jan 16 '16 at 16:46
  • For SQL Server the most important things to know: A direct comparison with `NULL` yields the `UNKNOWN` value (an under the hood boolean value you can't directly check or compare with), and that `UNKNOWN {TRUE | FALSE | UNKNOWN}` yields `UNKNOWN` except when `=OR` and the other argument is `TRUE`. If you want to know more, [here](https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/) is an excellent article written Joe Celko. – TT. Jan 16 '16 at 16:51
  • So the only way is to put a Where not ((isnull(a,'')= isnull(b,'') and isnull(c,'') = isnull(d,'')) This will sort the issue but will raise again the question of predicate having functions and will halt the server. it would be great if SqlServer gave a special function Called NullFalse(), because in C# (x=> !(x.a==x.b and x.c==x.d)) would give the desired result. – bhushanvinay Jan 18 '16 at 19:33
  • Oracle the query works like charm as no concept of nulls and as long as strings were concerned – bhushanvinay Feb 24 '16 at 13:57

1 Answers1

2

For simple comparisons, I would expect these to have the same execution plans. OR tends to prevent the use of indexes. However, it is unlikely that indexes would be used for not-equals.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786