IN Operator MySQL Essential Training

IN Operator MySQL Essential Training

MYSQL

IN Operator MySQL Essential Training




In this lesson we are gonna learn what is IN Operator MySQL Essential Training, which is another logical operator used in the MySQL. This operator allows you to determine if a specified value matches any one of a list or a subquery.
The Following is syntax of the IN Operator,

So far this is an structure of the IN operator. Lets see it in more details,
We can use this in a column or expression (expr) with the IN operator in the WHERE clause.
The values in the list must be separated by a comma (,).
The IN operator can also be used in the WHERE clause of other statements such as INSERT, UPDATE, DELETE, etc.
These are the functionalities of the In Operator MySQL Essential Training. The IN operator returns 1 if the value of the column_1 or the result of the expr expression is equal to any value in the list, otherwise, it returns 0.
When some times the values in the list all are constants. In similar conditions like this, we can do that in the following ways.
At the Beginning, MySQL evaluates the values based on the type of the column_1 or result of the expr expression.
After that, MySQL sorts the values.
Finally, MySQL searches for values using binary search algorithm. Therefore, a query that uses the IN operator with a list of constants will perform very fast.
We can notice that if expr or any value is NULL then the IN Operator also returns the NULL
We can combine the IN operator with the NOT operator to determine if a value does not match any value in a list or a subquery.




IN Operator MySQL Essential Training – Examples
If you want to understand more then, you’ve to implement it with the real time examples. Lets do with some real time examples.
Lets consider already exist table named emp_details. In that table we’ve to found the employees from the country India and UAE by using the IN Operator.

IN Operator MySQL Essential Training
In this IN Operator MySQL Essential Training We can achieve the above result by using the OR Operator too.

IN Operator MySQL Essential Training




In case the list has many values, you have to construct a very long statement with multiple OR operators. Hence, the IN operator allows you to shorten the query and make the query more readable.
After some time i don’t want to show the employees who are not located in the India and UAE. We use the NOT IN operator to do that particular functions. Consider the following example.

IN Operator MySQL Essential Training
The above example shows the employees who are not located in the country India and UAE. It shows the other employees then the mentioned countries.
IN Operator MySQL Essential Training with Sub Query
At some point we can use this IN operator with the Sub query. The following example will shows you how to do that.
For example, if you want to find countries whose total salary are greater than 20000, you use the IN operator as the following query:

IN Operator MySQL Essential Training
The whole query above can be broken down into 2 queries.
First, the sub query returns a list of order numbers that have total greater than 60000 using the GROUP BY and HAVING clauses on the emp_Details table.

IN Operator MySQL Essential Training
Second, the main query gets the data from the orders table and applies the IN operator in the WHERE clause.

In this tutorial, we have shown you how to use MySQL IN operator to determine if a value matches any value in a list or a sub query. We hope this lesson will gives you a clear idea about what is the IN Operator and why we use this.




Tagged

Leave a Reply