# Where clause to include and exclude sql

## All we need is an easy explanation of the problem, so here it is.

I have two tables where I am selecting values and then filtering them so that:

1. `BookId` would be 11 or 12
2. `C.Id` is not 5515, 7582, 7648
3. `C.Id` can be 5967 , but if `D.part` is equal to 8, then do not inlude row which has `C.Id=5967 and D.part = 8`

This is my select:

``````select * from dbo.C c
cross join dbo.D d
where
c.Id = d.Id
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and (c.Id not in (5515, 7582, 7648) -- 5515, 7582, 7648 (do not include)
or (c.Id = 5967 AND d.part <> 8)) -- include 5967 but not with part = 8
``````

it all works fine except the last line `(c.Id = 5967 AND d.part <> 8), I still see this row in my results. What I’m doing wrong here?

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

I interpret the sentence: "D.part is equal to 8, then do not include row which has C.Id=5967" as:

``````(D.part = 8) => (C.Id <> 5967)
``````

This is logically equivalent with:

``````NOT (D.part = 8) OR (C.Id <> 5967)
(D.part <> 8) OR (C.Id <> 5967)
``````

This can be rewritten as:

``````NOT (D.part = 8 AND C.Id = 5967)
``````

Your complete query would then be:

``````SELECT *
FROM dbo.C c
CROSS JOIN dbo.D d
WHERE c.Id = d.Id
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
AND (c.Id not in (5515, 7582, 7648) -- 5515, 7582, 7648 (do not include)
AND NOT (D.part = 8 AND C.Id = 5967) -- include 5967 but not with part = 8
``````

I would suggest rewriting the query with a JOIN instead of CARTESIAN PRODUCT

``````SELECT *
FROM dbo.C c
JOIN dbo.D d
ON c.Id = d.Id
WHERE (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
AND (c.Id not in (5515, 7582, 7648) -- 5515, 7582, 7648 (do not include)
AND NOT (D.part = 8 AND C.Id = 5967) -- include 5967 but not with part = 8
``````

### Method 2

With this specific part of the condition:

``````(c.Id not in (5515, 7582, 7648)  or  (c.Id = 5967 AND d.part <> 8))
``````

a row where `c.Id` is 5967 and `d.part` 8 will be evaluated like this

``````(c.Id not in (5515, 7582, 7648)  or  (c.Id = 5967 AND d.part <> 8))
↓                                 ↓
true              or              false         →      true
``````

So you can see why this row would be returned.

I can see more than one way to change your condition. Here is one option:

``````c.Id = d.Id
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and (c.Id not in (5515, 7582, 7648)) -- 5515, 7582, 7648 (do not include)
and (c.Id <> 5967 or d.part <> 8) -- exclude 5967 too unless part <> 8
``````

Here is another:

``````c.Id = d.Id
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and (c.Id not in (5515, 7582, 7648, 5967) -- 5515, 7582, 7648, 5967 (do not include)
or (c.Id = 5967 and d.part <> 8)) -- include 5967 but not with part = 8
``````

The second one is almost identical to yours. The only difference is that 5967 is included in the `in` list. It might seem redundant to list the same value in multiple predicates, though the way the logic is expressed I believe it is perfectly fine. Still, the first option has no such repetition in case that is your preference, and the results produced would be the same.

On a different note, please consider taking full advantage of the explicit `JOIN` syntax to separate different kinds of conditions. The `c.Id = d.Id` part is a joining condition while the others are filtering conditions. A joining condition would make most sense in the `ON` clause. So instead of `CROSS JOIN ... WHERE <joining_condition>`, use `INNER JOIN ... ON <joining_condition>`, like this:

``````select *
from dbo.C c
inner join dbo.D d on c.Id = d.Id  -- joining condition, moved from WHERE
where  -- filtering conditions go here
(c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and ... /* the rest of the conditions */
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0