Handling Missing Data When Upload to Mysql
Summary: in this tutorial, you will larn how to work with MySQL NULL values. In add-on, y'all'll larn some useful functions to deal with the NULL values effectively.
Introduction to MySQL NULL values
In MySQL, a NULL value ways unknown. A NULL value is different from zero (0) or an empty string ''.
A NULL value is not equal to anything, even itself. If you compare a NULL value with some other Zilch value or any other value, the effect is Zip considering the value of each Zip value is unknown.
By and large, you lot apply the NULL value to indicate that the information is missing, unknown, or not applicable. For example, the phone number of a potential client may be Nothing and can be added subsequently.
When y'all create a table, you can specify whether a column accepts NULL values or not by using the Not Zilch constraint.
For example, the following argument creates the leads table:
Drib Table IF EXISTS leads; CREATE TABLE leads ( id INT AUTO_INCREMENT PRIMARY Primal, first_name VARCHAR(fifty) Non NULL, last_name VARCHAR(50) NOT NULL, source VARCHAR(255) Not NULL, email VARCHAR(100), phone VARCHAR(25) );
Code language: SQL (Structured Query Language) ( sql ) In this leads tabular array, the column id is the primary central cavalcade, therefore, it does not accept any NULL value.
The first_name, last_name, and source columns use the Non NULL constraints, hence, you cannot insert whatever Null values into these columns, whereas the email and phone columns accept NULL values.
You lot can utilise a NULL value in the INSERT argument to specify that the data is missing. For instance, the following statement inserts a row into the leads table. Because the telephone number is missing, and so a NULL value is used.
INSERT INTO leads(first_name,last_name,source,email,phone) VALUE('John','Doe','Web Search','john.doe@acme.com',NULL);
Code language: SQL (Structured Query Language) ( sql ) Because the default value of the e-mail column is NULL, you can omit the e-mail in the INSERT statement every bit follows:
INSERT INTO leads(first_name,last_name,source,phone) VALUES ('Lily','Bush','Common cold Calling','(408)-555-1234'), ('David','William','Web Search','(408)-888-6789');
Code language: SQL (Structured Query Language) ( sql ) 
MySQL Ready NULL in UPDATE statement
To set the value of a column to NULL, you utilize the assignment operator ( =). For example, to update the phone of David William to NULL, you apply the followingUPDATE statement:
UPDATE leads Fix telephone = Nix WHERE id = iii;
Lawmaking language: SQL (Structured Query Linguistic communication) ( sql ) MySQL Lodge Past with NULL
If you use the ORDER Past clause to sort the result prepare in the ascending order, MySQL considers Null values are lower than other values, therefore, information technology presents the NULL values get-go.
The following statement sorts the leads by phone number in ascending order.
SELECT * FROM leads ORDER BY telephone;
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
In case you use the Gild BY DESC, the NULL values appear at final of the effect set. Meet the following example:
SELECT * FROM leads ORDER By phone DESC;
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
To test for NULL in a query, yous use the IS Zilch or IS NOT Goose egg operator in the WHERE clause.
For example, to get the leads who have non yet provided the phone number, you use the IS NULL operator every bit follows:
SELECT * FROM leads WHERE phone IS Zippo;
Code language: SQL (Structured Query Language) ( sql ) 
Yous tin use the IS NOT operator to get all leads who provided the email addresses.
SELECT * FROM leads WHERE electronic mail IS NOT NULL;
Code language: SQL (Structured Query Language) ( sql ) 
Even though the NULL is not equal to NULL, 2 Zippo values are equal in the GROUP BY clause.
SELECT id, first_name, last_name, email, phone FROM leads Group BY email;
Code language: SQL (Structured Query Language) ( sql ) 
The query returns only ii rows because the rows whose electronic mail column is NULL are grouped into one.
MySQL NULL and UNIQUE index
When you use a UNIQUE constraint or UNIQUE alphabetize on a column, you lot tin can insert multiple Nix values into that cavalcade. It is perfectly fine because in this case, MySQL considers NULL values are distinct.
Let's verify this bespeak by creating a UNIQUE index for the telephone column.
CREATE UNIQUE Alphabetize idx_phone ON leads(phone);
Code language: SQL (Structured Query Language) ( sql ) Notice that if you utilise the BDB storage engine, MySQL considers the NULL values are equal therefore y'all cannot insert multiple Cypher values into a column that has a unique constraint.
MySQL Zip functions
MySQL provides several useful functions that handle Cipher effectively: IFNULL, COALESCE, and NULLIF.
The IFNULL function accepts two parameters. The IFNULL function returns the offset argument if it is non Cypher, otherwise, it returns the second argument.
For instance, the following statement returns the telephone number if information technology is not Cipher otherwise, it returns N/A instead of Null.
SELECT id, first_name, last_name, IFNULL(phone, 'Due north/A') phone FROM leads;
Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) 
The COALESCE function accepts a list of arguments and returns the first not-Zip statement. For example, you can use the COALESCE function to display the contact information of a lead based on the priority of the information in the following order: phone, electronic mail, and North/A.
SELECT id, first_name, last_name, Coagulate(phone, email, 'Due north/A') contact FROM leads;
Lawmaking language: SQL (Structured Query Linguistic communication) ( sql ) 
The NULLIF role accepts two arguments. If the two arguments are equal, the NULLIF function returns NULL. Otherwise, it returns the first argument.
The NULLIF function is useful when yous accept both Zero and empty string values in a column. For case, by fault, y'all insert a following row into the leads table:
INSERT INTO leads(first_name,last_name,source,email,phone) VALUE('Thierry','Henry','Web Search','thierry.henry@instance.com','');
Code language: SQL (Structured Query Language) ( sql ) The phone is an empty string instead of NULL.
If you want to become the contact information of leads, you cease up with an empty phone instead of the electronic mail equally the post-obit query:
SELECT id, first_name, last_name, Coalesce(phone, email, 'N/A') contact FROM leads;
Lawmaking language: SQL (Structured Query Language) ( sql ) 
To set this, y'all use the NULLIF function to compare the phone with the empty cord, if they are equal, information technology returns NULL, otherwise, it returns the phone number.
SELECT id, first_name, last_name, Coagulate(NULLIF(telephone, ''), electronic mail, 'North/A') contact FROM leads;
Code language: SQL (Structured Query Language) ( sql ) 
In this tutorial, you have learned how to work with MySQL NULL and how to use some handy functions to handle NULL in queries.
Was this tutorial helpful?
Source: https://www.mysqltutorial.org/mysql-null/
Belum ada Komentar untuk "Handling Missing Data When Upload to Mysql"
Posting Komentar