Friday, February 15, 2013

What are NULL Values in SQL Server

SQL NULL Values
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a Null Value.

NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.
Note: It is not possible to compare NULL and Blank; they are not equivalent.

Working with NULL Values in SQL Server :

Look at the following "Customers" table:
EMP_ID
F_Name
L_Name
Dept_ID
Address
Salary
1001
Ravi
Kumar
1
Pune
20000
1002
David
Smith
2
NULL
35000
1003
Victory
Venkatesh
1
Bangalore
50000
1004
Tamatam
Reddy
3
NULL
25000
1005
William
Smith
2
Pune
40000
1006
King
Fisher
6
Bangalore
30000
Suppose that the "Address" column in the "Customers" table is optional. This means that if we insert a record with no value for the "Address" column, the "Address" column will be saved with a NULL value.

Testing for NULL values:

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL
To select only the records with NULL values in the "Address" column.
We will have to use the IS NULL operator:
SELECT Emp_Id,F_Name,L_Name,Address FROM Customers WHERE Address IS NULL
The result-set will look like this:
EMP_ID
F_Name
L_Name
Dept_ID
Address
Salary
1002
David
Smith
2
NULL
35000
1004
Tamatam
Reddy
3
NULL
25000

NOT NULL

We can select only the records with no NULL values in the "Address" column using the NOT NULL operator:
SELECT Emp_Id,F_Name,L_Name,Address FROM Customers
WHERE Address IS NOT NULL

The result-set will look like this:

EMP_ID
F_Name
L_Name
Dept_ID
Address
Salary
1001
Ravi
Kumar
1
Pune
20000
1003
Victory
Venkatesh
1
Bangalore
50000
1005
William
Smith
2
Pune
40000
1006
King
Fisher
6
Bangalore
30000

--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog