MySQL Where with Operators

We will continue looking at the wherewith other operators like the IN, Not IN, Between, Exists, and Not Exists. 

MySQL Where clause with the IN operator

To check records based on several values, we can use the "IN." Below are simple examples of the same. We can also use these operators for subqueries, as is shown in the last query.

SELECT COUNT(EMP_NO), TITLE FROM TITLES 
WHERE TITLE IN ('SENIOR ENGINEER', 'ENGINEER', 'ASSISTANT ENGINEER')
GROUP BY TITLE;

MySQL Where clause with the NOT IN operator

We can use the "Not IN" operator to get all the records that do not match the predicate. 

SELECT COUNT(EMP_NO), TITLE FROM TITLES 
WHERE TITLE NOT IN ('SENIOR ENGINEER', 'ENGINEER', 'ASSISTANT ENGINEER')
GROUP BY TITLE;

 
MySQL Where clause with AND operator

The operators AND is used to add multiple filters or conditions.AND is used when joining multiple tables. An AND condition returns True if both the predicates(parts of the condition) are True.  

SELECT DISTINCT EMP_NO FROM SALARIES 
WHERE SALARY < 45000 AND SALARY >= 39000
LIMIT 20;

MySQL Where clause  with OR operator

The other operator is OR. The OR operator returns True if either one of the predicates returns True. 

SELECT COUNT(*),DEPT_NO FROM DEPT_EMP 
WHERE DEPT_NO = 'D002' OR DEPT_NO = 'D008' 
GROUP BY DEPT_NO;

 
MySQL Where clause with Like operator

We use the LIKE operator to search for a pattern to filter records.

SELECT * FROM EMPLOYEES 
WHERE HIRE_DATE LIKE' 1999%'
AND GENDER = 'F'
LIMIT 20;

 
 
MySQL Where clause with EXISTS example

Exists is used with a subquery. If the subquery returns any result(s), then the tables' records in the main query are returned.

SELECT * FROM EMPLOYEES WHERE 
EXISTS 
(SELECT 1 
FROM TITLES2);

Here the Titles2 table has no records, and hence the subquery does not return any records. Due to this, the Employees table does not produce any records.

SELECT * FROM EMPLOYEES WHERE 
EXISTS 
(SELECT 1 
FROM TITLES)
LIMIT 20;

Here the Titles table has records, and hence employees table also returns records.
  

MySQL Where statement with the Not Exists Clause

Not Exists works precisely opposite to the Exists Clause, as is seen in the below examples,

SELECT * FROM EMPLOYEES WHERE 
NOT EXISTS 
(SELECT 1 
FROM TITLES2)
LIMIT 20;

Here, since titles2 does not return any records and the "Not Exists" is used, the Employees table's records are returned.

SELECT * FROM EMPLOYEES WHERE 
NOT EXISTS 
(SELECT 1 
FROM TITLES);

Conversely, the above query will not return any records.