Difference between (Equal) = and IN operator in SQL

Difference between != and is not operator in Python

Equal (=) and IN operators are both comparison operators in SQL, the only difference between = and IN operator is that ‘=’ operator compares two values, but IN operator compares a value against set of values.

Here, we will learn the difference between the SQL ‘=’ and IN operator by understanding each of them.

Difference between = and IN Operator

= Operator	IN Operator
It allows comparison of attributes with single value.	It allows comparison of attributes with multiple value. For single value comparison behaves same as = .
For multiple comparison we have to use appropriate Operator in addition.(i.e JOIN, OR, AND, etc.)	No additional use of Operator required.
Useful in scenarios when sub-query returns single value as result.	Useful in scenarios when sub-query returns multiple values as result.
It will generate an error if you have more than one result on the subquery.	It will not generate an error if you have multiple results on the subquery.
It is faster as compared to IN Operator.	The IN clause is slower compared to = as it compares with multiple values until the condition satisfies.

(Equal) = Operator

The (Equal) = operator is used with Where Clause in SQL. For Example consider the student table given below,

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	xxxxxxxxxx	18
2	RAMESH	GURGAON	xxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxx	20
4	SURESH	Delhi	xxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxx	20
2	RAMESH	GURGAON	xxxxxxxxxx	18

Query :

To fetch record of students with address as Delhi or ROHTAK. The SQL query using = operator would be,

SELECT * 
FROM Student 
WHERE ADDRESS='Delhi' OR ADDRESS='ROHTAK';

Output :

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	xxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxx	20
4	SURESH	Delhi	xxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxx	20

IN Operator

The IN operator is used with WHERE Clause to test if the expression matches any value in the list of values. The advantage of using IN operator is that it avoids the use of multiple OR Operator.

Query :

To fetch record of students with address as Delhi or ROHTAK. The SQL query using IN operator would be,

SELECT * 
FROM Student 
WHERE ADDRESS IN ('Delhi', 'ROHTAK');

Output :

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	xxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxx	20
4	SURESH	Delhi	xxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxx	20

Tags:

#DBMS-SQL #DBMS #Difference Between #GATE CS #SQL

Difference between != and is not operator in Python

Difference between (Equal) = and IN operator in SQL

Difference between = and IN Operator

(Equal) = Operator

IN Operator

Contact Us