how to replace null values in sql with customize value

ISNULL:-Replaces NULL value with the specified replacement value.

COALESCE:-Returns the first nonnull expression among its arguments.

For e.g.

CASE Statement:-Evaluates a list of conditions and returns one of multiple possible result expressions.

For e.g.

Joins in Sql

Joins in Sql can be classified as below:-

1) Inner Join:-Inner join return all the matching record between two table.

For e.g.

2.1) Left Outer Join:-Left outer join returns all the matching record between 2 table plus non matching record from left table.

For e.g.

2.2) Right Outer Join:-Right outer join returns all the matching record between 2 table plus non matching record from right table.

For e.g.

2.3) Full Join:-Full outer join returns all the matching record from both the table plus non matching record from both the tables.

For e.g.

3) Cross Join:-Cross join produces the Cartesian product of the 2 tables that are involved in the join operation.

For e.g.

Group By Clause in Sql Server

Group By:- Query involving Group by Clause must Satisfied one of the two below Condition:-
                    1) Column must contain aggregate function
                    2) Column has to be part of  group by clause

For e.g.

Where Clause Vs Having Clause Filter:-

1) Where Clause:-

For e.g.

--Query with where clause filter

select COUNT(LoginID) as [Login],[JobTitle],[MaritalStatus] 

from [AdventureWorks2008].[HumanResources].[Employee]

where [MaritalStatus] = 'M'

group by [JobTitle],[MaritalStatus]

Here above query retrieve only those rows whose MartalStatus is 'M' and then apply Group by filter.

2) Having Clause:-

For e.g.

--Query with having clause filter

select COUNT(LoginID) as [Login],[JobTitle],[MaritalStatus] 

from [AdventureWorks2008].[HumanResources].[Employee]

group by [JobTitle],[MaritalStatus]

having [MaritalStatus] = 'M'

Here above query retrieve all the rows and group them by [JobTitle] & [MaritalStatus] and then yield only those rows whose [MaritalStatus] is 'M'.

For e.g.:-

Where Vs Having Clause:-

1) Where clause filter the rows before grouping i.e. aggregation is performed,where as having clause does after the grouping is performed.

2) Where clause can be used with insert,update and delete statement,where as having clause is only used with select statement.

Note:-Aggregate function can't be used in the where clause,where as in the having clause we can used aggregate function.

SCOPE_Identity() Vs @@Identity Vs Ident_Current()

Scope_Identity:-Return the last generated identity column value within the same scope and same session.

For e.g. Customer Table with column (Id int identity(1,1),Name nvarchar(50))

@@Identity:Return the last generated identity column value across any scope and same session.

IDENT_CURRENT('TableName'):-Return the last generated identity column value across any scope and any session.