Hate for null in SQL

Source: Internet
Author: User

I accidentally wrote the following script in SQL today.

Select defaposipositionid from tablename where userid = 1100528 and defaultpositionid = NULL

After the execution, I was surprised why there was no result. Using select * From tablename, the column is indeed null. How can I find out whether my character is wrong? So I wrote the following judgment:

If (null = NULL)
Begin
Print 'fff'
End
Else
Begin
Print 'gggggggg'
End

The output result is as follows:

Is there a difference between null and null.

Search engine. in SQL, null is a data type. null cannot be used with any column or variable "! = "Compare

To determine whether a column or variable is null, you can only use is (not) null to determine whether its return value is true or false.

Use of null

Null Value and Index
If a column has a null value, you cannot create a unique index on this column. You can create a non-unique index;However, if a field has many rows with null values, the indexing effect on this field is not good. Therefore, we recommend that you do not create an index on a field with frequent null values (to be confirmed).

Null and sorting
Null always exists as the minimum value during sorting, that is, when order by col ASC, the row where col is null is at the beginning, and vice versa.

Note: three functions related to null in T-SQL:
Isnull (check_expression, replacement_value)

The check_expression and replacement_value data types must be consistent.
If check_expression is null, replacement_value is returned.
If check_expression is not null, check_expression is returned.
 

NullifUsed to check two expressions. Syntax:
Nullif (expression, expression)

If two expressions are equal, null is returned, which is the data type of the first expression.
If two expressions are not equal, the first expression is returned.

Coalesce ()A function can accept a series of values. If all items in the list are null, only one value is used. Then, it returns the first non-null value. This technique describes two ways to use coalesce () functions in SQL Server.

Help connection: http://msdn.microsoft.com/zh-cn/library/ms172138.aspx

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.