IN Operator in PostgreSQL
PostgreSQL IN operator compares values to a list of expressions or a subquery value. It simplifies complex searches by providing a clear and understandable alternative to many OR conditions in WHERE clauses. Simple IN operator takes a parenthesised list of explicit values on its right side. If an is 1, 2, or 3, IN (1, 2, 3) returns TRUE. This is identical to writing (a = 1 OR a = 2 OR a = 3), but it’s more concise and clearer.
The IN operator is more useful with subqueries. As in expression IN, a right-hand subquery that returns a single column of values can replace a fixed list. PostgreSQL evaluates the subquery first, then compares its results to the left-hand expression. This permits dynamic selection using other table data. For example, SELECT * FROM car_portal_app.car_model to select car models with at least one car. IN (SELECT car_model_id FROM car_portal_app.car).
SELECT car_id, registration_number FOR ‘Peugeot’ From car_portal_app.car Selected car_model_id from car_portal_app.car_model Where marke=’Peugeot’). This pattern allows DELETE statements to delete data based on matching values in another table, such as DELETE FROM car_portal_app.a. Where a_int IN (car_portal_app.b SELECT b_int). Scalar subqueries can be used in SELECT lists, WHERE clauses, and LIMIT clauses to return one column and one or zero records. PostgreSQL also allows multi-value comparisons with subquery results using a row constructor on the left side of IN.
Basic Usage with a List of Expressions
The IN operator is a comparison predicate in PostgreSQL that is mostly used to ascertain whether a value in a given list of expressions matches any other value. Its core syntax is as follows: an expression on the left, followed by IN; on the right, a parenthesised list of literal values or expressions separated by commas, as an IN (1, 2, 3). Because an IN (1, 2, 3) is equal to (a = 1 OR a = 2 OR a = 3), this construct is a quick and easy substitute for employing several OR conditions. If any of the values in the right-hand list are equal to the left-hand expression, the operator returns TRUE.
Note how IN handles NULL values: the entire IN expression will return NULL (unknown), not FALSE, if the left-hand expression returns NULL or if there is no match among the non-NULL values in the list and at least one value in the list is NULL. For Boolean combinations involving NULLs, this behaviour complies with SQL’s standard guidelines.
Usage with Subqueries
Using the IN operator with subqueries greatly expands its usefulness. A single column of values can be returned by a subquery in place of a static list of data.
This situation involves evaluating the subquery first, and then using the results as a list of values to compare the left-hand expression to. There can only be one column returned by the subquery.
Examples:
- Choosing automobile models where a car of that model is available: * FROM car_portal_app.car_model location of car_model_id (SELECT car_model_id FROM car_portal_app.car);
- To pick automobiles with the “Peugeot” model: SELECT car_portal_app.car with car_id and registration_number THE WHERE car_model_id IS (SELECT car_model_id FROM car_portal_app.car_model IF marke=’Peugeot’;
- An additional illustration of IN using a subquery: SELECT name FROM employee WHERE_id IN (SELECT employee_id FROM salesorder WHERE order_date = ‘7/19/1994’);
- In DELETE statements, the WHERE clause can also employ IN with a subquery: FROM car_portal_app.a DELETE FROM ORDERING b_int FROM car_portal_app.b WHERE a_int IN;
- The WHERE clause also allows scalar subqueries to be utilised as value expressions.
PostgreSQL has a row constructor on the left-hand side of the IN operator for more intricate comparisons that involve several values per row. This enables row-by-row comparison with a subquery’s output.
Handling NULL Values
It’s important to comprehend how the IN operator behaves with NULL values since it can occasionally cause developers to get unexpected results. The standard rules of SQL for Boolean combinations of NULL values state. IN returns NULL if left-hand expression returns NULL.
If at least one right-hand list or subquery item returns NULL and the left-hand expression does not match any non-NULL values, the IN construct returns NULL. The result won’t lie. If col is 3, Col IN (1, 2, NULL) yields NULL since 3=1 is FALSE, 3=2 is FALSE, and 3=NULL is UNKNOWN. False or NULL translates to unknown.
This behaviour emphasises the need to approach NULL-containing data with caution. Positive criteria are preferred; if NULL handling must be explicit, use IS NOT DISTINCT FROM or COALESCE.
Comparison with Other Operators and Constructs
There are similarities between the IN operator and other SQL constructs that can occasionally be used in its stead, each with unique performance characteristics and subtleties:
ANY / SOME: The semantic equivalent of the IN operator is = ANY / SOME. ANY (or SOME) returns TRUE for any array or subquery value if the comparison operator is TRUE. > ANY (ARRAY[x, y, z]) is the same as > x OR y OR z. When a subquery returns one column, “IN” becomes “EVERY”.
EXISTS: Subqueries that return at least one record return TRUE, otherwise FALSE. Rewriting an IN subquery with EXISTS may speed it up. IN may cause PostgreSQL performance issues owing to wasteful index usage. It may help to use EXISTS or INNER JOIN.
JOIN: INNER JOINS can replace many IN subqueries. A rewrite of SELECT * FROM table1 WHERE id IN (SELECT id FROM table2) is SELECT table1.Join table1 and table2 using (id). Benchmark samples can favour INNER JOIN over IN subquery. INNER JOIN was faster than EXISTS and somewhat faster than IN subqueries.
NOT IN: The logical opposite of IN is NOT IN. TRUE is returned if no list or subquery meets the left-hand expression. NULL values affect NOT IN strongly. If the right-hand list or subquery has even one NULL value and the left-hand expression does not match any non-NULL values, the NOT IN expression returns NULL. This makes NOT IN famously NULL-problematic. NULLs are clearer and faster with NOT EXISTS or LEFT JOIN… WHERE… IS NULL. Example: x NOT IN y = NOT.
Code Example:
-- Sample tables
CREATE TABLE t1(id INT);
CREATE TABLE t2(id INT);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(NULL);
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE id = ANY (SELECT id FROM t2);
SELECT * FROM t1 t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id);
SELECT t1.* FROM t1 INNER JOIN t2 ON t1.id = t2.id;
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2); -- returns 0 rows due to NULL
SELECT * FROM t1 t WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id);Output:
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
id
2
3
(2 rows)
id
2
3
(2 rows)
id
2
3
(2 rows)
id
2
3
(2 rows)
id
(0 rows)
id
1
(1 row)Conclusion
In PostgreSQL, the IN operator is versatile and typically used to validate set membership. It optimises complex OR conditions and explicit lists and subquery results. NULL values can make the expression evaluate to NULL instead of FALSE, therefore be careful while using it. Converting IN to INNER JOIN or EXISTS clauses may improve index use for performance-critical queries, especially with large datasets with probable NULLs.