Not Equal

Question

Not Equal

sudhakara

SSCrazy Eights

Points: 9425
More actions
October 22, 2008 at 8:10 am

#199828

Hi,
select * from not_equal
Name Age
----- ----
name226
name427
name527
name125
name325
select * from not_equal where (name <> 'name1' and age<> 25)
Name Age
----- ----
name226
name427
name527
i want know why this AND operator behave like OR operator . Please give you suggestion

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

It looks like it did precisely what you told it to. It limited the names to where they did not equal 'Name1' and it showed those for values not equal to 25. It doesn't look like an OR operation at all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

danschl SSCertifiable Points: 5770 More actions · Answer 2

name <> 'name1' and age<> 25

if name <> 'name1' or age<> 25

then you would get all the records back

not just the 3

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

I agree with Grant, that is exactly the resultset I would expect from that query. An OR would return:

name age

------ ---

name226

name427

name527

name325

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

tinku.reddy Mr or Mrs. 500 Points: 575 More actions · Answer 4

select * from not_equal where

name <> 'name1'

gives a result set of

name226

name325

name427

name527

this happened by saying not 'name1' but everything else

and now look at this

select * from not_equal where

name <> 'name1' and age <> 25

This is not give the names of 'name1' and age '25'

which gives a result set of

name226

name427

name527

ha the query gives the right result.

What is out put you are expecting from the query?

krayknot SSCertifiable Points: 7998 More actions · Answer 5

i think the query would be

select * from not_equal where (name <> 'name1' and age >= 25)

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 6

Query should show the recored which name <> 'name1' and age <> 25 .but there is one more record in the table name3 and age 25 its not coming on the result set ,why?

krayknot SSCertifiable Points: 7998 More actions · Answer 7

sudhakara (10/23/2008)
Query should show the recored which name <> 'name1' and age <> 25 .but there is one more record in the table name3 and age 25 its not coming on the result set ,why?

why it should come with the age 25 because you have applied the AND that will check both the conditions to be true

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 8

sudhakara

SSCrazy Eights

Points: 9425

October 23, 2008 at 12:59 am

#888479

but name is not name1 right

krayknot SSCertifiable Points: 7998 More actions · Answer 9

sudhakara (10/23/2008)
but name is not name1 right

yes except to the name: name1 and age: 25 all records will list

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

tinku.reddy Mr or Mrs. 500 Points: 575 More actions · Answer 10

Try this

select * from not_equal

where name <> 'name1' or age <> 25

name325

name226

name427

name527

if your query output is not this then give me the result set how you want your query

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

This gets into basic BOOLEAN LOGIC. Your where clause is this:

name <> 'name1' and age <> 25

That is equvilent to this:

not (name = 'name1' or age = 25)

For proof using your data from the original post, here is the code I put together:

create table #TestTable (

UName varchar(10),

Age tinyint

);

insert into #TestTable

select 'name1', 25 union

select 'name2', 26 union

select 'name3', 25 union

select 'name4', 27 union

select 'name5', 27;

select

*

from

#TestTable

where

UName <> 'name1'

and Age <> 25;

select

*

from

#TestTable

where

not (UName = 'name1'

or Age = 25);

drop table #TestTable;

😎

Ronald H SSCarpal Tunnel Points: 4023 More actions · Answer 12

What I think you mean that AND behaves like OR, is that most OR conditions logically returns more results than AND (name = 'name1' OR age = 25 gives more results than if you have used AND). Though the trick with using <> (or NOT for that matter) is that the logic is turned around.

In this case name may not be 'name' and the age may not be 25. So both the groups with name = 'name1' and age = 25 are excluded from the result. If you find hard to see the logic of this, rewrite the statement as NOT(name = 'name1' AND age = 25). The result would be exactly the same as name = 'name1' OR age = 25.

Ronald

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

tinku.reddy Mr or Mrs. 500 Points: 575 More actions · Answer 13

tinku.reddy

Mr or Mrs. 500

Points: 575

October 23, 2008 at 9:38 am

#888690

Exactly correct I agree with you

Ronald H SSCarpal Tunnel Points: 4023 More actions · Answer 14

Lynn Pettis (10/23/2008)
This gets into basic BOOLEAN LOGIC. Your where clause is this:
name <> 'name1' and age <> 25
That is equvilent to this:
not (name = 'name1' or age = 25)
...
😎

Haha Lynn, you beat on that 😉

Ronald

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2