PostgreSQL IN

PostgreSQL IN Operator

The IN operator is a highly efficient shorthand for multiple OR conditions. It allows you to explicitly specify multiple valid values in a WHERE clause.

Writing endless OR statements makes your SQL queries bulky and hard to read. The IN operator condenses the logic into a single, elegant array of options.

The Basic Syntax

The IN operator is always followed by a strict pair of parentheses. Inside these parentheses, you supply a comma-separated list of targeted values.

If the column matches absolutely any value inside the list, the row is returned. It behaves precisely like an inclusive multiple-choice checklist.

-- Fetch customers located in either Germany, France, or the UK
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

The NOT IN Operator

You can aggressively negate the logic by utilizing the NOT IN operator. This strictly filters out rows that match anything inside the provided list.

It is incredibly useful for blacklisting specific categories or user IDs globally. The database will return everything except the items explicitly mentioned.

-- Fetch customers who do NOT live in Germany, France, or the UK
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

IN with Subqueries

The true power of the IN operator shines when utilized with nested subqueries. Instead of hardcoding a static list, you can dynamically fetch the list.

You can query another table to generate the array of valid values automatically. This allows for dynamic, relationship-driven data filtering without complex joins.

-- Find all customers who have placed an order recently
SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

Performance Considerations

For small lists, the IN operator executes nearly instantaneously. However, if your subquery list contains millions of IDs, performance will degrade.

In massive relational databases, utilizing an EXISTS operator is often faster. Regardless, IN remains the most readable and commonly used SQL operator.

Summary

The IN operator effectively cleans up queries burdened by excessive OR conditions. It accepts hardcoded textual arrays or dynamically generated subqueries.

Use NOT IN to establish aggressive data blacklists efficiently.