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 |
Recommended Posts:
- MySQL | BIN() Function
- MySQL | Group_CONCAT() Function
- MySQL | LEAD() and LAG() Function
- MySQL | LAST_DAY() Function
- MySQL | Creating stored function
- MongoDB vs MySQL
- PHP | MySQL WHERE Clause
- IFNULL in MySQL
- How to get ID of the last updated row in MySQL?
- PHP | MySQL ORDER BY Clause
- PHP | MySQL Delete Query
- PHP | MySQL Database Introduction
- PHP | MySQL ( Creating Database )
- MySQL | Operator precedence
- PHP | MySQL UPDATE Query
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.




