Open In App

MySQL DEFAULT Function

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

The DEFAULT() function returns the default value for table column.

DEFAULT value of a column is a value used in the case, there is no value specified by user.

In order, to use this function there should be a DEFAULT value assign to the column. Otherwise, it will generate an error.

Syntax:

DEFAULT ( column_name)

column_name: Name of column whose default value is written.

Example: Consider two relations student and result

Structure of table “student”-

Field Type Null Default
sid int(11) YES NULL
sname varchar(10) YES NULL
subject varchar(10) YES NULL
marks int(11) YES 0

Structure of table “result”-

Field Type Null Default
lowest_marks int(11) YES NULL
lohighest_marks int(11) YES NULL
grade varchar(5) YES FAIL

Data in the tables-

Select * from student;

sid sname subject marks
1 aayushi O.S 89
2 yogesh D.A.A 75
3 chestha T.O.C 0
4 manasvi O.S 60
5 hardik D.A.A 99
6 shanu E.S.D 35

Select * from result;

lowest_marks highest_marks grade
85 100 A
60 85 B
40 60 C

Problem Description: We have to find result of all the students-

Query:

Select sid, sname, subject, marks, 
    IF ( grade is NULL, DEFAULT ( grade ), grade )
AS grade  FROM  student LEFT JOIN result 
    ON marks > lowest_marks 
AND marks < = highest_marks;

Output:

sid sname subject marks grade
1 aayushi O.S 89 A
2 yogesh D.A.A 75 B
3 chestha T.O.C 0 FAIL
3 chestha T.O.C 0 FAIL
4 manasvi O.S 60 C
5 hardik D.A.A 99 A
6 shanu E.S.D 35 FAIL

Explanation: Here, default() function is use to return default grade i.e “FAIL”. This default value is used in place where student marks doesn’t match according to joining condition. Those students results is shown as FAIL.

Note: The default function with select statement will return default value for all rows. That means, instead of getting a single default value of the column, we will get list of default values for that column.

For example for above table result, Output of query is-

Select default ( grade) from result;

Output:

default ( grade )
FAIL
FAIL
FAIL


Last Updated : 18 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads