SELECT inquiry in T-SQL (continued 4)
Due to the house problem, I can't draw it. This is not now in the afternoon break, and the "SELECT inquiry" series of "T-SQL" is tail!
Today, let us discuss the "null value" related questions!
Use a null value in the relational database model to represent the missing data. In practice, null value description has not yet to enter the corresponding data to the database, or a particular record line does not need to use this column. From the perspective of technology, null value represents "unknown value", any calculation result of any expression containing null value is also null. For example, if the amount of money in a bank account is unknown, and an asset combination includes this account of this account, the total value of the entire asset portfolio is unknown. In this way, this concept is also correct in SQL. Use a more famous DBA to say: "Null value takes the life of all other values."
Some developers are reluctant to use this null value because null values have the characteristics of the calculation result of the entire expression. They do not use null values when developing a database, but set the default value of the column to other replacement null values (such as empty strings, 0, or 'n / a'). Other database developers believe that unknown values cannot be used to use 0 or empty strings with a 0 or empty string only for the convenience of code. I belong to this second camp. Null values are valuable for the database because they provide important information about the status of the data, so it is worth writing code to properly check and process null values.
(1) Detecting null value
To know, an null value is not equal to another null value. That is, if the amount of funds in the account number 123 is unknown, the amount of funds in the account number 234 is also unknown, then, the amount of funds from the two accounts cannot be proved to be equal, because The equal operator cannot detect null values, so SQL introduces a special operator IS to detect equivalence between special values, and its syntax is:
Where expression is null
If: if Null Is Null
SELECT 'IS'
Else
SELECT 'IS Not'
The result of the execution is: IS
Another example: The following query only retrieves those customers who have nickname columns:
SELECT Firstname, Lastname, Nickname
From dbo.customer
WHERE NICKNAME IS NULL
Order by lastname, firstname
Its execution is:
Firstname lastname nickname
------------------------------------------------ -------------
Debbie andrews Null
Dave Bettys Null
Jay Brown Null
...
Note: 1. Use = to detect null values, the result is unsuccessful!
2. The polymerization function is different from the method used to accumulate the data contained in the column containing null values (i.e., the null value is added to a non-empty value is also a null value), and the polymerization function (eg SUM) (), AVG (), etc.) tend to ignore null values.
(2) Handling null value
When providing data for reports, end users, or some applications, null values are unpopular, usually need to convert null values to a valid value to facilitate understanding of data. ISNULL () and coalesce () functions can convert null values to valid values, and nullif () functions can generate null values according to the specified conditions.
In order to handle more complex cases, SQL Server uses three-state logic when processing Boolean expressions, comparing null values with true values, and the result is also an null value.
1. Use the isnull () function
First pay attention to it is different from the is NULL lookup condition. The isnull () function has two parameters, the first parameter is a single column or expression, and the second parameter is an alternate value. If the value of the first parameter is a valid value (non-empty), the ISNULL () function returns the value of the first parameter. If the value of the first parameter is null, ISNULL () will return the value of the second parameter to replace null values, such as:
SELECT Firstname, Lastname, Isnull (Nickname, 'None')
From customer
Order by lastname, firstname
The execution result is:
Firstname lastname nickname
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
Joe Adams Slim
Melissa Anderson Missy
Debbie Andrews None
Dave Bettys None
...
WARNING: 1.iNULL () and nullif () functions are T-SQL proprietary, they do not conform to ANSI's SQL standard!!
2. Coalesce () function
It can use multiple expressions or columns as parameters and return the first non-empty value, such as: SELECT COALESCE (NULL, 1 NULL, 1 2, 'ABC')
Its execution is: 3
3.Nullif () function
Nullif () has two parameters, if these two parameters are equal, it returns an null value; otherwise, it will return the value of the previous parameter. Such as: The following example will convert the empty string in the nickname column to null values. For the description, I use the first UPDATE statement to change a customer's nickname to an empty string:
Update Customer
SET NICKNAME = ''
Where lastname = 'adams'
Select Lastname, Firstname,
Case Nickname
When '' Ten 'Blank'
Else Nickname
End as nickname,
Nullif (Nickname, '') as Nicknamenullif
From dbo.customer
Where lastname in ('adams', 'Anderson', 'Andrews')
Order by lastname, firstname
The result of the execution is:
Lastname firstname Nickname Nicknamenullif
-------------------------------------------------------------------------------------------------------------------------------------------- ------ -------------------------- Adams Joe Blank NULL
Anderson Melissa Missy Missy
Andrews Debbie Null NULL
The third column uses the case expression to display the empty string as "Blank", and in the fourth column, nullif () does convert the empty string to null values. In other cases, Melissa's nickname is not affected by nullif (), and the null value on the NickName column has not changed.
Ok, I, I am with everyone's exchange of "SELECT inquiry in T-SQL"!
I really hope that you are comment, or leave a message, or send E-mail.
, Make progress together! Thank you! ~
