SQL: JOIN vs IN vs EXISTS – the Logical difference

原来 pre tag 也可以加style。

结论就是,只用Exists好了。

倒没说 left join 的问题。


SQL Server: JOIN vs IN vs EXISTS – the logical difference

Monday, March 18, 2013 I want some Moore

We’ve all written a CASE expression (yes, it’s an expression and not a statement) or two every now and then. But did you know there are actually 2 formats you can write the CASE expression in? This actually bit me when I was trying to add some new functionality to an old stored procedure. In some rare cases the stored procedure just didn’t work correctly. After a quick look it turned out to be a CASE expression problem when dealing with NULLS.

In the first format we make simple “equals to” comparisons to a value:

SELECT CASE <value>
WHEN <equals this value> THEN <return this>

WHEN <equals this value> THEN <return this>
-- ... more WHEN's here
ELSE <return this>
END

Second format is much more flexible since it allows for complex conditions. USE THIS ONE!

SELECT  CASE
WHEN <value> <compared to> <value> THEN <return this>
WHEN <value> <compared to> <value> THEN <return this>
-- ... more WHEN's here
ELSE <return this>
END

Now that we know both formats and you know which to use (the second one if that hasn’t been clear enough) here’s an example how the first format WILL make your evaluation logic WRONG.

Run the following code for different values of @i. Just comment out any 2 out of 3 “SELECT @i =” statements.

DECLARE @i INT
SELECT  @i = -1 -- first result
SELECT  @i = 55 -- second result
SELECT  @i = NULL -- third result



SELECT @i AS OriginalValue,

-- first CASE format. DON'T USE THIS!
CASE @i
WHEN -1 THEN '-1'
WHEN NULL THEN 'We have a NULL!'
ELSE 'We landed in ELSE'
END AS DontUseThisCaseFormatValue,

-- second CASE format. USE THIS!
CASE
WHEN @i = -1 THEN '-1'
WHEN @i IS NULL THEN 'We have a NULL!'
ELSE 'We landed in ELSE'
END AS UseThisCaseFormatValue

When the value of @i is –1 everything works as expected, since both formats go into the –1 WHEN branch.

CaseResult1_thumb

When the value of @i is 55 everything again works as expected, since both formats go into the ELSE branch.

CaseResult2_thumb

When the value of @i is NULL the problems become evident. The first format doesn’t go into the WHEN NULL branch because it makes an equality comparison between two NULLs.
Because a NULL is an unknown value: NULL = NULL is false. That is why the first format goes into the ELSE Branch but the second format correctly handles the proper IS NULL comparison.

CaseResult3_thumb_1

Please use the second more explicit format. Your future self will be very grateful to you when he doesn’t have to discover these kinds of bugs.

Posted on 2013-05-12, in IT and tagged , . Bookmark the permalink. 留下评论.

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Google photo

您正在使用您的 Google 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: