Try this
select * from database
where (name ='John' or name ='Michael') OR status <> 'active'

-- All except John and Michael in non-active states
WHERE NOT (name in('John','Michael') and coalesce(status,'') <> 'active')

-- All except John and Michael when they are active.
WHERE NOT (name in('John','Michael') and status = 'active')

DECLARE @T TABLE (Name VARCHAR(30), Status VARCHAR(20))

INSERT @T
VALUES
('Frank', 'Active'),
('Frank', 'Not Active'),
('John', 'Active'),
('John', 'Not Active'),
('Michael', 'Active'),
('Michael', 'Not Active')

WHERE NOT (name in('John','Michael') and status = 'active')

select * from @T
except
select * from @T where Name in('John','Michael') and Status='Active'

quote:

---

Originally posted by Sachin.Nand

How about Except ?

select * from @T
except
select * from @T where Name in('John','Michael') and Status='Active'

After Monday and Tuesday even the calendar says W T F ....

---

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by Sachin.Nand

How about Except ?

select * from @T
except
select * from @T where Name in('John','Michael') and Status='Active'

After Monday and Tuesday even the calendar says W T F ....

---

While logically correct, this would result in higher query cost due to the need for two passes against the table followed by the merge join or nested loops or whatever else query optimizer may choose to filter the data.

---

quote:

---

I have a query where I want all data except when name = john or michael and if their status is active

---

quote:

---

Was performance an issue here ?

---

quote:

---

The reason I showed the example with except was because the OP had actually bolded the word except in his original post.

---

use AdventureWorks
go

set statistics io on
go
select distinct CarrierTrackingNumber from Sales.SalesOrderDetail where CarrierTrackingNumber  not in ('2E63-4616-B1') and CarrierTrackingNumber IS not null
go

select CarrierTrackingNumber from Sales.SalesOrderDetail where CarrierTrackingNumber IS not null
except
select '2E63-4616-B1'

quote:

---

Originally posted by Sachin.Nand

If you want to talk about performance then lets see..you suggested NOT IN.Well for your kind info it is one of the most pathetically worst performing operators.So your humble opinion of you suggesting an "optimized query" to the OP does not stand. Need a proof ???

Check this out..

use AdventureWorks
go

set statistics io on
go
select distinct CarrierTrackingNumber from Sales.SalesOrderDetail where CarrierTrackingNumber  not in ('2E63-4616-B1') and CarrierTrackingNumber IS not null
go

select CarrierTrackingNumber from Sales.SalesOrderDetail where CarrierTrackingNumber IS not null
except
select '2E63-4616-B1'

Logical reads with the NOT IN is 327 and the one with Except is 323.

So accordingly keeping your suggestion in mind of "Writing code with performance in mind only when performance becomes an issue is not a sound practice" Except performs better than NOT IN.



After Monday and Tuesday even the calendar says W T F ....

---

quote:

---

Originally posted by Sachin.Nand

The point here is not on suggesting the OP on which method is better and which one is not but more of rather refraining from making one liner suggestions "of performance tips" without any substantial proof.

Also are you really serious in suggesting me to test performance on a table having 6 records ?

Once again I would reiterate that performance of the queries vary in different scenarios.So my initial posting on the thread was to make OP aware of the EXCEPT operator and nothing more than that.

After Monday and Tuesday even the calendar says W T F ....

---

quote:

---

Originally posted by Sachin.Nand

Are you telling me to check the performance on a table that has a design which is no where near to what a RDBMS is suppossed to be.I am surely not gonna do that.

After Monday and Tuesday even the calendar says W T F ....

---

quote:

---

If you want to talk about performance then lets see..you suggested NOT IN.Well for your kind info it is one of the most pathetically worst performing operators.So your humble opinion of you suggesting an "optimized query" to the OP does not stand. Need a proof ???

---