PostgreSQL: Using the IN Condition for Efficient Filtering

Efficiently filter data in PostgreSQL using the IN condition. This guide explains the syntax and usage of IN with various SQL commands.

PostgreSQL IN Condition

Introduction to the PostgreSQL IN Condition

The PostgreSQL IN condition, used within the WHERE clause, retrieves data matching any value in a specified list. It's a more efficient way to express multiple OR conditions.

PostgreSQL IN Condition Syntax

The IN condition works with SELECT, INSERT, UPDATE, and DELETE commands. There are two main syntaxes:

expression IN (SELECT column_name FROM table_name);
OR
expression IN (value1, value2, ..., valueN);
            

The first uses a subquery (a nested SELECT statement), while the second uses a list of values.

A more concise way to represent this is:

expression IN (subquery);
            

Parameters

Note: The IN condition returns true if the expression matches any value in the provided list. This list can contain literal values (strings, numbers) or the results of a SELECT statement.

Examples of PostgreSQL IN Condition

Example 1: Using Character Values

Let's retrieve employee information (emp_fname) for employees named 'John' and 'Ava' from an employee table.

SELECT *
FROM employee
WHERE emp_fname IN ('John', 'Ava')
ORDER BY emp_id DESC;
            

This is equivalent to using OR:

SELECT *
FROM employee
WHERE emp_fname ='John' OR emp_fname ='Ava'
ORDER BY emp_id DESC;
            

IN is generally more readable and often more efficient.

Example 2: Using Numeric Values

Let's get department information for emp_id 1, 2, and 3 from a department table.

SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id IN (1, 2, 3)
ORDER BY dept_name DESC;
            

Again, the equivalent using OR is less concise:

SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id=1 OR emp_id=2 OR emp_id=3
ORDER BY dept_name DESC;
            

Example 3: Using a Subquery

This example uses a subquery to find employees who joined on '2020-06-22'.

SELECT emp_id, emp_fname, emp_lname
FROM employee
WHERE emp_id IN (
    SELECT emp_id
    FROM department
    WHERE CAST(Joining_date AS DATE) = '2020-06-22'
)
ORDER BY emp_id;
            

Overview

We've explored the PostgreSQL IN operator for efficiently checking if a value matches any item in a list or the result of a subquery.