CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Mr.Ken
Mr.Ken

Reputation: 87

AND - OR in SQL Server

My code as : 

Select 
    ProductId, ProductCode, Color
From 
    dbo.Product 
Where 
   @Productname = ProductName 
   and ( Color != 'Orange' or Color != 'Green')

And result includes product with color 'Orange', 'Green' :

    1   prod0001   Orange
    2   prod0002   Blue
    3   prod0003   Yellow
    4   prod0004   Green
    5   prod0005   Orange
    6   prod0006   Blue
    7   prod0007   Yellow
    8   prod0008   Green

I want to get product with color != green or != orange.

Upvotes: 1

Views: 54

Answers (3)

Hugo Jose
Hugo Jose

Reputation: 369

you can use 'not in' or 'and' , like that

Select ProductId,ProductCode,Color From dbo.Product where Color not in ('Orange','Green');

Select ProductId,ProductCode,Color From dbo.Product where Color <> 'Orange' and Color <> 'Green'

Or will not work, it would be illogical, because a register that is green is not orange and a register that is orange is not green. because of that it get the both.

Upvotes: 0

Duc Nguyen
Duc Nguyen

Reputation: 588

You mean is color !=green or !=orange?

Color NOT IN ('green', 'orange')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want and, not or. But, this is more easily written using not in:

Select ProductId, ProductCode, Color
From dbo.Product
Where @Productname = ProductName and 
      Color not in ('Orange', 'Green') ;

Your logic is:

(color <> 'Orange') or (color <> 'Green')

Well, this is always true. If color is Orange, then the first clause is false, but the second is true: false or true is true. However, not in is simpler to write and clearer than just replacing the or with and.

Upvotes: 5

Related Questions