none
how to select null and is not null value from table?

    Question

  • hi all 

    i have a product table ...

    SELECT TOP 1000 [ProductId]
        
          ,[IsProduct]
          ,[StoragesRef]
          ,[UnitRef]
          ,[ProductName]
          ,[HasPrice]
          ,[Comment]
      FROM [dbo].[Product]    p

    and get this values...

    now i want select by this code ...

    SELECT * FROM Product   WHERE ProductId=15 AND StoragesRef = 50001 OR Product.StoragesRef  IS NULL

    i get this value ...

    this value is not normal 

    how to solve it 

    please help me

    thanks


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Saturday, July 27, 2013 5:58 PM

Answers

  • Hi,

    you need parentheses around the StoresRef condition, as AND has higher precedence then OR:

    SELECT * FROM Product
    WHERE ProductId=15 AND (StoragesRef = 50001 OR Product.StoragesRef IS NULL)

    Regards, Elmar

    Saturday, July 27, 2013 6:13 PM
  • I'm not sure exactly what you are looking for, but when you mix AND's and OR's the AND's are done first unless you use parenthesis.  This is analogous to when you have an arithmetic expression with both multiplication and addition and no parenthesis, the multiplications are done first.  so

    WHERE ProductID=15 AND StoragesRef=50001 OR ProductS.StoragesRef is NULL is the same as if you had written

    WHERE (ProductID=15 AND StoragesRef=50001) 
        OR ProductS.StoragesRef is NULL

    That is, you will get every row where StoragesRef is NULL plus every row where ProductID=15 AND StoragesRef=50001.

    You probably want

    WHERE ProductID=15 AND 
       (StoragesRef=50001 OR ProductS.StoragesRef is NULL)
    Tom



    Saturday, July 27, 2013 6:19 PM

All replies

  • Hi,

    you need parentheses around the StoresRef condition, as AND has higher precedence then OR:

    SELECT * FROM Product
    WHERE ProductId=15 AND (StoragesRef = 50001 OR Product.StoragesRef IS NULL)

    Regards, Elmar

    Saturday, July 27, 2013 6:13 PM
  • I'm not sure exactly what you are looking for, but when you mix AND's and OR's the AND's are done first unless you use parenthesis.  This is analogous to when you have an arithmetic expression with both multiplication and addition and no parenthesis, the multiplications are done first.  so

    WHERE ProductID=15 AND StoragesRef=50001 OR ProductS.StoragesRef is NULL is the same as if you had written

    WHERE (ProductID=15 AND StoragesRef=50001) 
        OR ProductS.StoragesRef is NULL

    That is, you will get every row where StoragesRef is NULL plus every row where ProductID=15 AND StoragesRef=50001.

    You probably want

    WHERE ProductID=15 AND 
       (StoragesRef=50001 OR ProductS.StoragesRef is NULL)
    Tom



    Saturday, July 27, 2013 6:19 PM
  • thanks all

    Name of Allah, Most Gracious, Most Merciful and He created the human

    Sunday, July 28, 2013 12:30 AM