SQL IN and NOT IN

SQL IN and NOT IN operators are used to compare values in a list or subquery. They are used to filter data based on specific conditions, making it easier to retrieve and manipulate data in a relational database.

IN operator

The IN operator is used to determine if a specified value is present in a list of values or a subquery. The syntax for using the IN operator is as follows:

1SELECT column1, column2, ...
2FROM table_name
3WHERE column_name IN (value1, value2, ...);

For example, if you have a table named employees and you want to retrieve all employees who work in either the Marketing or Sales departments, you could use the following query:

1SELECT *
2FROM employees
3WHERE department IN ('Marketing', 'Sales');

NOT IN operator

The NOT IN operator is used to determine if a specified value is not present in a list of values or a subquery. The syntax for using the NOT IN operator is similar to the IN operator:

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

For example, if you have a table named employees and you want to retrieve all employees who do not work in the HR department, you could use the following query:

1SELECT *
2FROM employees
3WHERE department NOT IN ('HR');