SQL IN Operator

The SQL IN operator is used to filter results based on a list of values.

It allows you to specify multiple values in a WHERE clause, and returns rows that match any of the values in the list.

Syntax

The syntax for the IN operator is as follows:

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

In this example, column_name is the name of the column you want to filter on, and value1, value2, etc. are the values you want to match.

Examples

Let’s look at some examples of how to use the IN operator.

Example 1:

Retrieve all rows from the “customers” table where the “city” column is either “New York” or “Los Angeles”.

SELECT *
FROM customers
WHERE city IN ('New York', 'Los Angeles');

Example 2:

Retrieve all rows from the “orders” table where the “product_id” column is in a list of specific values.

SELECT *
FROM orders
WHERE product_id IN (4, 8, 15, 16, 23);

Example 3:

Retrieve all rows from the “employees” table where the “salary” column is between 50000 and 75000.

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 75000;

Subqueries

You can also use a subquery in place of the list of values for the IN operator.

A subquery is a query that is nested inside another query.

Example 4:

Retrieve all rows from the “orders” table where the “customer_id” column is in a list of customer IDs returned by a subquery.

SELECT *
FROM orders
WHERE customer_id IN (SELECT id
                      FROM customers
                      WHERE country = 'USA');

In this example, the subquery returns a list of customer IDs for customers that are from the USA, and the main query retrieves all rows from the “orders” table where the “customer_id” column matches any of the IDs in the list.

NOT IN Operator

The NOT IN operator is used to filter results that do not match the specified list of values.

The syntax is similar to the IN operator, with the addition of the NOT keyword.

Example 5:

Retrieve all rows from the “employees” table where the “department” column is not “IT” or “HR”.

SELECT *
FROM employees
WHERE department NOT IN ('IT', 'HR');

In this example, the query returns all rows from the “employees” table where the “department” column is not “IT” or “HR”.

Note: In the examples above, It’s assumed that the table structure and data exist.

In conclusion, the IN operator is a useful tool for filtering results based on a list of values, and the NOT IN operator allows you to exclude certain values from your query results. The use of subqueries with the IN operator also allows for more complex filtering.

Related Posts: