NULL functions in SQL | Day 42
Day 42 – Today we will cover all the five types of NULL functions in SQL.
These functions are very useful while you deal with large datasets.
The article has been prepared by Prakash Dwivedy
Let’s start with NULL functions in SQL
ISNULL() : This is used in both MYSQL and SQL Server. In SQL Server this function is used to replace a
null value with a user value. In MySQL if the expression is null this will return 1, if the expression is not
null this will return 0
ex:
ISNULL (argument1,argument2)
returns argument2 if argument1 is NULL otherwise argument1 is
returned
Statement :
SELECT SUM (ISNULL (Salary, 1000) AS Salary
FROM Employee;
IFNULL() : This function is used in MYSQL and has the same functionality as ISNULL() used for SQL Server. It takes 2 arguments and returns the first argument if it is not null and returns second argument
if the first argument contains a null value
ex :
IFNULL(argument1,argument2) returns argument2 if argument1 is NULL otherwise argument1 is
returned
Statement : SELECT SUM (IFNULL (Salary, 1000) AS Salary FROM Employee;
NVL() : This function is used in Oracle and has same feature of ISNULL() and IFNULL() functions used are MySQL and SQL Server respectively. It replaces a null value with another value, and it takes two arguments
ex :
NVL(argument1,argument2) returns argument2 if argument1 is NULL otherwise argument1 is returned
Statement : SELECT SUM (NVL (Salary, 1000) AS Salary FROM Employee;
NULLIF() : This function is used to compare two arguments. If the two arguments are equal it will return null value, otherwise the first argument is returned
ex :
NULLIF(argument1,argument2) returns null value if argument1=argument2 otherwise argument1 is returned
Statement: SELECT NULLIF(Actual, Predicted) AS Values FROM Store;
Coalesce() : This function is supported by SQL Server. This is also known as shortcut to a case statement. It returns the first not null values.
ex :
coalesce(exp1,exp2,….) returns the value found in exp1 if the value is not NULL; however, if it is NULL, then exp2 is returned
Statement: SELECT Name, coalesce(Phone1,Phone2) AS Contact FROM Employee;
You can also practice Interview Questions below
Walmart – http://thedatamonk.com/walmart-data-analyst-questions/
American Express– http://thedatamonk.com/american-express-data-analyst-questions/
Barclays– http://thedatamonk.com/barclays-data-analyst-interview-questions/
Lowes– http://thedatamonk.com/lowes-data-science-interview-questions/
Factspan– http://thedatamonk.com/factspan-interview-questions/
Oracle– http://thedatamonk.com/oracle-data-analyst-interview-questions/
SAP– http://thedatamonk.com/sap-data-science-interview-questions/
Big Basket– http://thedatamonk.com/big-basket-data-analyst-interview-questions/
Swiggy – http://thedatamonk.com/swiggy-data-analyst-interview-questions/
Accenture – http://thedatamonk.com/accenture-business-analyst-interview-question/
Deloitte – http://thedatamonk.com/deloitte-data-scientist-interview-questions/
Amazon – http://thedatamonk.com/amazon-data-science-interview-questions/
Myntra – http://thedatamonk.com/myntra-data-science-interview-questions-2/
Box8 – http://thedatamonk.com/box8-data-analyst-interview-questions-2/
Flipkart – http://thedatamonk.com/flipkart-business-analyst-interview-questions/
If you want any guidance or want to make a career in Data Science, then you can take a look on our proposed Data science path
Connect with us on Linkedin for latest updates and Data Science e-books giveaways- Nitin Kamal (Co-Founder, The Data Monk)
Our Linkedin Page for latest updates and giveaways- https://www.linkedin.com/company/14573769
Keep Learning!
Leave a reply