SQL - Clarification of Between and NOT IN Combo

Question

I'm new to SQL somewhat so bear with me if this is a n00b question. So my code runs something akin to the following:

(select "Balance."CodeValue" AS "CodeValue"
       , "Balance"."OtherValue" AS "OtherValue" 
from "SomeDB"."dbo"."AValue" "Balance"
where ("Balance"."CodeValue" between 'A' and'Z' 
    or "Balance"."CodeValue" in ('ABCDEFG')) 
and "Balance"."CodeValue" NOT in ('XYZ', '1234', 'Etc') 
or "Balance"."CodeValue" between 'A' and 'Z') "Balance" 
on "SomeMatrix"."CodeValue" = "Balance"."CodeValue"

Reading it, it would seem that it checks for the "Balance"."CodeValue" to be between A and Z or in 'ABCDEFG' and not in 'XYZ', '1234', 'Etc' or between A and Z. Wouldn't the two checks for A and Z cancel each other out?

Thank you ahead of time for your assistance.

Depending on what you're trying to accomplish, you might want to clean up the clause `in ('ABCDEFG')`. If you're meaning that CodeValue is one of those single characters, you write it like this: `in ('A','B','C','D','E','F','G')`. The way you've written it above is the equivalent of `CodeValue = 'ABCDEFG'`. — BrettFromLA, Feb 03 '14 at 23:25
@BrettFromLA - I would clean it up, but it's not my code to clean; I'm just having to interpret it for management. I appreciate the notes though as I will be delving more into SQL coding over the coming months. — Nathaniel, Feb 04 '14 at 00:08

M.Ali · Answer 1 · 2014-02-03T23:33:57.697

select Balance.CodeValue AS CodeValue
       ,Balance.OtherValue AS OtherValue 
from SomeDB.dbo.AValue Balance INNER JOIN SomeMatrix
on SomeMatrix.CodeValue = Balance.CodeValue
where 
(
  Balance.CodeValue between 'A' and'Z' ----\
OR                                          -- Either of this is true
  Balance.CodeValue in ('ABCDEFG')     ----/
)     
AND                                    -- AND
(
  Balance.CodeValue 
         NOT IN ('XYZ', '1234', 'Etc')  ----\
OR                                           -- Either of this is true
  Balance.CodeValue between 'A' and 'Z' ----/
)

The precedence of operator is NOT --> AND --> OR

When you have a bit complex/Tricky NOT INs , ANDs & ORs in your WHERE clause closing related conditions in parenthesis () makes it easier to read and debug your code.

Appreciate the follow-up; it's much clearer the way you've laid it out. Thanks! — Nathaniel, Feb 04 '14 at 00:10

score 0 · Answer 2 · answered Feb 03 '14 at 23:20

No they wouldnt. SQL works on "restrictions" (litteraly). When you first run query

select * from table

It pulls out all rows from table Then after lets say this query

select * from table where table.column>5

It "hides"(again literally) all rows that are lesser or equal than 5

And so on.So when you type

select * from table table.column>5 AND table.column>5

It hides same values and you get the right result

EDIT: Actually it works on adding rows. Condition table.column>5 AND tavle.column>5 is going to be true or false for every specific row and that settles if row is added in result or not

score 0 · Answer 3 · answered Feb 03 '14 at 23:23

0

I think you're confused by the order that "and" and "or are evaluated. Values that meet ANY of these three requirements will be returned:

1) Balance.CodeValue NOT in ('XYZ', '1234', 'Etc'), and Balance.CodeValue between 'A' and'Z'

2) Balance.CodeValue NOT in ('XYZ', '1234', 'Etc'), and Balance.CodeValue in ('ABCDEFG')

3) Balance.CodeValue between 'A' and 'Z'

The last "or" condition (Balance.CodeValue between 'A' and 'Z') is separate from the 3 previous conditions, because "and" is evaluated before "or". The first 3 conditions are kind of glued together.

(Sorry for my sloppy terminology! I know the logic, just not the right words to describe it.)

answered Feb 03 '14 at 23:23

BrettFromLA

906
1
7
17

Basically everything is filtering down through the steps based on AND / OR precedence, therefore even if it met the last condition (#3) it wouldn't be returned if it didn't meet the previous two conditions correct? Thanks for your help! – Nathaniel Feb 04 '14 at 00:13
Actually, the opposite is true! If it's between A and Z, it WILL be returned, because of the final condition, after the "or" clause. "and" is only true if the conditions before and after it are both met. "or" is true if the condition before it is met, OR if the condition after it is met, but one of them could be unmet and it would still be true! So in my 3 examples above, in #1 and #2, two of the conditions before the final "or" were met. In #3, only the condition after the final "or" is met. :) – BrettFromLA Feb 04 '14 at 00:26

Hart CO · Accepted Answer · 2014-02-03T23:30:28.963

0

As written above you are correct, the first bit isn't doing anything because it's negated by the last bit:

;WITH cte AS (SELECT 'XYZ' AS CodeValue
              UNION 
              SELECT 'A')
SELECT *
FROM cte
WHERE (CodeValue between 'A' and'Z'  or CodeValue in ('ABCDEFG')) 
      AND CodeValue NOT in ('XYZ', '1234', 'Etc') 
      OR CodeValue between 'A' and 'Z'

Will Return XYZ even though XYZ is listed in the NOT IN portion.

Demonstration: SQL Fiddle

edited Feb 03 '14 at 23:30

answered Feb 03 '14 at 23:23

Hart CO

34,064
6
48
63

So basically if it doesn't meet the first two conditions, the third condtition afte the OR would be irrelevant? Thanks much for the help! – Nathaniel Feb 04 '14 at 00:15
If it doesn't meet the first two conditions, it will still be included due to the third. – Hart CO Feb 04 '14 at 04:54

SQL - Clarification of Between and NOT IN Combo

4 Answers4