Difference between = and IN operator in SQL

Prerequisite – SQL Commands
In this article we are going to see the difference between = and IN operator in SQL.

1. = Operator :
The = 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

2. 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

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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.