SQL Where clause excluding more than it should

Question

I have 2 tables with a join and a where clause. Example contents of the 2 tables:

Id         FieldA                   Id       FieldB
 1           100                     1        Yellow
 2           100                     2        Green
 3           200                     3        Green
 4           200                     4        Blue
 5           300                     5        Yellow
 6           300                     6        Orange

I am trying to return everything except where fieldA = 200 AND fieldB = Green. So it should still return line 2 that has fieldA = 100 and FieldB = Green. However, here is my query and it is not working. It is excluding all rows with 200 and green in them:

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE (t1.FieldA <> 200 AND t2.FieldB <> 'Green')

The way I see it, after running this query the only row excluded should be row 3 because it has fielda = 200 and fieldb = green, but instead it only returns row1, row 5 & row6. It seems to me that it should only do that if I am using an OR.

Let me know where I am going wrong, and here is some DDL so you can play with it:

create table dbo.test1
(
    Id int not null,
    FieldA int
)

create table dbo.test2
(
    Id int not null,
    FieldB varchar(10)
)

INSERT INTO test1 (Id, FieldA)
VALUES 
    (1,100),
    (2,100),
    (3,200),
    (4,200),
    (5,300),
    (6,300)

INSERT INTO test2 (Id, FieldB)
VALUES 
    (1,'Yellow'),
    (2,'Green'),
    (3,'Green'),
    (4,'Blue'),
    (5,'Yellow'),
    (6,'Orange')

Answer 1

Each condition is being evaluated independently against the whole set of rows. To combine them, flip your operators and negate the combination, like so:

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE not (t1.FieldA = 200 AND t2.FieldB = 'Green')

Your original query was basically saying, first eliminate all the rows where FieldA is not 200, and then, from the rows that remain, eliminate all the ones where FieldB is not 'Green'.

When you want both conditions to apply for a given row, you first select for the conditions you want to exclude, which is why you switch from <> to =, then make your WHERE clause exclude the whole thing by applying the NOT operator.

EDIT re: comment

I think the confusion about the results returned by your original query and the idea that conditions in parenthesis are "evaluated as one" might stem from the fact that logical negation is not distributive, i.e. the negation of A && B is not ~A && ~B, but rather ~(A && B).

Your first sentence describing the results you want is pretty close to the correct t-sql for the query. You say "I am trying to return everything except where fieldA = 200 AND fieldB = Green." The last part of the sentence is your where clause, i.e.

except where fieldA = 200 AND fieldB = Green

Substitute "not" for "except"

not where fieldA = 200 AND fieldB = Green
-- or, to make the grouping explicit
not (where fieldA = 200 AND fieldB = Green)

and clean it up to be valid t-sql syntax

where not (fieldA = 200 AND fieldB = Green)

By contrast, the English equivalent to WHERE (t1.FieldA <> 200 AND t2.FieldB <> 'Green') might be: return everything where field1 is anything but 200 and field1 is anything but green. In which case a match for either 200 or green would be sufficient to exclude the row.

To see why rows 2 and 4 were erroneously excluded, consider the truth table for your original where clause:

                       Field1 <> 200

                         T       F
                     -----------------
                   T |   T   |   F   |
                     |       | row 4 |
Field2 <> 'Green'    -----------------
                   F |   F   |   F   |
                     | row 2 |       |
                     -----------------

In other words, row 2 gets excluded because Field2 = 'Green', making the condition Field2 <> 'Green' evaluate to FALSE, so it doesn't matter what Field1 is, because FALSE and any other value is always FALSE.

Answer 2

When you do a join on Id, it creates a temporary table as follows

Id  FieldA  FieldB
1   100  Yellow
2   100  Green
3   200  Green
4   200  Blue
5   300  Yellow
6   300  Orange

When you say FieldA <> 200, the rows 3 and 4 gets excluded and the remaining rows will be 1,2,5,6. Now when you say FieldB <> Green,then row 2 gets excluded resulting in Rows 1,5,6.

Note: Conditions in 'where' Clause are not applied based on the order you specify. Rather, it is applied on the sql execution plan at run time and the order of specifying the where conditions will not have any impact on the result.

To get the result, use the below condition

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE NOT (t1.FieldA = 200 AND t2.FieldB = 'Green')