MySQL DEFAULT Function
Last Updated :
18 Mar, 2024
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 |
Share your thoughts in the comments
Please Login to comment...