Mr.Ken Mr.Ken - 6 months ago 15
SQL Question

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 include 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.


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.