We will see some logical operators from MySQL in action with real-time table examples.
For this article, we will use this table of employees to perform various queries.
1. Operator ALL
select all salary from employees where salary > 50000
- ALL operator returns TRUE if all of the subquery values meet the condition.
- Here we can see that it produced all the employees salary which is more than 50000 as the condition is satisfied and faithful.
2. Operator AND
SELECT * FROM employees WHERE first_name = "North" AND job_title = "VP Product Management";
- AND returns TRUE if both the provided conditions are satisfied.
- Here we can see that both the needs of first_name and job_title are present in the table, satisfying the requirement.
3. Operator ANY
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE salary > 100000);
- Unlike ALL, ANY operator returns TRUE if any conditions get satisfied.
- Here we can see that the subquery produced all the employees with a salary of more than 1lakh.
4. Operator BETWEEN
SELECT * FROM employees
WHERE salary BETWEEN 80000 AND 110000;
- BETWEEN operator returns True if the range provided and the between values are satisfied.
- Here it has produced the salary range between 80000 to 110000, thus satisfying the condition.
5. Operator EXISTS
SELECT * FROM employees
WHERE EXISTS (SELECT salary FROM employees WHERE salary > 100000);
- EXISTS operator returns TRUE if the subquery returns one or more records.
6. Operator IN
SELECT * FROM employees
WHERE job_title IN ('Staff Scientist','London');
- IN operator returns TRUE if the operand is equal to one list.
- Even though there is no job_title like London, One of the values gets satisfied, and that is how IN works.
7. Operator LIKE
SELECT * FROM employees
WHERE first_name LIKE 'M%';
- LIKE operator returns TRUE if the operand matches.
- The table was asked to get first_name, starting with' M.'
8. Operator NOT
SELECT * FROM employees
WHERE first_name NOT LIKE 'M%';
- NOT operator Displays record if the condition is NOT TRUE.
- Here except first_name starting with the letter 'M' has returned other records other than that.
9. Operator OR
SELECT * FROM employees WHERE first_name = "North" OR job_title = "VP Product Management";
- OR operator returns TRUE if any one of the conditions gets satisfied.
10. Operator SOME
SELECT * FROM employees
WHERE salary > SOME (SELECT salary FROM employees WHERE salary > 20000);
- SOME operator returns TRUE if any of the conditions get satisfied.