The aim of this tutorial is to show you how to substitute NULL values in a MySQL query with empty strings with the use of the MySQL COALESCE function. This function is handy in that it will minimize not having to do NULL checks on data in the programming layer.
Prerequisites
Before you begin this guide you'll need the following:
- One Ubuntu 18.04 server is configured with a firewall and a non-root user. You can refer to the Initial Server Setup with Ubuntu 18.04 guide to configuring your server.
- A MySQL server is set up as detailed in this How To Install MySQL on Ubuntu 18.04 tutorial. Ensure you set a root password for the MySQL server.
Step 1 - Access Your Server And Mysql Environment
First, ssh into your Ubuntu 18.04 server:
ssh user_name@your_server_ip
Then, run the following command to log in to the MySQL server as the a normal user:
sudo mysql -u digitalriver -p
Step 2 - Let's Review A Database Table
Let's preview a table where data is typically set to NULL. We will look at a typical customer_address table that has a state field typically set to null because it is not applicable to a user.
Run the following command to query:
mysql> SHOW COLUMNS FROM customer_address;
You should get an output similar to the following:
Output
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | BIGINT(11) | NO | PRI | NULL | auto_increment |
| customerId | BIGINT(11) | NO | | NULL | |
| address1 | VARCHAR(255) | NO | | | |
| address2 | VARCHAR(255) | NO | | | |
| city | VARCHAR(100) | NO | | | |
| state | VARCHAR(50) | Yes | | NULL | |
| postalCode | INT(11) | No | | NULL | |
| country | VARCHAR(255) | No | | | |
+-------------+---------------+------+-----+---------+----------------+
Step 3 - Run A Standard Query On Table
By executing a normal query on the table it would return data that contains NULL values because it is not applicable in this case the state field would be NULL.
mysql> SELECT address1, address2, city, state, postalCode, country FROM customer_address WHERE postalCode = '2052';
You should get an output similar to the following:
Output
+-------------------+------------------------+-----------------------+-----------------+----------------------------+
| address1 | address2 | city | state | postalCode | country |
+-------------------+------------------------+--------------+--------+-----------------+----------------------------+
| 15 Jacob Drive | South Western Township | Johannesburg | NULL | 2052 | South Africa |
| 18 John Street | South Western Township | Johannesburg | NULL | 2052 | South Africa |
+------+------------+------------------------+--------------+--------+-----------------+----------------------------+
2 rows in set (0.01 sec)
As you will notice that the query returns NULL values on the state column.
Step 4 - Avoiding NULL values from appearing on your programming layer
You can now try to change the query to return "N/A" instead of NULL values appearing in your programming layer by changing the query to the following :
mysql> SELECT address1, address2, city, COALESCE(state,'N/A'), postalCode, country FROM customer_address WHERE postalCode = '2052';
You should get an output similar to the following:
Output
+-------------------+-------------------------+--------------+--------+------------+--------------+
| address1 | address2 | city | state | postalCode | country |
+-------------------+-------------------------+--------------+--------+------------+--------------+
| 15 Jacob Drive | South Western Township | Johannesburg | N/A | 2052 | South Africa |
| 18 John Street | South Western Township | Johannesburg | N/A | 2052 | South Africa |
+-------------------+-------------------------+--------------+--------+------------+--------------+
2 rows in set (0.01 sec)
You will notice that the state column now returns "N/A" for NULL values. If the value in the state column is NULL, the COALESCE function will substitute it with the "N/A" string. Otherwise, it returns the value of the state column.
Conclusion
Dealing with NULL values that come from the data layer can add unnecessary checking on the programming layer and this type of function can assist you in avoiding or limiting NULL values coming from the data layer.
Comments
Post a Comment