How to use MySQL IN and BETWEEN Operator?
MySQL IN and BETWEEN we can use in Where condition to get the required data based on condition. We will see how we can use them in the query one by one. We will use the same ‘students’ table created earlier to fetch the data.
IN Operator:
Syntax:
SELECT * FROM schema.table_name
WHERE column_name IN (value1, value2, …);
When we want to add multiple OR conditions on one particular column we can use IN operator instead.
For example, we want to fetch the students whose marks are 492 or 450 or 422 or 432, or 465. By using the OR operator query will be generated like the one below.
SELECT * FROM students
where marks = 492
OR marks = 450
OR marks = 422
OR marks = 432
OR marks = 465;
The above query is quite big and part of the query in the where condition is repetitive. By using the IN operator we can rewrite the above query as below which is quite readable and smaller in length. We will get the results of the records mentioned in the IN condition.
SELECT * FROM students
WHERE marks IN (492,450,422,432,465);
If the record is not present for mentioned value then we will not get the records for that particular value and the remaining matching values will be returned as shown in the below example.
SELECT * FROM students
WHERE marks IN (492,450,421,430,460);
We can also use the NOT operator to get the data for students whose marks are not mentioned in the IN condition. We will get the records for all students except the students whose marks are equal to the marks mentioned in the condition
SELECT * FROM students
WHERE marks NOT IN (492,450,422,432,465);
BETWEEN Operator
The BETWEEN operator is used when we want the data for the given range. Both values are included
Syntax:
SELECT * FROM schema.table_name
WHERE column_name BETWEEN value1 AND value2;
In the below example, we will get the records for students whose marks are between 432 and 480 including 432 and 480 as well.
SELECT * FROM students
WHERE marks BETWEEN 400 AND 480;
We can also use the NOT operator in between conditions.
SELECT * FROM students
WHERE marks NOT BETWEEN 300 AND 480;
