Understanding with AND

  • I have the following table which was taken from a practice problem for the Microsoft 98-364 exam.

    SQL Table

    The following code returns 4 rows.

      SELECT *
    FROM Cars c
    WHERE c.Origin <> 'USA' AND c.Color <> 'Black';

    I understand Boolean logic, but for some reason I can't understand this one.  There is only one occurrence where both of these conditions are true (row 7).  Therefore I would expect "not equal" would return all other rows, which are 7 rows.

    So how does this return 4 rows, instead of 7 rows?

     

  • issue is that you are not looking for equality but for inequality - hence if either of the conditions is true it meets the criteria

    so for your desired result it could be written as

    where not (c.origin = 'USA' and c.color = 'Black')

    e.g. all cases were both conditions are NOT true

  • ok - it should return 4, anything that does not contain black OR/AND USA

    it has to satisfy both conditions to return the row, so if it has black , but not usa - it wont return

    if it has usa but not black - it wont return

    if it has both it wont return

    if it has neither then it will return

     

     

    MVDBA

  • If you write out the truth table you'll see what is going on:

    Color     Country    Color<>'Black'    Country<>'USA'
    Red Japan 1 1
    Black Japan 0 1
    Red USA 1 0
    etc...
  • From your description it sounds like you want it to use OR instead of AND:

     SELECT *
    FROM Cars c
    WHERE c.Origin <> 'USA' OR c.Color <> 'Black';

Viewing 5 posts - 1 through 4 (of 4 total)

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