Understanding with AND
-
I have the following table which was taken from a practice problem for the Microsoft 98-364 exam.
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?
- This topic was modified 5 years, 1 month ago by michael.leach2015.
-
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