SQL变量默认值问题

 

在SQL Server中定义一个SQL变量后，如果未给变量赋值默认是NULL；

declare @i varchar(1)
 --set @i =''
if @i  <>'Y'
 print 'rr'
 else
 print 'EE'
 if @i is null
 print 'gg'

－－结果：

EE

gg

 

 

/// <summary>
/// Handles the Click event of the RequestLanguageChange control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void RequestLanguageChange_Click(object sender, EventArgs e)
{
    LinkButton senderLink = sender as LinkButton;

    if (senderLink != null)
    {
        // Store requested language as new culture in the session
        Session["Culture"] = senderLink.CommandArgument;

        // Reload last requested page with new culture
        Server.Transfer(Request.Path);
    }
}

UNKNOWN，下面有分析）

原因分析：
1. NULL,充当一个占位符，相当于一个未知值的变量，NULL <>10的结果既不是TRUE，也不是False，而是未知（UNKONWN）。
2. 任何类型的变量如果未赋值都是NULL，NULL和任何值的比较结果都是未知。SQL中这种变量的特性叫做3VL （Three－Valued logic）。
3.下图为具有3VL特性变量关系运算的结果

4.如果WHERE条件的结果是False或UNKNOWN，select的结果集是没有任何数据。 INSERT、UPDATE、DELETE也一样，如果WHERE条件的结果是UNKNOWN，将不会影响任何行。
5.当SET ANSI_NULLS OFF时可以使用<>或＝与NULL进行比较，但是默认情况下SQL SERVER是SET ANSI_NULLS ON的，也即不能用<>或=与NULL进行比较，ISO标准推荐是用Is NULL、Is Not NULL进行判断。如下图

6.在实际的使用过程中，应尽量避免更改SET ANSI_NULLS，如果随意更改，会造成混淆。微软不推荐使用SET ANSI_NULLS OFF，SQL Server以后的版本（2008以后？）将会删掉SET ANSI_NULLS 特性。

7.Select statement的Where条件使用ISULL(tbl.col1,'') <> 'Y',用Isnull才能够过滤出col1为NULL的记录，或者and Col1 is null;

 

NULL Trouble

Remember that T-SQL uses three-valued logic. In this section, I will demonstrate problems that can evolve with subqueries when NULLs are involved, and you do not take into consideration the three-valued logic.

Consider the following seemingly intuitive query that is supposed to return customers who did not place orders:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
                    FROM Sales.Orders AS O);

 

With the current sample data in the Orders table in the TSQLFundamentals2008 database the query seems to work the way you expect it to, and indeed it returns two rows for the two customers who did not place orders:

custid      companyname
----------- ----------------
22          Customer DTDMN
57          Customer WVAXS

 

Next, run the following code to insert a new order to the Orders table with a NULL customer ID:

INSERT INTO Sales.Orders
  (custid, empid, orderdate, requireddate, shippeddate, shipperid,
   freight, shipname, shipaddress, shipcity, shipregion,
   shippostalcode, shipcountry)
  VALUES(NULL, 1, '20090212', '20090212',
         '20090212', 1, 123.00, N'abc', N'abc', N'abc',
         N'abc', N'abc', N'abc');

 

Run the query that is supposed to return customers who did not place orders again:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
                    FROM Sales.Orders AS O);

 

This time the query returns an empty set. Keeping in mind what you've read in the section about NULLs in Chapter 2, try to explain why the query returns an empty set. Also try to think of ways to get customers 22 and 57 in the output, and in general, to figure out best practices you can follow to avoid such problems, assuming there is a problem here.

Obviously, the culprit in this story is the NULL customer ID that was added to the Orders table and is now returned by the subquery among the known customer IDs.

Let's start with the part that behaves the way you expect it to. The IN predicate returns TRUE for a customer who placed orders (for example, customer 85) because such a customer is returned by the subquery. The NOT operator is used to negate the IN predicate; hence, the NOT TRUE becomes FALSE, and the customer is not returned by the outer query. This means that when a customer ID appears in the Orders table, you can tell for sure that the customer placed orders, and therefore you don't want to see it in the output. However, having a NULL customer ID in the Orders table, you can't tell for sure whether a certain customer ID does not appear in Orders, as explained shortly.

The IN predicate returns UNKNOWN (the truth value UNKNOWN like the truth values TRUE and FALSE) for a customer such as 22 that does not appear in the set of known customer IDs in Orders. The IN predicate returns UNKNOWN for such a customer because comparing it with all known customer IDs yields FALSE, and comparing it with the NULL in the set yields UNKNOWN. FALSE OR UNKNOWN yields UNKNOWN. As a more tangible example, consider the expression 22 NOT IN (1, 2, NULL). This expression can be rephrased as NOT 22 IN (1, 2, NULL). You can expand the last expression to NOT (22 = 1 OR 22 = 2 OR 22 = NULL). Evaluate each individual expression in the parentheses to its truth value and you get NOT (FALSE OR FALSE OR UNKNOWN), which translates to NOT UNKNOWN, which evaluates to UNKNOWN.

The logical meaning of UNKNOWN here before you apply the NOT operator is that you can't tell for sure whether the customer ID appears in the set, because the NULL could represent that customer ID as well as anything else. The tricky part is that negating the UNKNOWN with the NOT operator still yields UNKNOWN, and UNKNOWN in a query filter is filtered out. This means that in a case where it is unknown whether a customer ID appears in a set, it is also unknown whether it doesn't appear in the set.

In short, when you use the NOT IN predicate against a subquery that returns at least one NULL, the outer query always returns an empty set. Values from the outer table that are known to appear in the set are not returned because the outer query is supposed to return values that do not appear in the set. Values that do not appear in the set of known values are not returned because you can never tell for sure that the value is not in the set that includes the NULL.

So, what practices can you follow to avoid such trouble?

First, when a column is not supposed to allow NULLs, it is important to define it as NOT NULL. Enforcing data integrity is much more important than many people realize.

Second, in all queries that you write you should consider all three possible truth values of a three-valued logic (TRUE, FALSE, and UNKNOWN). Think explicitly about whether the query might process NULLs, and if so, whether the default treatment of NULLs is suitable for your needs. When it isn't, you need to intervene. For example, in our case the outer query returns an empty set because of the comparison with NULL. If you want to check whether a customer ID appears in the set of known values and ignore the NULLs, you should exclude the NULLs—either explicitly or implicitly. An example of explicitly excluding the NULLs is by adding the predicate O.custid IS NOT NULL to the subquery, like so:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
                    FROM Sales.Orders AS O
                    WHERE O.custid IS NOT NULL);

 

An example of excluding the NULLs implicitly is using the NOT EXISTS predicate instead of NOT IN, like so:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
  (SELECT *
   FROM Sales.Orders AS O
   WHERE O.custid = C.custid);

 

Recall that unlike IN, EXISTS uses two-valued predicate logic. EXISTS always returns TRUE or FALSE and never UNKNOWN. When the subquery stumbles into a NULL in O.custid, the expression evaluates to UNKNOWN and the row is filtered out. As far as the EXISTS predicate is concerned, the NULL cases are eliminated naturally, as though they weren't there. So EXISTS ends up handling only known customer IDs. Therefore, it's safer to use NOT EXISTS than NOT IN.

When you're done experimenting, run the following code for cleanup:

DELETE FROM Sales.Orders WHERE custid IS NULL;
DBCC CHECKIDENT('Sales.Orders', RESEED, 11077);

转载于:https://www.cnblogs.com/weiflyes/archive/2009/07/02/1515299.html