Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL IN

The `IN` clause in PostgreSQL is used to filter rows based on whether a column's value matches any value in a specified list. It is commonly utilized in `SELECT`, `UPDATE`, and `DELETE` statements to streamline queries involving multiple values.

Usage

The `IN` clause is used when you need to test if a value exists within a set of given values. It simplifies queries by allowing multiple values to be checked at once.

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

In this syntax, the `IN` clause is used to determine if `column_name` matches any of the values provided in the parentheses.

Examples

1. Basic IN Usage

SELECT *
FROM products
WHERE category_id IN (1, 2, 3);

This query selects all columns from the `products` table where `category_id` is either 1, 2, or 3.

2. Using IN with Strings

SELECT employee_id, name
FROM employees
WHERE department IN ('Sales', 'Marketing');

This example retrieves `employee_id` and `name` from the `employees` table for those working in the 'Sales' or 'Marketing' departments.

3. IN with Subquery

SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

Here, the `IN` clause is used with a subquery to find orders where the customer is from the USA. Note that the subquery must return a single column to be used with `IN`.

Tips and Best Practices

  • Use IN for clarity. Opt for `IN` when checking against multiple values for better readability over multiple `OR` conditions.
  • Consider performance. For large lists, consider using a table join or a temporary table to optimize query performance. Joining tables on indexed columns can be more efficient than using `IN` with a large list of values.
  • Leverage subqueries. Utilize subqueries within `IN` to handle complex conditions and dynamically generated lists.
  • Avoid null pitfalls. Remember that if any value in the list is `NULL`, the result will be `false` unless handled explicitly. Consider using `IS NOT NULL` explicitly to handle potential `NULL` values in the list, as `IN` evaluates to `false` if any value is `NULL`.