Null and Blank values in MySQL - MySQL Developer Tutorial
In the world of MySQL, we encounter these things called "null" and "blank" values. Now, a null value is like that moment when you're searching your pockets for your keys, and you realize they're just not there - it's the absence of a value, like a blank space in your pockets where your keys should be.
Now, a blank value, on the other hand, is like having a pocket that's there, but it's empty. Think of it as having an empty slot where something could fit, but you haven't put anything in it yet. For example, if we're making a table for customers, and one of the columns is for their last name, there might be times when someone doesn't have a last name to put in. So, you'd leave that slot empty, and that's like a null value - a space where there's nothing.
Let's dive into making a customer table together, just like setting up a table for a dinner party. We'll have columns for things like the first name, last name, age, phone number, date of birth, and gender. Now, sometimes our guests might not have a last name to share, so we'd leave that space empty - that's the null value. And imagine this: sometimes we might even have guests who bring an empty plate to the table. That's a blank value - it's there, but it's got nothing in it.
So, here's how we'd create our fancy table for the customers:
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);
Now, let's say we're adding some customers to our table, just like new friends arriving at the party. Some guests have complete information, like their first name, last name, and so on. But some might just give us their first name and forget to tell us their last name - that's where we'll have a null value. And then, there are some who give us an empty plate instead of a last name - that's a blank value.
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');
Now, let's say you want to gather all the guests who didn't provide us with a last name, those are the folks with null values in the last name slot. You'd use this magic spell (SQL query) to get them:
SELECT * FROM customer WHERE lastname IS NULL;
But what if you're curious about the guests who handed us empty plates (blank values) instead of a last name? Well, in our table, we've got some sneaky spaces in those empty plates, like if someone forgot to clear their plate after dinner. To catch them, we use this trick with `ltrim` and `rtrim` - it's like trimming the edges of the empty plate to see if there's anything hidden there. Here's how you'd do it:
SELECT * FROM customerWHERE RTRIM(LTRIM(lastname)) = '';
And there you have it, the world of null and blank values in MySQL, brought to life in a way that makes it feel like you're hosting a dinner party for data. Cheers to making sense of these quirks in the world of databases! đŊ️đ