What is the difference between "IS NULL" and "ISNULL" in SQL Server??

Question

I have two tables to join with a column (say emp_id).. if emp_id in both the tables have null values, how will SQL Server and Oracle treat???

I read that informatica will neglect the NULL rows when joining..if I handle the null, by substituting -1, a cross-join will happen which i don't want..

What can I do here? (I cannot completely neglect the rows which has NULL)

Answer 1

IS NULL is used to check whether a value is null or not.

Ex: if @parametername is null then
        go here

ISNULL works like a method by using which you can use null values as your requirement.

Ex: select ISNULL(table.name,'No Name Specified'),
in this example if name is null then it will replace it with no name specified.

Answer 2

ISNULL is used where we want to replace any value based on that it's value is null or not

select ISNULL(NULL, 1) - Result 1
select ISNULL(2, 1) - Result 2

While IS NULL is used to select records from a table where particular column is null Ex-

Select * from products where
product_code is null