PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
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:
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');