开发者

SQL: check if a table contains no nulls in a particular column

I'm working on a stored procedure and at one point I want to check my table variable to see if there are exactly zero rows with a specific field equal null. In other words, for every row in my table, this field has a non-null value. I'd like to not do it by checking count(), but by using EXISTS.

Currently I'm doing this:

IF EXISTS ( SELECT * FROM @sometable where somefield IS NULL ) 
       SET @somefield = @somefield /* just a way to skip this */
ELSE
       /* perform some logic */

Now I know I can use NOT EXISTS, but I assume a NOT EXISTS is much slower than an EXISTS. I swear I read something recently (on SO I think) that had an example of how to do what I want to do with one Exists clause but I can't find it and neglected to bookmark it. Though maybe I'm mistaken.

The reason I set up an if/else is because from my understanding EXISTS will return before a NOT EXISTS and since this is a pretty intensive stored procedure (called quite frequently and sometimes in bulk) I'm hoping to save some processing here.

Maybe I'm looking at this all wrong or assuming something incorrectly. I'm here to learn so point me in the right direction if I've got it all wrong.

Edit: Apparently there is n开发者_如何学Pythono difference in this context (not part of a where clause/subquery). But suppose I WERE using exists in a where clause and wanted to check for the exact scenario above (ALL non-null values in a field) particularly when the table in question could potentially have thousands of rows. [kind of new to SO, if I should post this as its own question, please let me know]


Both EXISTS or NOT EXISTS in this format will stop when they find a row and give true/false as appropriate.

The IF will jump accordingly and there îs not much to optimise here...


NOT EXISTS and EXISTS should take the same amount of time. Either one can be derived by first computing the other and negating the result.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜