How to use IN operator in mysql?

MySQL IN operator is used to determine if a value matches any value in a list of values.
IN operator is used to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
The IN operator returns 1 (true) if the value matches any value in the list (value1, value2, value3,…). Otherwise, it returns 0.
The IN Operator is used to search for specified values that found any value in the set of multiple values. The “IN” operator evaluates multiple values on a single data column. It displays the data row if any one of the given values is matched with the column. the IN operator generally used with WHERE clause to compare column values with a set of multiple values.

expression IN (value1, value2, .... value_n);
OR
expression IN (subquery);

Example

SELECT * FROM itechxpert WHERE last_name IN ('alam', 'habib', 'mahira','sana');

The above query is equivalent to the following SELECT statement:

SELECT * FROM contacts WHERE last_name = 'alam' OR last_name = 'habib' OR last_name = 'mahira' OR last_name = 'sana';

With Numeric

SELECT * FROM iech_suppliers WHERE supplier_id IN (201, 202, 203, 300);

IN With subquery

SELECT * FROM ittutorial WHERE courseid IN (select course_id from course_table);
SELECT * FROM ittutorial WHERE courseid IN (select course_id from course_table where course_name='PHP');

The above IN example is equivalent to the following SELECT statement:

SELECT * FROM itech_suppliers WHERE supplier_id = 200 OR supplier_id = 201 OR supplier_id = 203 OR supplier_id = 300;

Using NOT operator

SELECT * FROM contacts WHERE last_name NOT IN ('mahtab', 'sana', 'mahira');
SELECT * FROM Users WHERE id NOT IN (select fki_id from user_contact);

when IN operator return NULL

IN operator returns NULL in two cases:

The value on the left side of the operator is NULL.

The value doesn’t equal any value in the list and one of values in the list is NULL.

SELECT NULL IN (1,2,3);
SELECT 0 IN (1 , 2, 3, NULL);

Example :-

SELECT name,address,phone, state FROM itechxpert WHERE state IN ('bihar' , 'delhi');

Previous Next

Review & Rating

0.0 / 5

0 Review

5

(0)

4

(0)

3

(0)

2

(0)

1

(0)