How to replace null entries with default values in SQL

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 data
INSERT INTO Footballer
VALUES (1, 'Lionel Messi', 'FC Barcelona', 35, 'Argentina', null);
INSERT INTO Footballer
VALUES (2, 'Cristiano Ronaldo', 'Manchester United', 37, 'Portuguese', 24);
INSERT INTO Footballer
VALUES (3, 'Kevin De Bruyne', 'Manchester city', 31, 'Belgian', 15);
INSERT INTO Footballer
VALUES (4, 'Robert Lewandowski', 'FC Bayern Munich', 33, 'Poland', null );
-- Query 
SELECT name, COALESCE(goal_scored, 10) goal_scored
FROM Footballer;

Explanation

Here is a line-by-line description of the code above:

  • Lines 1–8: We create a table called Footballer that has the id, name, club_name, age, nationality and goal_scored columns.

  • Lines 11–18: We insert data into the Footballer table. Some entries in the goal_scored column 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 of 10 using the COALESCE() function.

New on Educative
Learn to Code
Learn any Language as a beginner
Develop a human edge in an AI powered world and learn to code with AI from our beginner friendly catalog
🏆 Leaderboard
Daily Coding Challenge
Solve a new coding challenge every day and climb the leaderboard

Free Resources

License: Creative Commons-Attribution NonCommercial-ShareAlike 4.0 (CC-BY-NC-SA 4.0)