What is the difference between IS NULL operator and ISNULL() function?

 ISNULL() function lets you return an alternative value when an expression is NULL. IS NULL operator list all records that have no values

A column value is NULL if it does not exist. The IS NULL operator is used to display all the rows for columns that do not have a value.

For Example: If you want to find the names of students who do not participate in any games, the query would be as given below

SELECT first_name, last_name FROM student_details WHERE games IS NULL

There would be no output as we have every student participate in a game in the table student_details, else the names of the students who do not participate in any games would be displayed.

ISNULL() Function

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

Syntax

ISNULL (check_exp, change_value)

Example

ISNULL() function:

SELECT ISNULL(NULL, NULL, 'Hello')

IS NULL is more efficient because it uses Index seek which is generally faster than a scan as it only includes qualifying records, while scan includes every row. 

• Is NULL:a boolean expression used to filter a records 
  • example select  * from table1 where field1 is null
• isnull function :A function that transforms a null value to another value
  • example select isnull(field1,0) from table 1

IS NULL is used in WHERE calsue to evaluate a condition. On the other hand, ISNULL() is a function that examins a column or a value, and return a specific value is the evaluation returns NULL as a result

IS NULL operator is used to filter the record where a particular column has null value on which IS NULL operator is applied.

ISNULL() function is used to replace null value from another value

IS NULL:

IS NULL operator is used to filter the null values from the tables or view.

IS NULL operator returns set of data from table which matches the filter

ISNULL():

ISNULL() function is used to check a value whether it is NULL or not

ISNULL() returns numerical value 0 or 1 based on test 

ISNULL check for the null value in the given column and returns the results. 

For e.g:  SELECT * FROM DBO.ACCOUNTS WHERE ACCOUNTNAME IS NULL

Returns all the records which has accountname as NULL.

In sql server, the ISNULL ( ) function is used to replace null value with another value.

For e.g :  ISNULL (ACCOUNTNAME, “NoName” )  returns “Noname” in the result wherever accountname is NULL. 

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments. The Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.

Is null operator only taken the null values only

but is null function are used to set the default value

is null function that transforms a null value to another value and Is NULLa boolean expression used to filter a records.