Kiến thức

SQL NULL Check in Where clause-IS NULL and IS NOT NULL

SQL NULL Check in Where clause – IS NULL and IS NOT NULL

SQL

In

SQL Where clause

tutorial, we learned how to use comparison operators such as =, <, > etc in where clause for conditions. However when a column (field) of table has null values then such operators do not work on those columns, in such case we have to use IS NULL & IS NOT NULL operators for the null check. In this guide, we will learn how to deal with null values in SQL.

Bạn đang xem: SQL NULL Check in Where clause-IS NULL and IS NOT NULL

How to check for null values in SQL

We cannot use comparison operators such as =, <, > etc on null values because the result is undefined. To check for null values we can use IS NULL and IS NOT NULL operators. Lets see the syntax of these operators.

IS NULL Syntax

Null check:

SELECT column_name1, column_name2, column_name3, ... FROM table_name WHERE column_nameN IS NULL;

IS NOT NULL Syntax

Not Null check:

SELECT column_name1, column_name2, column_name3, ... FROM table_name WHERE column_nameN IS NOT NULL;

SQL IS NULL or empty check Example

Table: Employees

EmployeeName EmployeeAge EmployeePhoneNo EmployeeAddress ------------ ----------- --------------- --------------- Cindi 34 95XXXXXXX8 Noida Linda 35 Agra Shaun 33 75XXXXXXX4 Delhi Timmy 34 Noida Pappu 36 Noida 

The following SQL statement will fetch the EmployeeName & EmployeeAddress details of employees where the EmployeePhoneNo column has null value.

SELECT EmployeeName, EmployeeAddress FROM Employees WHERE EmployeePhoneNo IS NULL;

Result:

EmployeeName EmployeeAddress ------------ --------------- Linda Agra Timmy Noida Pappu Noida 

SQL IS NOT NULL in where clause Example

Lets take the same example that we have seen above. In this example, we will check for the not null values.
Table: Employees

EmployeeName EmployeeAge EmployeePhoneNo EmployeeAddress ------------ ----------- --------------- --------------- Cindi 34 95XXXXXXX8 Noida Linda 35 Agra Shaun 33 75XXXXXXX4 Delhi Timmy 34 Noida Pappu 36 Noida 

The following SQL statement will fetch the EmployeeName & EmployeeAddress details of employees where the value of EmployeePhoneNo column is not null.

SELECT EmployeeName, EmployeeAddress FROM Employees WHERE EmployeePhoneNo IS NOT NULL;

Result:

EmployeeName EmployeeAddress ------------ --------------- Cindi Noida Shaun Delhi 

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button