Filtering Rows with WHERE (PostgreSQL)

Up to this point, every query we’ve written has returned all rows from a table.

But in real applications, we usually don’t want everything.

Most of the time, we want to fetch only specific rows that match some condition.

This is where the WHERE clause comes in.

---

Why Do We Need WHERE?

Imagine we have a table of cities, but we only want:

• cities with a large area

• or cities with a high population

• or cities from a specific country

Instead of fetching all rows and filtering them manually, we let PostgreSQL do the filtering for us.

---

Basic Example

Let’s say we want to fetch:

• city name

• area

• only for cities with area greater than 4000

Here is the query:

select
  name,
  area
from cities
where area > 4000;

Result

This query returns:

• Tokyo

• Shanghai

Both of these cities have an area greater than 4000.

---

How WHERE Works

The WHERE keyword is used to filter rows.

The condition written after WHERE decides:

• which rows to keep

• and which rows to discard

In our case:

where area > 4000

means:

“Only include rows where the area value is greater than 4000.”

---

Important: How PostgreSQL Thinks About This Query

A very common beginner mistake is to assume that SQL runs left to right.

That’s not how PostgreSQL works internally.

Let’s understand the actual execution order.

---

Actual Execution Order (Very Important)

Even though we write:

select name, area
from cities
where area > 4000;

PostgreSQL thinks about it in this order:

1️⃣ FROM

First, PostgreSQL looks at the data source.

FROM cities

It loads all rows from the cities table.

---

2️⃣ WHERE

Next, PostgreSQL applies the filter.

WHERE area > 4000

Rows that do not match this condition are removed.

In our example:

• Delhi → removed

• Sao Paulo → removed

Remaining rows:

• Tokyo

• Shanghai

---

3️⃣ SELECT

Finally, PostgreSQL decides which columns to show.

SELECT name, area

Only the name and area columns are returned for the remaining rows.

---

Visualizing the Process

You can think of it like this:

1. Get all cities

2. Filter cities with area > 4000

3. Show only name and area

Keeping this mental model makes complex queries much easier later.

---

Key Takeaways

• WHERE is used to filter rows

• Conditions decide which rows stay or go

• SQL is not executed left to right

• Internally, PostgreSQL evaluates:

    FROM → WHERE → SELECT
  

• Understanding this order is extremely important