Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

SQL Server NULL Functions

NULL Functions in SQL Server are used to handle missing, unknown or NULL values in database columns.

What is NULL?

In SQL Server, NULL represents a missing, unknown or unavailable value.

NULL is not the same as zero, an empty string or a blank space. It means that no value is available.

Example:

Suppose an employee has not provided a phone number. The PhoneNumber column may contain NULL because the value is not available.

Common NULL Functions

Function Purpose Example
ISNULL() Replaces a NULL value with a specified value. ISNULL(Phone, 'Not Available')
COALESCE() Returns the first non-NULL value from a list of expressions. COALESCE(Phone, Email, 'N/A')
NULLIF() Returns NULL when two expressions are equal. NULLIF(10, 10)

1. ISNULL() Function

The ISNULL() function is used to replace a NULL value with a specified replacement value.

Syntax
ISNULL(expression, replacement_value)
Simple Example
SELECT ISNULL(NULL, 'Not Available');
Output:
Not Available
Example with Column
SELECT
    EmployeeName,
    ISNULL(PhoneNumber, 'Not Available') AS PhoneNumber
FROM Employees;

If PhoneNumber contains NULL, SQL Server displays Not Available.

2. COALESCE() Function

The COALESCE() function returns the first non-NULL value from a list of expressions.

Syntax
COALESCE(expression1, expression2, expression3, ...)
Example
SELECT
    COALESCE(NULL, NULL, 'Hello');
Output:
Hello

SQL Server checks the expressions from left to right and returns the first non-NULL value.

Practical Example
SELECT
    EmployeeName,
    COALESCE(PhoneNumber, Email, 'No Contact Available')
        AS ContactInformation
FROM Employees;

If PhoneNumber is NULL, SQL Server checks Email. If Email is also NULL, it returns No Contact Available.

3. NULLIF() Function

The NULLIF() function returns NULL when two expressions are equal.

If the two expressions are different, the first expression is returned.

Syntax
NULLIF(expression1, expression2)
Example 1: Values are Equal
SELECT NULLIF(10, 10);
Output:
NULL
Example 2: Values are Different
SELECT NULLIF(10, 20);
Output:
10

How to Check for NULL Values?

We should use IS NULL and IS NOT NULL to check NULL values.

IS NULL
SELECT *
FROM Employees
WHERE PhoneNumber IS NULL;

This query returns employees whose PhoneNumber is NULL.

IS NOT NULL
SELECT *
FROM Employees
WHERE PhoneNumber IS NOT NULL;

This query returns employees whose PhoneNumber contains a value.

NULL vs Zero vs Empty String

Value Meaning
NULL Value is missing or unknown.
0 Numeric value zero.
'' Empty string.
' ' String containing a space.

Practical Example with Employees

Create Table
CREATE TABLE Employees
(
    EmployeeId INT,
    EmployeeName VARCHAR(100),
    PhoneNumber VARCHAR(20),
    Email VARCHAR(100)
);
Insert Data
INSERT INTO Employees
VALUES
(1, 'Rahul', '9876543210', 'rahul@gmail.com'),
(2, 'Priya', NULL, 'priya@gmail.com'),
(3, 'Amit', NULL, NULL);
Display Replacement for NULL
SELECT
    EmployeeName,
    ISNULL(PhoneNumber, 'Phone Not Available') AS PhoneNumber
FROM Employees;
Use COALESCE()
SELECT
    EmployeeName,
    COALESCE(
        PhoneNumber,
        Email,
        'No Contact Available'
    ) AS Contact
FROM Employees;
Find Employees Without Phone Number
SELECT *
FROM Employees
WHERE PhoneNumber IS NULL;

ISNULL() vs COALESCE()

ISNULL() COALESCE()
Accepts two arguments. Can accept multiple expressions.
SQL Server-specific function. Standard SQL expression.
ISNULL(Phone, 'N/A') COALESCE(Phone, Email, 'N/A')

When Do We Use NULL Functions?

  • To handle missing database values.
  • To replace NULL values with meaningful values.
  • To display user-friendly reports.
  • To select the first available value from multiple columns.
  • To check whether a column contains NULL.
  • To safely handle missing data during calculations.

Important Points

  • NULL means the value is missing or unknown.
  • NULL is different from zero.
  • NULL is also different from an empty string.
  • Use IS NULL to find NULL values.
  • Use IS NOT NULL to find values that are not NULL.
  • ISNULL() can replace a NULL value.
  • COALESCE() returns the first non-NULL value.
  • NULLIF() returns NULL when two expressions are equal.

Summary

NULL handling is an important part of SQL Server development. In this topic, we learned how NULL values work and how to use ISNULL(), COALESCE(), NULLIF(), IS NULL and IS NOT NULL. These techniques are useful when working with incomplete or missing database information.