SQL: IN OPERATOR
In this post we will discuss IN operator in SQL server 2016 with example.
- The arithmetic operator (=) compare a single value to another single value. In case a value needs to be compared to a list of values then the IN predicate is used.
- The IN predicate helps reduce the need to use multiple OR conditions. If query used multiple times OR operator then it becomes difficult to read. Therefore whenever we have to write a query that has multiple OR operators, we can use IN operator to make a query more readable.
- The IN operator is used in where clause to select rows whose values in a set of values.
- We can use this operator in select, update, delete statement.
- The IN operator can combine with NOT operator to select rows whose column values are not in a set of values
- This operator returns TRUE if the expression or value of the column matches one of the values in the list i.e., value1, value2, etc., otherwise, it returns FALSE.
- This operator is also used in SQL subquery.
column IN(val1, val2, val3,..);
Retrieve the pid, pname, price of product whose pid is 2 ,4.
Using OR operator:
select pid, pname, price from tblproduct where pid=2 or pid=4;
Using IN operator:
select pid, pname, price from tblproduct where pid IN(2,4);
Retrieve the pid, pname, price of product whose pname is pens ,laptop.
select pid, pname, price from tblproduct where pname in(‘Books’, ‘Calculators’);
Retrieve all the information of product whose price is not 200, 700.
select * from tblproduct where price NOT IN(200,700);
You can also by SQL 70-761 book write by me from Amazon.