Why Subqueries in WHERE with IN in PostgreSQL? - Purpose & Use Cases

The Big Idea

Discover how a simple subquery can save you hours of tedious checking!

The Scenario

Imagine you have a big list of customers and a separate list of orders. You want to find all customers who have made at least one order. Doing this by hand means checking each customer against every order one by one.

The Problem

Manually comparing each customer to every order is slow and tiring. It's easy to miss some matches or make mistakes, especially when the lists are large. This wastes time and causes errors.

The Solution

Using a subquery with IN lets you ask the database to find all customers whose IDs appear in the orders list automatically. This saves time and avoids mistakes by letting the database do the hard work.

Before vs After

✗ Before

SELECT * FROM customers WHERE id = 1 OR id = 2 OR id = 3;

✓ After

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

What It Enables

This lets you quickly find related data across tables without writing long, complicated checks.

Real Life Example

A store manager wants to see all customers who placed orders this month. Instead of checking each customer manually, they use a subquery with IN to get the list instantly.

Key Takeaways

Manually checking matches is slow and error-prone.

Subqueries with IN let the database find matches automatically.

This makes queries simpler, faster, and more reliable.