How to replace null entries with default values in SQL
Share
Replace null entries with default values in SQL
In SQL , a null entry is a cell in a particular relation/table with no value. It is also known as a missing entry. This null entry can sometimes be replaced with a default value.
A default value is a value used when no value is supplied in a given entry in a table.
In SQL, we can use two functions to replace null entries with a default value. These functions are COALESCE() and ISNULL(). We’ll use only the COALESCE() method in this Answer.
The COALESCE() method
The COALESCE() function returns the first non-null arguments from the given list of expressions. If the expression is null, the function returns a null. The function can also replace null entries with a given default value.
The default value is returned if the expression passed into the COALESCE() function is null. Otherwise, the expression is returned.
Syntax
We’ll use the below syntax to replace a null value:
COALESCE(expression, value)
Parameters
expression: This represents the expression that helps to operate.value: This represents the value that replaces the expression if it is null.
Example
Consider an example of a table that contains footballers’ name, age, nationality, club_name, and goal_scored. It has null entries in the goal_scored column.
Footballer table
S/N | name | club_name | age | nationality | goal_scored |
1 | Lionel Messi | FC Barcelona | 35 | Argentina | |
2 | Cristiano Ronaldo | Manchester United | 37 | Portuguese | 24 |
3 | Kevin De Bruyne | Manchester City | 31 | Belgian | 15 |
4 | Robert Lewandowski | FC Bayern Munich | 33 | Poland |
The code below shows how to use the COALESCE() function in SQL:
CREATE TABLE Footballer (id int,name varchar(50),club_name varchar(50),age int,nationality varchar (30),goal_scored int);-- Insert dataINSERT INTO FootballerVALUES (1, 'Lionel Messi', 'FC Barcelona', 35, 'Argentina', null);INSERT INTO FootballerVALUES (2, 'Cristiano Ronaldo', 'Manchester United', 37, 'Portuguese', 24);INSERT INTO FootballerVALUES (3, 'Kevin De Bruyne', 'Manchester city', 31, 'Belgian', 15);INSERT INTO FootballerVALUES (4, 'Robert Lewandowski', 'FC Bayern Munich', 33, 'Poland', null );-- QuerySELECT name, COALESCE(goal_scored, 10) goal_scoredFROM Footballer;
Explanation
Here is a line-by-line description of the code above:
-
Lines 1–8: We create a table called
Footballerthat has theid,name,club_name,age,nationalityandgoal_scoredcolumns. -
Lines 11–18: We insert data into the
Footballertable. Some entries in thegoal_scoredcolumn are not filled. -
Lines 22–23: We fetch the footballer’s name and
goal_scored. We then replace the null entries with a default value of10using theCOALESCE()function.
