Difference between the “=” operator and “is” operator in SQL Server

Consider the following example queries which are having different behaviors while using “=”(known as assignment operator) and “is”(known as unary postfix operator) with a NULL placeholder (NULL is not a value ).

Let’s consider a table called devices having 3 columns id primary key, name, and location_id and add some data to it.

CREATE TABLE DEVICES (

ID INT PRIMARY KEY IDENTITY(1,1),

NAME VARCHAR(20),

LOCATION_ID varchar(20)

);

INSERT INTO DEVICES VALUES

(‘SMART_PLUG’,’A101'), (‘SMART_BULB’,NULL),

(‘SMART_CAMERA’,’B102'),(‘SMART_SWITCH’,NULL);

SELECT * FROM DEVICES

Table: devices

Problem statement:- Write an SQL query to fetch data from devices table having columns id, name, location_id and where location_id column having null placeholder.

First_approch:-

To solve this problem statement let’s consider “=” operator to fetch data from the table based on the condition

SELECT * FROM DEVICES WHERE LOCATION_ID = NULL;

By using the “=” operator

From the result what we received is not the desired result or this is not as per our requirement.Here “=” is an assignment operator which assigns NULL placeholder to location_id which results in :-

SELECT * FROM DEVICES WHERE NULL= NULL;

So, any operation on the NULL placeholder is not considered a valid operation.

Second approach:-

Now let’s consider the second approach by using “is” operator to fetch data from the table based on the condition

SELECT * FROM DEVICES WHERE LOCATION_ID IS NULL;

By using the “is” operator

Wow, happy to see the result, query returns the data as per our requirement.Here, we used “is” operator which is an unary postfix operator.

Conclusion:-

In order to work with a NULL placeholder in SQL always make use of two unary postfix operators i.e, IS NULL and IS NOT NULL.

Do clap, if find useful!🙂