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
|
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.