Introduction

In this video, we will learn about the IN and NOT IN operators in PostgreSQL. These operators are used to check if a value matches any value in a list or a subquery. They provide a concise way to perform multiple comparisons in a WHERE clause.

IN Operator

The IN operator is used to check if a value matches any value in a list or a subquery. The basic syntax is:

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

or

SELECT column1, column2, ...
FROM table_name
WHERE column IN (subquery);

For example:

SELECT *
FROM products
WHERE category IN ('Electronics', 'Clothing', 'Books');

This will return all rows from the products table where the category is 'Electronics', 'Clothing', or 'Books'.

NOT IN Operator

The NOT IN operator is used to check if a value does not match any value in a list or a subquery. The basic syntax is:

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

or

SELECT column1, column2, ...
FROM table_name
WHERE column NOT IN (subquery);

For example:

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

This will return all rows from the employees table where the department is not 'IT', 'HR', or 'Finance'.

Examples

Let's look at some more examples of using IN and NOT IN:

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

This query first finds all customer IDs for customers located in the USA using a subquery. It then returns all orders where the customer_id matches any of the IDs returned by the subquery.

SELECT *
FROM products
WHERE product_id NOT IN (
  SELECT product_id
  FROM order_details
  WHERE quantity > 10
);

This query first finds all product IDs that have been ordered with a quantity greater than 10 using a subquery. It then returns all products where the product_id does not match any of the IDs returned by the subquery.

SELECT *
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31'
  AND department IN ('Sales', 'Marketing');

This query returns all employees hired between '2020-01-01' and '2021-12-31' who work in either the 'Sales' or 'Marketing' department.

Conclusion

In this video, we covered the IN and NOT IN operators in PostgreSQL. These operators provide a concise way to perform multiple comparisons in a WHERE clause, either by specifying a list of values or using a subquery. Practice using IN and NOT IN in your queries to become more comfortable with these operators.