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

NULL functions in SQL


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

IFNULL
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

NVL in SQL
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

NULL functions in SQL
Statement:
SELECT NULLIF(Actual, Predicted) AS Values
FROM Store;
SQL Interview questions

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 – https://thedatamonk.com/walmart-data-analyst-questions/
American Express– https://thedatamonk.com/american-express-data-analyst-questions/
Barclays– https://thedatamonk.com/barclays-data-analyst-interview-questions/
Lowes– https://thedatamonk.com/lowes-data-science-interview-questions/
Factspan– https://thedatamonk.com/factspan-interview-questions/
Oracle– https://thedatamonk.com/oracle-data-analyst-interview-questions/
SAP– https://thedatamonk.com/sap-data-science-interview-questions/
Big Basket– https://thedatamonk.com/big-basket-data-analyst-interview-questions/
Swiggy – https://thedatamonk.com/swiggy-data-analyst-interview-questions/
Accenture – https://thedatamonk.com/accenture-business-analyst-interview-question/
Deloitte – https://thedatamonk.com/deloitte-data-scientist-interview-questions/
Amazon – https://thedatamonk.com/amazon-data-science-interview-questions/
Myntra – https://thedatamonk.com/myntra-data-science-interview-questions-2/
Box8 – https://thedatamonk.com/box8-data-analyst-interview-questions-2/ 
Flipkart – https://thedatamonk.com/flipkart-business-analyst-interview-questions/

The Data Monk Interview Books – Don’t Miss

Now we are also available on our website where you can directly download the PDF of the topic you are interested in. At Amazon, each book costs ~299, on our website we have put it at a 60-80% discount. There are ~4000 solved interview questions prepared for you.

10 e-book bundle with 1400 interview questions spread across SQL, Python, Statistics, Case Studies, and Machine Learning Algorithms – Ideal for 0-3 years experienced candidates

23 E-book with ~2000 interview questions spread across AWS, SQL, Python, 10+ ML algorithms, MS Excel, and Case Studies – Complete Package for someone between 0 to 8 years of experience (The above 10 e-book bundle has a completely different set of e-books)

12 E-books for 12 Machine Learning algorithms with 1000+ interview questions – For those candidates who want to include any Machine Learning Algorithm in their resume and to learn/revise the important concepts. These 12 e-books are a part of the 23 e-book package

Individual 50+ e-books on separate topics

Important Resources to crack interviews (Mostly Free)

There are a few things which might be very useful for your preparation

The Data Monk Youtube channel – Here you will get only those videos that are asked in interviews for Data Analysts, Data Scientists, Machine Learning Engineers, Business Intelligence Engineers, Analytics managers, etc.
Go through the watchlist which makes you uncomfortable:-

All the list of 200 videos
Complete Python Playlist for Data Science
Company-wise Data Science Interview Questions – Must Watch
All important Machine Learning Algorithm with code in Python
Complete Python Numpy Playlist
Complete Python Pandas Playlist
SQL Complete Playlist
Case Study and Guesstimates Complete Playlist
Complete Playlist of Statistics

Author: TheDataMonk

I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)