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!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?|||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.|||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))|||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!
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'))
)|||[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|||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|||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 ;)|||okay, i understand
good luck
you're gonna need it, eh :cool:|||We are sincerely sorry your code is bloated. Please accept our deepest condolences.|||I suppose you could write an XOR function. Deosn't seem like it would be that hard.|||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 ("^").|||Hmm. Thinking about it, it actually would be very hard to write.|||where
(
case
when (complex_clause_1_true )
then 1
else 0
end
^
case
when (complex_clause_2_true )
then 1
else 0
end
) = 1|||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|||The logic is simple. It's writing it as a function that is the problem.
Unfortunately, a returned boolean result is not a valid SQL server datatype and so can't be passed as a parameter without converting it first. And once you convert it you can use any of the bitwise operators, so passing them to a function doesn't gain you much.
My preferred conversion would be:
select cast(count(*) as bit) from table
...but that's just because I have a mild aversion to CASE statements.|||count(*) ? You would really rather do that? Man, you ain't seen the data then!|||Count(*) is very fast, because under most circumstances the optimizer need only pull the rowcount from the system tables.|||Count(*) is very fast, because under most circumstances the optimizer need only pull the rowcount from the system tables.
Is this true in MS-SQL... When I test this the query plan estimates reading the clustered index, and with a trace it appears that the entire index is being read. I tried this both before and after updating statistics.|||If you have additional filtering criteria it would need to resort to searching the index, so the strategy to use will ultimately depend on exactly what "complex_clause_1" and "complex_clause_2" are.
I'd be willing to bet that in blm14_cu's case the statement could be rewritten with much greater clarity using JOINs than his EXISTS clauses, after which he could use some of the Null-related functions such as ISNULL and NULLIF to implement his logic.
blm14_cu is bashing SQL Server, but poorly written code is going to be difficult to translate to any platform.|||Originally posted by blindman
If you have additional filtering criteria it would need to resort to searching the index, so the strategy to use will ultimately depend on exactly what "complex_clause_1" and "complex_clause_2" are.
I'd be willing to bet that in blm14_cu's case the statement could be rewritten with much greater clarity using JOINs than his EXISTS clauses, after which he could use some of the Null-related functions such as ISNULL and NULLIF to implement his logic.
blm14_cu is bashing SQL Server, but poorly written code is going to be difficult to translate to any platform.
Absolutely. The XOR condition in relational terms is an inverse intersection and should be convertible to NOT IN (intersection) in almost all cases.
