MySQL Functions:

List Functions

• Independent of datatypes (Example: null value)

• Pessimistic querying → searching for null values
• select * from emp where comm = null; ❌ ← No row selected, doesn’t give error.
  • When you search for null value, result is nothing ASCII value of null is 0(zero). Because there is nothing.
• select * from emp where comm is null; ✔️
  • Generates output because special treatment is given to null.
  • note: is null: special operator
• select * from emp where comm != null; ❌ ← no row selected
• select * from emp where comm is not null; ✔️

note: any comparision done with null, returns null.

• select Sal + Comm from emp; ❌
• select Sal + IFNULL(Comm,0) from emp; ✔️

note: if null function source code

if comm is null then
	return 0;
else
	return comm;
end if

• ideal sol: select IFNULL(Sal, 0) + IFNULL(Comm, 0) from emp; ✔️

5500
6000
700

1. IFNULL

SELECT IFNULL(NULL, 'Default') AS result;

Explanation: