MySQL Cleaning and Transformation Commands: CAST, COALESCE, CASE and More

5 min readNov 3, 2024

MySQL offers a variety of powerful functions to manipulate and query data effectively. This article explores essential functions such as CAST, COALESCE, CASE, IFNULL, and UNION, which enhance data handling capabilities. Understanding these functions is crucial for building robust SQL queries and optimizing data retrieval in MySQL.

CAST

The CAST function is used to convert a value into a specified datatype. This is particularly useful when we want to ensure that data is in the correct format for calculations or comparisons. This transformation lasts only for the duration of the query.

Syntax: CAST(expression AS type)

-- Example:
SELECT CAST('2024-10-30' AS DATE) AS activity_date;
SELECT CAST('1550.99' AS DECIMAL(7,2) AS sales;
SELECT CAST(NOW() AS DATE);

COALESCE Function

COALESCE is a SQL function that takes multiple arguments and returns the first non-NULL value. If all values are NULL, it returns NULL. This function is useful for handling empty values in your data, and there is no limit to the number of arguments.

Question 1:

Given the Users table, write an SQL query to retrieve each user’s UserID, Full_Name, and contact information. The Users table includes phone_number and email columns. The contact information should display phone_number if it is available; if it is NULL, then display email. If both are NULL, print the text ‘No contact information provided’.

SELECT 
    UserID, 
    Full_Name, 
    COALESCE(Phone_Number, Email, 'No contact information provided') AS contact_info
FROM 
    Users;

In this query, the COALESCE function checks the phone_number column first, then email column, and if both are NULL, it returns the message 'No contact information provided'.

Knowledge Check Question

Using the provided Orders table, write an SQL query to return the OrderID, CustomerName, OrderDate, and delivery status. For orders that have not been delivered, where the DeliveryDate column is NULL, display ‘NOT DELIVERED’ instead of NULL.

CASE Function

The CASE function is one of the most common control flow functions used in MySQL, operating similarly to an if-then-else statement. It evaluates a series of conditions within a case block and returns the value associated with the first true condition. If no conditions are met, it will return the value specified in the ELSE clause; if there is no ELSE clause and no conditions are true, it returns NULL.

The syntax for a CASE expression starts with the CASE keyword, indicating the beginning of the case block. Conditions are defined using the WHEN and THEN clauses, and the block concludes with the END clause. You can also add an alias for the resulting expression if needed.

The CASE expression can be utilized in SELECT, WHERE, and ORDER BY clauses.

Assume we have a table called employees:

Now we want to categorize employees based on their performance scores. We can use the CASE function to create a new column that assigns a performance rating.

SELECT 
    EmployeeID, 
    Name, 
    PerformanceScore,
    CASE 
        WHEN performance_score >= 90 THEN 'Excellent'
        WHEN performance_score >= 75 THEN 'Good'
        WHEN performance_score >= 50 THEN 'Average'
        ELSE 'Poor'
    END AS Rating
FROM employees;

Here the CASE function evaluates each employee’s performance score and assigns a corresponding rating.

IFNULL

IFNULL is a function that takes two arguments and returns the first argument if it is not NULL. If the first argument is NULL, it returns the second argument instead. These arguments can be literal values or expressions, making IFNULL useful for providing default values when dealing with NULLs in your data.

SELECT IFNULL(discount, 0) AS final_discount
FROM orders;

In this query, the IFNULL function checks the discount column. If it is NULL, it returns 0. Otherwise, it returns the value of discount column.

UNION and UNION ALL

The UNION operator in SQL is used to combine the results of two or more SELECT statements into a single result set. To utilize UNION, both SELECT statements must have the same number of columns, with matching data types and order. The UNION operator inherently eliminates duplicate rows from the combined results, returning only distinct values from the queried tables. This makes it a useful tool for consolidating data from different sources while ensuring that each value appears only once.

If you want to include all values, including duplicates, you can use the UNION ALL keyword. This modification allows all records, regardless of duplication, to be included in the final result set. When constructing SQL queries with the UNION operator, it’s important to adhere to best practices, such as maintaining column consistency across the SELECT statements, to ensure smooth execution and accurate results.

Example

Assume we have two tables: Customers_USA and Customers_Canada, which contain customer names and customer IDs.

To combine the customer names and their IDs from both tables we can use UNION operator. The UNION operator ensures distinct values in the results, meaning that if the same customer name appears in both tables, it will be shown only once.

SELECT CustomerID, CustomerName FROM Customers_USA
UNION
SELECT CustomerID, CustomerName FROM Customers_Canada;

If we want to include all names and IDs, including duplicates, wecan use UNION ALL:

SELECT CustomerID, CustomerName FROM Customers_USA
UNION ALL
SELECT CustomerID, CustomerName FROM Customers_Canada;

In Conclusion, learning to use MySQL functions like CAST, COALESCE, CASE, IFNULL, and UNION is essential for effective data management. These functions help you clean and organize your data, ensuring both accuracy and consistency.

If you found this article helpful, consider following for more tips and tutorials on MySQL and data analysis!