1. Registered User
Join Date
Jan 2004
Posts
32

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. SQL Consultant
Join Date
Apr 2002
Location
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?

3. Registered User
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

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'))
)```

6. Registered User
Join Date
Jan 2004
Posts
32
[QUOTE][SIZE=1]Originally posted by r937

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. SQL Consultant
Join Date
Apr 2002
Location
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

8. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
okay, i understand

good luck

you're gonna need it, eh

10. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
We are sincerely sorry your code is bloated. Please accept our deepest condolences.

11. Registered User
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.

12. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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 ("^").

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

14. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

#### Posting Permissions

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