SQL Server Null Operator

Last updated on Dec 17 2021
Amit Warghade

Table of Contents

SQL Server Null Operator

The SQL Server IS NULL operator is used to test for a NULL value.

Syntax:

  1. expression ISNULL

Parameter explanation

expression: It specifies a value whether it is NULL.

Note:

·         If the expression is NULL value, then the condition evaluates to TRUE.

·         If expression is not a NULL value, the condition evaluates to FALSE.

IS NULL Operator with SELECT Statement

Example:

  1. SELECT*
    FROM [tecklearn].[dbo].[Employees]
    WHEREsalary IS NULL;

Output:

image001 29
SELECT

IS NULL Operator with INSERT Statement

Example:

  1. INSERTINTO [tecklearn].[dbo].[Employees]
    (id, name, salary)
    SELECTid, name, Department
    FROM [tecklearn].[dbo].[Employee2]
    WHEREname IS NULL;

Output:

image002 19
INSERT

Note: This displays “0 rows affected” because there is no NULL value in name in the “Employees” table.

IS NULL Operator with UPDATE Statement

Example:

Update the salary of the employees in “Employees” table and set to 100000 where salary is NULL.

  1. UPDATEEmployees
    SET salary = '100000'
    WHEREsalary IS NULL;

Output:

image003 18
UPDATE

You can verify it by using SELECT query:

image004 15
verify

IS NULL Operator with DELETE Statement

Delete the employees from the “Employees” table where age is NULL.

Example:

  1. DELETEFROM [tecklearn].[dbo].[Employees]
    WHERE age IS NULL;

Output:

image005 16
DELETE

You can verify it by using SELECT query:

image006 13
verify using SELECT

You can see that there is no NULL value in age in the above table.

 SQL Server IS NOT NULL Condition (Operator)

SQL Server IS NOT NULL condition is used to test for a NOT NULL value.

Syntax:

  1. expression ISNOT NULL

Parameter explanation

expression: It specifies the value to test where it is NOT NULL value.

Note: If the expression is NOT a NULL value, the condition evaluates to TRUE. If it is a NULL value, the condition evaluates to FALSE.

IS NOT NULL operator with SELECT Statement

Example:

Retrieve all employees from the table “Employees” where salary is NOT NULL value.

  1. SELECT*
    FROM [tecklearn].[dbo].[Employees]
    WHEREsalary IS NOT NULL;

Output:

image007 11
IS NOT NULL

IS NOT NULL operator with INSERT Statement

Example:

  1. INSERTINTO [tecklearn].[dbo].[Employees]
    (id, name, salary)
    SELECTid, name, salary
    FROM [tecklearn].[dbo].[Employee2]
    WHEREname IS NOT NULL;

Output:

image008 14
FROM

IS NOT NULL operator with UPDATE Statement

Update the employees of “Employees” table and set the name “Active” where name is not null.

Example:

  1. UPDATE[tecklearn].[dbo].[Employees]
    SET name = 'Active'
    WHEREname IS NOT NULL;

Output:

image009 6
Statement

Verify the example:

image010 7
Verify the example

IS NOT NULL operator with DELETE Statement

Update the employees of “Employees” table where name is not null.

  1. DELETE
    FROM [tecklearn].[dbo].[Employees]
    WHEREname IS NOT NULL;

Output:

image011 6
DELETE Statement

Verify the example:

image012 3
example

So, this brings us to the end of blog. This Tecklearn ‘SQL Server Null Operator’ blog helps you with commonly asked questions if you are looking out for a job in MS-SQL Server. If you wish to learn MS-SQL Server and build a career in Database domain, then check out our interactive, MS-SQL Server Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/ms-sql-server-training-and-certification-course/

MS-SQL Server Training

About the Course

Microsoft SQL Server training course is an online classroom training meant for developers to master the descriptive language to work for relational databases. Important topics included in this SQL course are SQL introduction, relational databases, queries, subqueries, joins, and unions. After completing this course, you will also become proficient in the theory of optimization, transactions, indexing, installing SQL Server, database administration, clustering, implementing programmability objects, designing and implementing database objects, managing database concurrency, and optimizing SQL infrastructure and database objects.

Why Should you take MS-SQL Server Training?

  • The average salary for Sql server developer ranges from approximately $88,666 per year for Database Developer to $115,811 per year for Senior SQL Developer. – Indeed.com
  • Wells Fargo, UPS, US Bank, Perspecta, Silicon Valley Bank, Mindtree Wipro, Infosys & many other MNC’s worldwide use MS SQL Server for their Database deployments.
  • According to Gartner, MS SQL Server is the market leader in the Relational Database with 18.5% market share globally.

What you will Learn in this Course?

Introduction to SQL

  • Various types of databases
  • Introduction to SQL
  • Installation Steps of MS-SQL Server
  • Overview of Microsoft SQL Server Management Studio
  • SQL architecture, client/server relation
  • Database types

Introduction to relational databases, basic concepts of relational tables

  • Working with rows and columns
  • Various operators used like logical and relational
  • Constraints, Primary Key and Foreign Key
  • Insert, Update, Delete, Alter

Working with SQL: Join, Tables, and Variables

  • SQL Operators and Queries
  • SQL functions
  • Creation of Table
  • Retrieval of Data from tables
  • Combining rows from tables using joins
  • Operators such as intersect, except, union, temporary table creation, set operator rules, table variables etc

Deep Dive into SQL Functions

  • Data Retrieval Language
  • Functions
  • Operators
  • Clauses
  • Sub-Queries
  • Correlated Sub-Queries

SQL Views, Functions

  • Sub-queries
  • Views
  • Indexes

Managing Data with Transact-SQL

  • Transact-SQL queries
  • Implementing functions and aggregating data
  • Determining the results of DDL statements on supplied tables and data

Cursor Management and Triggers

  • Cursors
  • Triggers
  • Types of Triggers

Stored Procedures and User Defined functions

  • Understanding of Stored Procedures
  • Key benefits of Stored Procedures
  • Studying user-defined functions

Deep Dive into User-defined Functions

  • Detailed study of user-defined functions
  • Various types of UDFs like Scalar, Inline Table Value, multi-statement Table
  • What is Rank Function? Triggers, when to execute Triggers?

SQL Optimization and Performance

  • Concepts of Concurrency and Locking behaviour
  • Usage of memory-optimized tables to solve issues
  • Examining and Troubleshooting of query plans
  • Performance management of Database instances
  • SQL server Performance Monitoring

Got a question for us? Please mention it in the comments section and we will get back to you.

0 responses on "SQL Server Null Operator"

Leave a Message

Your email address will not be published. Required fields are marked *