MS SQL Server IS NULL and NOT NULL Operator
MS SQL Server IS NULL Operator
- IS NULL is used to evaluate a condition for NULL Value.
- If the expression is NULL then it returns to TRUE.
- If the expression is not a NULL then it returns to FALSE.
- Syntax:
expression IS NULL
IS NULL Operator with SELECT Statement
Example:
SELECT * FROM [University].[dbo].[student] WHERE Branch IS NULL;
| Id | Name | Branch | CGPA |
| EN19CS301258 | Kavi | NULL | NULL |
IS NULL Operator with UPDATE Statement
Example:
UPDATE student SET Branch='CSE' WHERE Branch IS NULL;
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.34 |
| EN19CS301246 | Savi | CSE | 8.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
| EN19CS301258 | Kavi | CSE | NULL |
IS NULL Operator with DELETE Statement
Example:
DELETE FROM [University].[dbo].[student] WHERE Branch IS NULL;
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.34 |
| EN19CS301246 | Savi | CSE | 8.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
IS NULL Operator with INSERT Statement
Example:
INSERT INTO [University].[dbo].[student](Id, Name, Branch) SELECT id, name, Branch
FROM [University].[dbo].[Staff] WHERE name IS NULL;
Output:
"0 rows affected"
MS SQL Server IS NOT NULL Operator
- IS NOT NULL is used to evaluate a condition for NOT NULL Value.
- If the expression is NOT NULL then it returns to TRUE.
- If the expression is a NULL then it returns to FALSE.
- Syntax:
expression IS NOT NULL
IS NOT NULL Operator with SELECT Statement
Example:
SELECT * FROM [University].[dbo].[student] WHERE Branch IS NOT NULL;
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.34 |
| EN19CS301246 | Savi | CSE | 8.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
IS NOT NULL Operator with UPDATE Statement
Example:
UPDATE student SET Branch='CSE' WHERE Branch IS NOT NULL;
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.34 |
| EN19CS301246 | Savi | CSE | 8.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
| EN19CS301258 | Kavi | CSE | NULL |
IS NOT NULL Operator with DELETE Statement
Example:
DELETE FROM [University].[dbo].[student] WHERE Branch IS NOT NULL;
| Id | Name | Branch | CGPA |
| EN19CS301254 | Ravi | CSE | 9.34 |
| EN19CS301246 | Savi | CSE | 8.23 |
| EN19CS301248 | Aayush | CSE | 7.25 |
IS NOT NULL Operator with INSERT Statement
Example:
INSERT INTO [University].[dbo].[student](Id, Name, Branch) SELECT id, name, Branch
FROM [University].[dbo].[Staff] WHERE name IS NOT NULL;
Output:
"0 rows affected"
