Subqueries in WHERE with IN in PostgreSQL

Introduction

We use subqueries with IN to find rows where a column matches any value from another list of values. It helps us filter data based on related information.

You want to find customers who have placed orders by checking order records.

You want to list products that appear in any sales transactions.

You want to get employees who work in departments located in a specific city.

You want to find students enrolled in courses offered this semester.

Syntax

PostgreSQL

SELECT column1, column2
FROM table1
WHERE columnX IN (SELECT columnY FROM table2 WHERE condition);

The subquery inside IN returns a list of values to compare against.

The main query returns rows where columnX matches any value from the subquery.

Examples

Finds customers who have made at least one order.

PostgreSQL

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Lists products that belong to the 'Books' category.

PostgreSQL

SELECT product_name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Books');

Finds employees working in departments located in New York.

PostgreSQL

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

Sample Program

This example creates two tables: departments and employees. It inserts some data, then finds employees who work in departments located in New York.

PostgreSQL

CREATE TABLE departments (id SERIAL PRIMARY KEY, name TEXT, location TEXT);
CREATE TABLE employees (id SERIAL PRIMARY KEY, employee_name TEXT, department_id INT);

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Engineering', 'San Francisco'),
('HR', 'New York');

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 3),
('Diana', 1);

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

Important Notes

Subqueries inside IN must return a single column.

Using IN with subqueries is easy to read and understand for beginners.

Performance can be slower with large data; sometimes JOINs are faster.

Summary

Use subqueries with IN to filter rows based on a list from another query.

The subquery returns values that the main query compares against.

This helps connect related data across tables simply.