You are here

SQL: IN OPERATOR

in operator

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.

Syntax:
SELECT
col1, col2
FROM
table
WHERE
column IN(val1, val2, val3,..);

Example:

Table: tblproduct

Pid

Pname price manufadate
1 Books 100.00

2017-01-12

2

Pens 200.00 2013-01-01
3 Pencils 50.00

2017-09-28

4

Calculators 700.00 2016-04-24
5 Laptop 23000.00

2017-08-14

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);
output:

Pid

Pname price
2 Pens

200.00

4

Calculators

700.00

Retrieve the pid, pname, price of product whose pname is pens ,laptop.
select pid, pname, price from tblproduct where pname in(‘Books’, ‘Calculators’);
output:

Pid

Pname price
1 Pens

200.00

4

Calculators

23000.00

Retrieve all the information of product whose price is not 200, 700.
select * from tblproduct where price NOT IN(200,700);
output:

Pid

pname price
1 Pens

200.00

4

Calculators

23000.00

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

 

You can also by SQL 70-761 book write by me from Amazon.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top