Prev Next

MySQL COALESCE

The COALESCE function returns the first non-NULL value in a list of arguments. It is useful for handling NULL values in your data.

Examples with Tamil Kings

1. Using COALESCE to Handle NULL Values

SELECT king_name, COALESCE(reign_period, 'Unknown Period') AS period_description FROM tamil_kings_auto_increment;

Code Explanation: This query returns the reign_period if it is not NULL, or 'Unknown Period' if it is NULL.

2. Using COALESCE with Multiple Columns

SELECT COALESCE(kingdom, 'Unknown Kingdom', king_name) AS description FROM tamil_kings_auto_increment;

Code Explanation: This query returns the first non-NULL value among kingdom and king_name, or 'Unknown Kingdom' if both are NULL.

Best Practices

  • Use COALESCE to provide default values for NULL data.
  • Ensure that the arguments are in the desired order, as COALESCE returns the first non-NULL value it finds.

Key Takeaways

  • The COALESCE function handles NULL values by returning the first non-NULL argument.
  • It is useful for providing default values in your queries.
Prev Next