Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

How to replace null entries with default values in SQL

Maria Elijah

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

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.

RELATED TAGS

sql

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring