Not Equal

• Scott D-810830

  SSC-Addicted

  Points: 494

  More actions

  October 27, 2008 at 6:54 am

  #889943

  It is easy to get confused with Boolean logic. An easy way to visualize what is going on is to create a table (spreadsheet) of your data. Then highlight the areas that are involved in your Boolean expression. In this case, age <> 25 are the two rightmost columns and name <> 'name1' are the four bottom rows. If this were an OR, it would include everything in either set (the UNION) of the two. But, since this is an AND, it includes only the things that are in both (the INTERSECTION). So, only the the region in the lower right meets the condition in your Boolean expression. And as you can see, only name2, name4 and name5 are in that region.

  Scott

  [font="Courier New"]

  |age <> 25

  25 |26 27

  name1 X |

  ___________________________________________

  name <> 'name1' name2 |X

  name3 X |

  name4 | X

  name5 | X

  [/font]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 28, 2008 at 2:51 am

  #890448

  Hi Sudhakara

  The problem is this: you think your statement should be operating on a row-by-row basis.

  Look at a row; if name <> 'name1' and age <> 25 then keep for output. But the statement isn't operating on a row-by-row basis, it's operating on the whole data set, as others have explained with examples.

  If you want to filter in the way you're expecting then you have to change the logic slightly:

  [font="Courier New"]DROP TABLE #not_equal

  CREATE TABLE #not_equal (

      [name] VARCHAR(10),

      age   tinyint

  );

  INSERT INTO #not_equal

  SELECT 'name1', 25 UNION

  SELECT 'name2', 26 UNION

  SELECT 'name3', 25 UNION

  SELECT 'name4', 27 UNION

  SELECT 'name5', 27;

  SELECT *

  FROM #not_equal

  WHERE NOT ([name] = 'name1' AND age = 25)

  [/font]

  Which gives:

  name age

  ---------- ----

  name2 26

  name3 25

  name4 27

  name5 27

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden