SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

NULL Data in table

Null mean missing or unknown value. Null is not equal to zero or blank space data in a column. Null is also not same as empty string. So to match a Null value we can use IS NULL condition in our SQL statement to get the records from a database table.

For all our examples we have modified our student table by making class and mark column to accept null and changed the data to add some null values. You can download and install a copy of this table to test in your database. The SQL dump of student3 is available at the end of this page.

SELECT* FROM`student3` WHERE class IS NULL

This will display all the records which have NULL value for class column. Similarly we can display all the records which do not have null value for the class. Like this

SELECT * FROM `student3` WHERE class IS NOT NULL

Now we will get all the records without having NULL value in class column

Making the column data to null

We can make all the class data ( or column ) equal to NULL by updating the record like this

update `student3` set class = null

We can't change the value of the column ( class here ) if the property is set to NOT NULL for the column. To make the column null for all the records we first have to make the table accept NULL value by updating the structure of the table.

ALTER TABLE `student3` CHANGE `class` `class` VARCHAR( 10 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT ''

After this change we can add NULL value to the record.

Deleting records with null value

We can delete records for which some column has null value. Here it is

DELETE FROM student3 WHERE class IS NULL

As we know null value means unknown data so how to use the value ? A situation may come where we have to multiply or apply any other mathematical calculation on all the data of a column ( say Mark in our student table ) which have some null data also. For this we have to use different sql commands like IFNULL, COALESCE, NVL. We will learn in next Part.

FNULL, COALESCE, NVL


Download sql dump of student3 table

Be the first to post comment on this article :


Google+

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer