If you are interested to learn about the MySQL Features
MySQL comes with special characters or words to perform certain operations. MySQL operators work as filters in queries to do operations on our desired data. Here, we will learn some popular SQL operators.
- MySQL IN Operator used to retrieve the result set by checking whether a value from the set of literal values or provided by the subquery is matched or not.
- Basically, this operator helps to determine the specific value if available in the result of subquery using the WHERE clause.
- It fetches the set of columns from the table if a particular value is present in the list of values provided to match a conditional expression.
- Thus, the MySQL IN operator like other MySQL operators allows comparing a value matching from the set of column values returned by a subquery statement.
- If we want to get the rows as result from the Database table where a particular value should be present in a list of values available through conditional query then, we use MySQL IN operator.
- We extract the rows which have matching values in the list of values in IN operator function from a particular value of column given using WHERE keyword in MySQL.
What are the operators?
In mathematics and sometimes in computer programming, an operator is a character that represents an action, as for example x is an arithmetic operator that represents multiplication. In computer programs, one of the most familiar sets of operators, the Boolean operators, is used to work with true/false values.
What are Operators in MySQL?
Operators are used to specifying a condition in a statement in MySQL. Below are the different types of operators used in MySQL.
How IN Operator Works in MySQL?
As per the above syntax, MySQL IN operator provides us an equal value in the arguments. To use the IN Keyword it is combined with WHERE clause together. When this happens, the query executed only affects the records whose values are matched with the list of values available in the IN Keyword. IN keyword in MySQL helps to minimize the use of the OR clause.
Suppose we have all the constant values in the list, then in MySQL following operational steps are carried:
- Firstly, it examines if the values are of the same data type as of the ColumnA or not. Also, evaluation is done for expression or subquery results.
- Next, the values are sorted in a proper sequence.
- Then, the value is searched based on the Binary Search Algorithm, which allows the SQL statement query using IN operator with a list of constant values to execute very speedily.
- Otherwise, based on the rules in MySQL type conversion can take place to process the query.
Types of Operators
1. Arithmetic Operators
In MySQL, arithmetic operators are used to perform the arithmetic operations as described below.
| Arithmetic Operators in MySQL | ||
| Operator | Description | Example |
| + | Addition of two operands | a + b |
| – | Subtraction of right operand from the left operand | a – b |
| * | Multiplication of two operands | a * b |
| / | Division of left operand by the right operand | a / b |
| % | Modulus – the remainder of the division of left operand by the right | a % b |
The following are a few examples of operations, using Arithmetic Operators. Let us assume certain values for the below variables as
a = 10 , b = 5
- a + b will give the result as 15.
- a – b will give the result as 5.
- a * b will give the result as 50.
- a / b will give the result as 2.
- a % b will give the result as 0.
2. Comparison Operators
The comparison operators in MySQL are used to compare values between operands and return true or false according
| Comparison Operators in MySQL | ||
| Operator | Description | Example |
| > | If the value of left operand is greater than that of the value of the right operand, the condition becomes true; if not then false. | a > b |
| < | If the value of left operand is less than that of a value of the right operand, the condition becomes true; if not then false. | a < b |
| = | If both the operands have equal value, the condition becomes true; if not then false. | a == b |
| != | If both the operands do not have equal value, the condition becomes true; if not then false. | a != y |
| >= | If the value of left operand is greater than or equal to the right operand, the condition becomes true; if not then false. | a >= b |
| <= | If the value of left operand is less than or equal to the right operand, the condition becomes true; if not then false. | a <= b |
| !< | If the value of left operand is not less than the value of right operand, the condition becomes true; if not then false. | a !< b |
| !> | If the value of left operand is not greater than the value of right operand, the condition becomes true; if not then false. | a !> b |
| <> | If the values of two operands are not equal, the condition becomes true; if not then false. | a <> b |
Let us take an example of EMPLOYEE table as shown below to understand how to use the comparison operators as stated above while performing MySQL queries.
| ID | NAME | AGE | SALARY |
| 4 | Sushma | 32 | 35000.00 |
| 6 | Ritu | 23 | 23000.00 |
| 8 | Amit | 27 | 30000.00 |
| 11 | Harish | 35 | 35000.00 |
| 18 | Pooja | 28 | 29500.00 |
Let us use the different comparison operators to query the EMPLOYEE table as shown below.
SELECT * FROM EMPLOYEE WHERE SALARY > 25000;
| ID | NAME | AGE | SALARY |
| 4 | Sushma | 32 | 35000.00 |
| 8 | Amit | 27 | 30000.00 |
| 11 | Harish | 35 | 35000.00 |
| 18 | Pooja | 28 | 29500.00 |
SELECT * FROM EMPLOYEE WHERE SALARY = 35000;
| ID | NAME | AGE | SALARY |
| 4 | Sushma | 32 | 35000.00 |
| 11 | Harish | 35 | 35000.00 |
SELECT * FROM EMPLOYEE WHERE SALARY < 35000;
| ID | NAME | AGE | SALARY |
| 6 | Ritu | 23 | 23000.00 |
| 8 | Amit | 27 | 30000.00 |
| 18 | Pooja | 28 | 29500.00 |
SELECT * FROM EMPLOYEE WHERE SALARY != 30000;
| ID | NAME | AGE | SALARY |
| 4 | Sushma | 32 | 35000.00 |
| 6 | Ritu | 23 | 23000.00 |
| 11 | Harish | 35 | 35000.00 |
| 18 | Pooja | 28 | 29500.00 |
SELECT * FROM EMPLOYEE WHERE SALARY <> 35000;
| ID | NAME | AGE | SALARY |
| 6 | Ritu | 23 | 23000.00 |
| 8 | Amit | 27 | 30000.00 |
| 18 | Pooja | 28 | 29500.00 |
3. Logical Operators
The logical operators used in MySQL are shown below.
| Logical Operators in MySQL | |
| Operator | Description |
| BETWEEN | It is used to search within a set of values, by the minimum value and maximum value provided. |
| EXISTS | It is used to search for the presence of a row in a table which satisfies a certain condition specified in the query. |
| OR | It is used to combine multiple conditions in a statement by using the WHERE clause. |
| AND | It allows the existence of multiple conditions in an SQL statement’s WHERE clause. |
| NOT | It reverses the meaning of the logical operator with which it is used. (Examples: NOT EXISTS, NOT BETWEEN, NOT IN, etc.) |
| IN | It is used to compare a value in a list of literal values. |
| ALL | It compares a value to all values in another set of values. |
| ANY | It compares a value to any value in the list according to the condition specified. |
| LIKE | It uses wildcard operators to compare a value to similar values. |
| IS NULL | It compares a value with a NULL value. |
| UNIQUE | It searches for every row of a specified table for uniqueness (no duplicates). |
Let us take the example of the same EMPLOYEE table as shown above earlier to understand the usage of logical operators as shown in the below queries.
SELECT * FROM EMPLOYEE WHERE AGE <= 25 AND SALARY >= 5000;
| ID | NAME | AGE | SALARY |
| 6 | Ritu | 23 | 23000.00 |
SELECT * FROM EMPLOYEE WHERE AGE >= 25 OR SALARY >= 25000;
| ID | NAME | AGE | SALARY |
| 4 | Sushma | 32 | 35000.00 |
| 8 | Amit | 27 | 30000.00 |
| 11 | Harish | 35 | 35000.00 |
| 18 | Pooja | 28 | 29500.00 |
SELECT * FROM EMPLOYEE WHERE AGE IS NOT NULL;
| ID | NAME | AGE | SALARY |
| 4 | Sushma | 32 | 35000.00 |
| 6 | Ritu | 23 | 23000.00 |
| 8 | Amit | 27 | 30000.00 |
| 11 | Harish | 35 | 35000.00 |
| 18 | Pooja | 28 | 29500.00 |
SELECT * FROM EMPLOYEE WHERE NAME LIKE 'Am%';
| ID | NAME | AGE | SALARY |
| 8 | Amit | 27 | 30000.00 |
SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 25 AND 30;
| ID | NAME | AGE | SALARY |
| 8 | Amit | 27 | 30000.00 |
| 18 | Pooja | 28 | 29500.00 |
SELECT NAME FROM EMPLOYEE WHERE EXISTS (SELECT NAME FROM EMPLOYEE WHERE SALARY > 25000);
| NAME |
| Sushma |
| Amit |
| Harish |
| Pooja |
Examples to Implement IN operator in MySQL
Let us now discuss the IN operator using some examples and learn the code to write the SQL query for it.
Example #1 – within, NOT IN and OR
We are using tables named ‘Customers’ and ‘Employees’. Below is the SQL statement using IN operator in the Customers table.
Customers Table:
Employees Table:
SELECT * FROM Customers WHERE City IN (‘Delhi’, ‘Mumbai’);
The above query with IN operator will fetch all the records from the table where the customers are from the cities provided in the IN keyword: Delhi and Mumbai.
Output:
If we use OR operator in the query above then, the result will be the same as above:
Output:
But if we have multiple values in the list, then we need to separate each value with OR operators and the statement will be constructed very long. Therefore, to avoid such kind of issue and making the SQL syntax more proper and readable, the IN operator is introduced in MySQL. So, the IN keywords has helped to shorten the statement by listing values together separated by commas.
In reverse, if we want to get the result set which does not match the values in the list then, we use the following query with NOT IN operator in MySQL:
Query:
SELECT * FROM Customers WHERE City NOT IN (‘Delhi’, ‘Mumbai’);
Output:
You can see from the result above that NOT IN operator has fetched the rows of customers form the table that does not belong or related to the cities provided in the list: Delhi and Mumbai.
Example #2 – With Subquery
Generally, the IN operator is used with an inner query or subquery. It is done so because instead of passing a list of literal values with IN keyword, we can use a subquery which fetches the list of values from one or multiple tables and provides them as input to IN operator to match the value in the outer query and produce the result from the SQL statement.
In the following query, we have taken both tables Customers (CustNum, Name, City, Phone) and Employees (EmpNum, Profile, CustNum, Salary) to filter the result set from both the tables in MySQL:
Query:
SELECT CustNum,Name, City FROM Customers WHERE CustNum IN (SELECT CustNum FROM Employees where EmpNum > 202 ) ORDER BY CustNum;
Output:
Here, the IN Operator has provided the result rows from Customers using subquery where the output of subquery from Employees is the values of the CustNum column which includes the employees having their salary greater than 5000.
