MYSQL not in

The NOT IN operator in MySQL is used to retrieve rows from a table that are not present in another table or a list of values.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

Example:

Consider two tables, "employees" and "sales", with the following data:

employees table:
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | John | 50000 |
| 2 | Sarah | 60000 |
| 3 | Jack | 45000 |
| 4 | Mary | 55000 |
+----+-------+--------+

sales table:
+----+-------+---------+
| id | name | amount |
+----+-------+---------+
| 1 | John | 2000 |
| 2 | Sarah | 3000 |
| 3 | Jack | 4000 |
+----+-------+---------+

To find the employees who have not made any sales, we can use the NOT IN operator as follows:

SELECT name, salary
FROM employees
WHERE name NOT IN (SELECT name FROM sales);

Output:
+------+--------+
| name | salary |
+------+--------+
| Mary | 55000 |
+------+--------+

Here, the subquery `(SELECT name FROM sales)` retrieves the names of employees who have made sales. The main query then selects the names and salaries of employees who are not present in the subquery result. In this case, only Mary has not made any sales.