Friday, 15 February 2013

What are SQL NULL Values and Examples

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 0; they are not equivalent.


SQL WORKING WITH NULL VALUES

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.

SQL 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


SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?
We will have to use the IS 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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts