Some random IN vs. NOT IN with T-SQL

-

I'll try to expand on this, but anyway, here's some examples about how you have to be careful with IN and NOT IN in T-SQL in terms of when results will be returned. If the list you're comparing to has nulls, watch out. If the item you're comparing has nulls, watch out. If the list could be empty, check for that. IN and NOT IN may behave differently than you expect in all these situations. Below are most of the iterations.


--in select 'row' where 1 in (1,2,3)--returns select 'row' where 1 in (select 1 union select 2)--returns select 'row' where 1 in (select null union select 2) --does NOT return - cannot determine if 1 "in" null, maybe it is, who knows? select 'row' where 1 in (select top 0 f1 from (select 1 f1 union select 2) qry) --NO return b/c the top 0 qry returns "nothing", and 1 is NOT "in" nothing ("in nothing" = false) --so if the scenario is that you want to return "all" if the list is empty, then you have to add a check like this: select 'row' where --include all if the list is empty not exists (select top 0 f1 from (select 1 f1 union select 2) qry) or --if the list is not empty, return matches only 1 in (select top 0 f1 from (select 1 f1 union select 2) qry) --NOW returns because the list is empty (top 0) --not in select 'row' where 1 not in (1,2,3) --no return select 'row' where 1 not in (select 1 union select 2) --no return select 'row' where 1 not in (select 2 union select 3)--returns select 'row' where 1 not in (select null union select 2) --does NOT return - cannot determine if 1 "not in" null, maybe it is, who knows? select 'row' where 1 not in (select top 0 f1 from (select 1 f1 union select 2) qry) --Returns b/c the top 0 qry returns "nothing", and 1 is "not in" nothing ("not in nothing" = true) --so if the scenario is that you want to return "all" if the list is empty, same "not exists / or" check as above: select 'row' where --include all if the list is empty not exists (select top 5 f1 from (select 1 f1 union select 2) qry) or --if the list is not empty, return matches only 1 NOT in (select top 5 f1 from (select 1 f1 union select 2) qry) --NO return b/c 1 "not in" the exclusion list = true






There you go, happy coding.

Comments

Post a Comment

Popular posts from this blog

SOPA and the NDAA

-
During the Internet blackout today against SOPA, I was complaining on VoiceOfTheDBA that I wish we could get a blackout on the recent "indefinite detention of US citizens" fiasco. I said: Unfortunately (as you know) we have bigger issues. I’d love to see a blackout for basic liberty:   http://www.washingtonpost.com/opinions/is-the-united-states-still-the-land-of-the-free/2012/01/04/gIQAvcD1wP_story.html   http://www.huffingtonpost.com/2011/11/29/senate-votes-to-let-military-detain-americans-indefinitely_n_1119473.html   http://abcnews.go.com/blogs/politics/2011/12/with-reservations-obama-signs-act-to-allow-detention-of-citizens/ But now I've read the actual final section of the law under question, and couldn't figure out what the big deal is: http://thomas.loc.gov/cgi-bin/cpquery/?&dbname=cp112&sid=cp112MdQpb&refer=&r_n=hr329p1.112&item=&&&sel=TOC_1073854& Upon further research, I see that mostly the NDAA refused to clarify our sta...
Read more

Hiding an ASPXGridView Delete button with HTMLRowCreated vs. CommandButtonInitialize

-
It's pretty typical in Administration forms to need to hide buttons on certain rows for various reasons. Particularly it is nice to hide the Delete button if in fact the row in question is being used in other places in the app. This is a nice feature to let the user know up front that the row in question is in use, rather than them trying to delete it and getting a foreign key related error after the fact. For years now in DevExpress ASPxGridView's I have been handling this in the HtmlRowCreated event. And I have been doing it wrong. Before: protected void gridGiftCodes_HtmlRowCreated ( object sender, DevExpress.Web.ASPxGridViewTableRowEventArgs e) { if (e.RowType == GridViewRowType.Data){ int commandColumnIndex = - 1 ; //Find the command column commandColumnIndex = e.Row.Cells.Count- 1 ; //assume it's the last visible colum //Get the command cell for this row GridViewTableCommandCell cell = e.Row.Cells[commandColumnIndex] as Gri...
Read more

Why I still don't use Entity Framework for small-to-medium business applications

-
This post is really about the cons of Entity Framework. I know, I know, it's sacrilege to not be in love with EF and using it everywhere, and I feel I am taking some professional risk expressing these thoughts. But I actually find Entity Framework to be problematic in many ways, both technologically and philosophically, and I am frustrated that it is now the "standard" way to do data access in Microsoft land. To be the standard implies "generally suitable for most applications", which in my experience, Entity Framework is not.
Read more