Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: non-bitwise exclusive or?

    Is there a way to do a logical exclusive OR (XOR) in sql server?

    I'm trying to do this in where clause, something like:

    WHERE
    (not exists (select 1 from table a where a.date > '01/30/03') XOR
    exists (select 1 from table a where a.date < '01/30/03'))

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no idea what you mean by "exclusive or" -- do you mean one is true or the other is true but not both false and not both true?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    32
    Originally posted by r937
    no idea what you mean by "exclusive or" -- do you mean one is true or the other is true but not both false and not both true?
    Regular "OR" logic with boolean values a & b:

    a = true, b = false returns TRUE
    a = false, b = true returns TRUE
    a = true, b = true returns TRUE
    a = false, b = false returns FALSE

    Exclusive "OR" logic (XOR) with same:

    a = true, b = false returns TRUE
    a = false, b = true returns TRUE
    a = true, b = true returns FALSE
    a = false, b = false returns FALSE

    In other words XOR returns true when ONLY one of the two arguments is true, but not both.

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    the general structure is:

    declare @a int
    declare @b int

    set @a=1
    set @b=1

    if not ( (@a=1) AND (@b=1) ) AND not((@a=0) AND (@b=0))

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, translating this to your example, your two conditions are

    a: not exists (select 1 from table a where a.date > '01/30/03'
    b: exists (select 1 from table a where a.date < '01/30/03')

    and you want exactly one of these to be true

    that's easy!

    Code:
    WHERE (
          (not exists (select 1 from table a 
                       where a.date > '01/30/03'))
      AND
      NOT (exists (select 1 from table a 
                   where a.date < '01/30/03'))
          )
    OR    (
      NOT (not exists (select 1 from table a 
                       where a.date > '01/30/03'))
      AND (exists (select 1 from table a 
                   where a.date < '01/30/03'))
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    32
    [QUOTE][SIZE=1]Originally posted by r937
    okay, translating this to your example, your two conditions are

    yeah but I was hoping to be able to do it using a single operator like you can in ORACLE, which uses XOR. O well, guess my monstrous SQL will get more so, and more proof that microsoft products suck hehe

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rather than look at the shortcomings of the database language, i would examine my conditions a little more closely

    the WHERE clause comes down to either

    1) table1 contains no dates at all, or else at most one date, 01/30/03
    or
    2) table1 contains at least one date on each side of 01/30/03, and possibly even 01/30/03 itself

    i cannot imagine where this might be a real world situation that you want to filter for, but i can tell you one thing, i would certainly code it so that the purpose of the filtering is crystal clear, rather than obfuscate the meaning using some fancy logical operator that the next guiy to maintain the code (which could be you yourself six months from now) first has to figure out before even touching the code

    just because a particular language feature exists doesn't mean it is appropriate to use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Posts
    32
    I think I was somewhat unclear. I actually just made up the two date clauses. My real clauses are very very large and so I didnt want to replicate them in here because that would have just been confusing.

    I didn't realize that XOR was such a fancy operator

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i understand

    good luck

    you're gonna need it, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We are sincerely sorry your code is bloated. Please accept our deepest condolences.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I suppose you could write an XOR function. Deosn't seem like it would be that hard.
    -bpd

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You would have to pass boolean values as parameters, or convert them to bits (in which case you could just use the bitwise EXCLUSIVE OR operator ("^").
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Hmm. Thinking about it, it actually would be very hard to write.
    -bpd

  14. #14
    Join Date
    Nov 2003
    Posts
    94
    where
    (
    case
    when (complex_clause_1_true )
    then 1
    else 0
    end
    ^
    case
    when (complex_clause_2_true )
    then 1
    else 0
    end
    ) = 1

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    HanafiH, that is insidious, devious, marginally incomprehensible...

    ... and gorgeous!

    nice one

    i would put a great big humungous comment next to that ^ explaining that it's not a typo, it's an actual legitimate operator

    first time i've seen it, and i had to go look it up in BOL to confirm

    kudos to you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •