MySQL Logical Operators

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.