MySQL | DEFAULT() Function

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



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.