Null and Blank values in MySQL
Null is absence of value. The blank is value that is empty string. Let's say if we need to create customer table with different columns. There is possibility that sometime we don't have any last name. So we will leave the column without inserting any value, As we are not inserting any value in last name then it will have null ( Absence of value).
There could be scenarios when you will insert the value but blank. Let's create our table and then insert leave few column value as Null and insert some blank values.
CREATE TABLE `customer` (
`firstname` varchar(50) NULL,
`lastname` varchar(30) NULL,
`age` int(11) DEFAULT NULL,
`phonenumber` char(11) DEFAULT NULL,
`dob` date DEFAULT NULL,
`gender` char(1) NOT NULL
) ;
Let's insert some values in customer table.
`firstname` varchar(50) NULL,
`lastname` varchar(30) NULL,
`age` int(11) DEFAULT NULL,
`phonenumber` char(11) DEFAULT NULL,
`dob` date DEFAULT NULL,
`gender` char(1) NOT NULL
) ;
Let's insert some values in customer table.
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Bob',Null,59,'505-4141969','1980-01-01','M'),
(4,'Robert','',59,'505-4141969','1980-01-01','M'),
(5,'Lisa',' ',59,'505-4141969','1980-01-01','M');
values
(1,'Aamir','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Bob',Null,59,'505-4141969','1980-01-01','M'),
(4,'Robert','',59,'505-4141969','1980-01-01','M'),
(5,'Lisa',' ',59,'505-4141969','1980-01-01','M');
Now if you want to get the records from customer table which don't have value (absent of value) in lastname column. you can use below query.
select * from customer where lastname is NULL;
If we need to find all the rows which has blank value in lastname column, and you can see in our insert statement we have blank values which is equal to '' or ' ' or more blank spaces.
We can use ltrim and rtrim to trim to single blank space to compare and get all the records.
select * from customer
where Rtrim(ltrim(lastname))='';
where Rtrim(ltrim(lastname))='';
