Xor between two conditions in SQL Server

Question

There are OR and AND operators in T-SQL. AND means when two conditions are true and OR means if only first condition is true, if only second condition is true or if both conditions are true.

But I need something like XOR which is like OR but if both are true, the WHERE condition is not applied.

Example :

select * 
from [Table] 
where Id in (@FromId) XOR TypeId = @TypeId

My question is: how should I write this query so if Id in (@FromId) found, it doesn't check TypeId =@TypeId and if Id in (@FromId) isn't found, it checks for TypeId =@TypeId .

I know that I can write this for solving the problem :

select * from [Table] where Id in (@FromId) 
if @@ROWCOUNT =0
select * from  [Table] where TypeId =@TypeId

but I want write it in one query, for simplicity, performance, less code ,....

Thanks in advance.

Add table DDL sample data and desired results and you'll probably see. — David Browne - Microsoft, Aug 21 '20 at 21:02
so is there any solution to this kind of problem or not? I cant write this kind of queries in one query? — 334253465375347, Aug 21 '20 at 21:05
It's a duplicate of [this](https://stackoverflow.com/questions/5411619/t-sql-xor-operator) — SteveC, Aug 21 '20 at 21:06
See https://stackoverflow.com/help/minimal-reproducible-example — David Browne - Microsoft, Aug 21 '20 at 21:07
I have read it before and i Think my question is in this rules. I check this two links from you two dears but i couldnt find my answer. maybe i have to more google — 334253465375347, Aug 21 '20 at 21:11
If you've read it, I think you should read it again. Minimal reproducible example for SQL queries: Show us `CREATE TABLE` and `INSERT` statements with enough sample data to cover all of your use cases (a row that meets one criteria, a row that meets the other, a row that meets both, and a row that meets neither), then show us that the result should be in each case. — Aaron Bertrand, Aug 21 '20 at 21:22
My understanding of what you're asking changed as I re-read your question. You're saying that you want to run the second query if-and-only-if the first returns ZERO results. If the first query returns even a single row, you don't want to evaluate the second expression. Yes? — Duncan, Aug 21 '20 at 21:41

score 1 · Answer 1 · answered Aug 22 '20 at 01:52

1

XOR in sql server is ^. Please give it a try

answered Aug 22 '20 at 01:52

Conor Cunningham MSFT

4,151
1
15
21

score 0 · Answer 2 · answered Aug 21 '20 at 21:44

0

I'm not crazy about this, but...

if exists(select * from [Table] where Id in (@FromId)) begin
    select * from [Table] where Id in (@FromId)
end
else begin
    select * from [Table] where TypeId = @TypeId
end

Is @FromId a CSV or other iterable entity? I would transform it into a table and join, as opposed to using the IN operator and suffering a performance hit.

answered Aug 21 '20 at 21:44

Duncan

582
4
21

Thank you for reply . but I think my code which is in question body is better because in your code we have 3 select in worst situtation but in my code only two. – 334253465375347 Aug 21 '20 at 21:51

score 0 · Answer 3 · answered Aug 21 '20 at 22:58

0

You can do this in a single query with window functions:

select t.*
from (
    select t.*, max(case when id in (@FromId) then 1 else 0 end) has_from_id
    from mytable t
) t
where id in (@FromId) or (has_from_id = 0 and typeId = @typeId)

Another typical approach is union all and not exists:

select t.*
from mytable t
where id in (@FromId)
union all
select t.*
from mytable t
where 
    typeId = @typeId
    and not exists (select 1 from mytable where id in (@FromId))

answered Aug 21 '20 at 22:58

GMB

216,147
25
84
135

Thank you but what about performance??? I need simplicity, performance, less code – 334253465375347 Aug 21 '20 at 23:10
I found this answer the most comprehensible of the ones I read across SO, personally. Thanks. The performance should be decent for either of these, they're trivial for even the most obtuse query planner to optimize. – Gavin Ray Nov 03 '22 at 21:16

score 0 · Accepted Answer · answered Aug 21 '20 at 23:24

Basically an XOR can be expressed in terms of AND and OR as follows:

 (cond1 AND NOT cond2) OR (NOT cond1 AND cond2)

So your query can be written as

select * 
from [Table] 
where (Id in (@FromId) AND TypeId <> @TypeId) OR (Id NOT IN (@FromId) AND TypeId = @TypeId)

Xor between two conditions in SQL Server

4 Answers4