Filtering and Sorting Data

Welcome back.

In the previous lesson, you learned about data types and constraints.

You learned that PostgreSQL should not accept nonsense.

No banana ages.

No negative prices.

No duplicate emails.

No mysterious empty users walking into your database like they own the place.

Very good.

Today we learn how to find the data we actually need.

Because once your table has many rows, this is not enough:

SELECT * FROM products;

That gives everything.

Sometimes you do not want everything.

You want:

products cheaper than 100;
users from one city;
active customers;
books published after a certain year;
courses that contain the word JavaScript;
the most expensive products;
only the first five results.

This is where filtering and sorting become important.

A database is useful not because it stores data.

A database is useful because it helps you find the right data.

Otherwise it is just a very expensive drawer.

What You Will Learn

In this lesson, you will learn:

how to filter rows with WHERE;
how comparison operators work;
how to use =;
how to use !=;
how to use >;
how to use <;
how to use >=;
how to use <=;
how to combine conditions with AND;
how to combine conditions with OR;
how to search text with LIKE;
how to search text without case sensitivity using ILIKE;
how to sort data with ORDER BY;
how to limit results with LIMIT;
how to skip results with OFFSET;
how to avoid common filtering mistakes.

By the end of this lesson, you will be able to ask PostgreSQL more precise questions.

Not:

Give me everything.

But:

Give me active customers from Milan, ordered by name, but only the first 10.

Much better.

Much more professional.

Less database shouting.

Prepare the Database

Open PostgreSQL:

sudo -iu postgres psql

Connect to the database:

\c learning_postgresql

If you do not have this database, create it:

CREATE DATABASE learning_postgresql;

Then connect:

\c learning_postgresql

Now create a table for this lesson.

We will use products.

First remove the old table if it exists:

DROP TABLE IF EXISTS products;

Now create it:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100),
  price NUMERIC(10, 2) CHECK (price >= 0),
  available BOOLEAN DEFAULT true,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);

This table has:

id;
name;
category;
price;
available;
rating.

Now insert data:

INSERT INTO products (name, category, price, available, rating)
VALUES
  ('Laptop', 'Electronics', 900.00, true, 5),
  ('Mouse', 'Electronics', 25.00, true, 4),
  ('Keyboard', 'Electronics', 70.00, false, 4),
  ('Desk Chair', 'Furniture', 150.00, true, 5),
  ('Notebook', 'Office', 5.00, true, 3),
  ('Pen', 'Office', 2.00, true, 3),
  ('Desk Lamp', 'Furniture', 45.00, true, 4),
  ('Monitor', 'Electronics', 250.00, true, 5),
  ('Bookshelf', 'Furniture', 120.00, false, 4),
  ('USB Cable', 'Electronics', 10.00, true, 2);

Check the data:

SELECT * FROM products;

Now we have enough data to practice.

Not millions of rows.

But enough for PostgreSQL to stretch a little.

Like database yoga.

WHERE

WHERE filters rows.

Without WHERE, PostgreSQL returns all rows:

SELECT * FROM products;

With WHERE, PostgreSQL returns only rows that match a condition:

SELECT * FROM products
WHERE category = 'Electronics';

This means:

Give me only products where category is Electronics.

You should see products like:

Laptop;
Mouse;
Keyboard;
Monitor;
USB Cable.

WHERE is one of the most important parts of SQL.

It helps you ask specific questions.

And specific questions get useful answers.

Very life lesson.

Also very SQL.

Filtering by Text

To filter by text, use single quotes.

Example:

SELECT * FROM products
WHERE category = 'Office';

This returns products in the Office category.

Another example:

SELECT * FROM products
WHERE name = 'Laptop';

This returns the product named Laptop.

Important:

Text values need single quotes.

Correct:

WHERE name = 'Laptop'

Wrong:

WHERE name = Laptop

Without quotes, PostgreSQL thinks Laptop is a column name.

PostgreSQL is not guessing.

It is a database.

Not a mind reader.

Filtering by Boolean

Boolean columns store true or false.

Example:

SELECT * FROM products
WHERE available = true;

This returns available products.

You can also find unavailable products:

SELECT * FROM products
WHERE available = false;

This is useful for values like:

active;
published;
completed;
available;
verified.

Do not store this kind of data as text like:

yes
no
maybe

Use BOOLEAN.

Your queries will be cleaner.

Your database will complain less.

Everyone wins.

Almost.

Comparison Operators

PostgreSQL lets you compare values.

Common comparison operators:

=   equal
!=  not equal
>   greater than
<   less than
>=  greater than or equal
<=  less than or equal

Important for MDX:

When writing operators like <, <=, >, and >= in normal text, put them inside backticks.

Otherwise MDX may think you are trying to write JSX.

And then it may explode emotionally.

SQL code blocks are safe.

Markdown text is the dangerous neighborhood.

Greater Than

Find products with price greater than 100:

SELECT * FROM products
WHERE price > 100;

This returns products like:

Laptop;
Desk Chair;
Monitor;
Bookshelf.

This is useful when you want expensive items.

Or when you want to know why your wallet is crying.

Less Than

Find products with price less than 50:

SELECT * FROM products
WHERE price < 50;

This returns cheaper products like:

Mouse;
Notebook;
Pen;
Desk Lamp;
USB Cable.

This is useful for discounts, budgets, filters, and moments when life says:

Maybe not the 900 euro laptop today.

Sad.

But financially responsible.

Greater Than or Equal

Find products with rating greater than or equal to 4:

SELECT * FROM products
WHERE rating >= 4;

This returns products with ratings 4 and 5.

The operator >= means:

greater than or equal

So 4 is included.

This is useful when you want good products.

Not perfect.

But good enough.

Like many real projects.

Less Than or Equal

Find products with price less than or equal to 25:

SELECT * FROM products
WHERE price <= 25;

This includes products with price 25.

So Mouse appears because its price is exactly 25.

The operator <= means:

less than or equal

Tiny symbols.

Big meaning.

Databases are full of this.

Not Equal

Find products that are not in the Electronics category:

SELECT * FROM products
WHERE category != 'Electronics';

This returns products from other categories.

You can also use:

SELECT * FROM products
WHERE category <> 'Electronics';

Both != and <> can mean not equal in PostgreSQL.

In this course, we will usually use != because it is easy to read.

PostgreSQL gives options.

Sometimes too many.

But these are fine.

AND

AND combines conditions.

All conditions must be true.

Example:

SELECT * FROM products
WHERE category = 'Electronics'
AND price < 100;

This means:

Give me products where category is Electronics and price is less than 100.

Both conditions must be true.

So this may return:

Mouse;
Keyboard;
USB Cable.

But not Laptop.

Laptop is Electronics, yes.

But Laptop is not cheaper than 100.

Laptop has chosen the expensive lifestyle.

More AND Examples

Find available Electronics products:

SELECT * FROM products
WHERE category = 'Electronics'
AND available = true;

Find Furniture products with rating 5:

SELECT * FROM products
WHERE category = 'Furniture'
AND rating = 5;

Find products cheaper than 100 and rated at least 4:

SELECT * FROM products
WHERE price < 100
AND rating >= 4;

AND is strict.

Every condition must pass.

Like a very serious checklist.

No condition left behind.

OR

OR combines conditions where at least one condition must be true.

Example:

SELECT * FROM products
WHERE category = 'Office'
OR category = 'Furniture';

This means:

Give me products where category is Office or Furniture.

A product can match either condition.

It does not need to match both.

Because something cannot usually be both Office and Furniture.

Unless your office chair is having an identity crisis.

More OR Examples

Find products that are very cheap or highly rated:

SELECT * FROM products
WHERE price < 10
OR rating = 5;

This returns products that match at least one condition.

So a product appears if:

its price is less than 10;
or its rating is 5;
or both.

OR is more flexible than AND.

Sometimes too flexible.

Use carefully.

AND and OR Together

You can combine AND and OR.

But you must be careful.

Example:

SELECT * FROM products
WHERE category = 'Electronics'
AND price < 100
OR rating = 5;

This may not mean what you think.

PostgreSQL evaluates AND before OR.

So the query is interpreted like this:

SELECT * FROM products
WHERE (category = 'Electronics' AND price < 100)
OR rating = 5;

That means:

Electronics cheaper than 100, or anything with rating 5.

If you want a different meaning, use parentheses.

Parentheses make your intention clear.

Your future self will thank you.

Maybe with coffee.

Parentheses

Use parentheses to group conditions.

Example:

SELECT * FROM products
WHERE category = 'Electronics'
AND (price < 100 OR rating = 5);

This means:

Give me Electronics products where price is less than 100 or rating is 5.

Now the product must be Electronics.

Then it must also match one of the conditions inside parentheses.

Parentheses are not decoration.

They are logic fences.

Use them when mixing AND and OR.

Otherwise your query may become a wild animal.

LIKE

LIKE searches for text patterns.

Example:

SELECT * FROM products
WHERE name LIKE 'Desk%';

This finds names that start with Desk.

The % symbol means:

anything can come here

So Desk% matches:

Desk Chair;
Desk Lamp.

Another example:

SELECT * FROM products
WHERE name LIKE '%book%';

This finds names that contain book.

It can match:

Notebook;
Bookshelf.

But there is something important.

LIKE is case-sensitive in many situations.

So book may not match Book.

PostgreSQL has another option.

It is called ILIKE.

ILIKE

ILIKE is like LIKE, but case-insensitive.

Example:

SELECT * FROM products
WHERE name ILIKE '%book%';

This can match:

Notebook;
Bookshelf;
BOOKSHELF;
notebook.

Very useful.

Especially when users type text like humans.

Humans are not consistent.

Sometimes they type laptop.

Sometimes Laptop.

Sometimes LAPTOP.

Sometimes lapotp.

That last one is not PostgreSQL's fault.

ORDER BY

ORDER BY sorts results.

Sort by price from low to high:

SELECT * FROM products
ORDER BY price;

Same as:

SELECT * FROM products
ORDER BY price ASC;

Sort by price from high to low:

SELECT * FROM products
ORDER BY price DESC;

Sort by name alphabetically:

SELECT * FROM products
ORDER BY name;

Sorting makes data easier to read.

Without sorting, rows come in whatever order PostgreSQL returns them.

Do not rely on natural order.

Natural order is not a contract.

It is a mood.

ORDER BY with WHERE

You can filter and sort at the same time.

Example:

SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

This means:

Give me Electronics products, sorted from most expensive to cheapest.

Another example:

SELECT name, price FROM products
WHERE available = true
ORDER BY price ASC;

This means:

Give me available products, sorted by price from low to high.

This is very common in real applications.

Filter first.

Sort results.

Show to user.

The user thinks the website is smart.

Really, it is SQL doing the heavy lifting.

Quietly.

Like a tired warehouse worker.

LIMIT

LIMIT controls how many rows are returned.

Example:

SELECT * FROM products
LIMIT 3;

This returns only three rows.

Find the three most expensive products:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

This is very useful for:

top products;
latest posts;
first page of results;
small previews;
dashboards.

Without LIMIT, your query may return too much data.

And too much data is like too much pasta.

At first exciting.

Then dangerous.

OFFSET

OFFSET skips rows.

Example:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

This means:

Skip the first 3 rows, then return 3 rows.

This is useful for pagination.

Page 1:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 0;

Page 2:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

Page 3:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 6;

Pagination is how websites show results in pages.

Not everything at once.

Because nobody wants to load 5000 products just to find a mouse.

Except maybe the database.

The database does not complain.

But your users will.

Combining Everything

Now let us combine filtering, sorting, and limiting.

Example:

SELECT name, category, price, rating
FROM products
WHERE available = true
AND price >= 10
ORDER BY rating DESC, price ASC
LIMIT 5;

This means:

Give me available products with price at least 10.
Show only name, category, price, and rating.
Sort by rating from high to low.
If ratings are equal, sort by price from low to high.
Return only 5 rows.

This is a real query.

Not toy theory.

This is the kind of thing real applications do constantly.

Search.

Filter.

Sort.

Limit.

Display.

Repeat.

Web development is often just this with nicer buttons.

Common Mistakes

Forgetting Quotes Around Text

Wrong:

SELECT * FROM products
WHERE category = Electronics;

Correct:

SELECT * FROM products
WHERE category = 'Electronics';

Text values need single quotes.

Column names do not.

PostgreSQL will not guess.

Again.

It has boundaries.

Good for PostgreSQL.

Confusing AND and OR

This:

WHERE category = 'Office'
OR category = 'Furniture'

means either one.

This:

WHERE category = 'Office'
AND category = 'Furniture'

usually returns nothing.

Because one product usually cannot have both categories at the same time.

AND means all conditions.

OR means at least one condition.

Small words.

Big consequences.

Like “yes” and “no” in contracts.

Forgetting Parentheses

Dangerous:

WHERE category = 'Electronics'
AND price < 100
OR rating = 5

Better:

WHERE category = 'Electronics'
AND (price < 100 OR rating = 5)

Parentheses make logic clear.

Clear logic means fewer surprises.

Fewer surprises means less screaming at the terminal.

Healthy.

Using LIKE When You Need ILIKE

This may miss results because of case:

WHERE name LIKE '%book%'

This is often better for user search:

WHERE name ILIKE '%book%'

Users do not care about your case sensitivity.

They just want results.

PostgreSQL can help.

Let it.

Practice

Write queries for the products table.

Find all Electronics products:

SELECT * FROM products
WHERE category = 'Electronics';

Find products cheaper than 50:

SELECT * FROM products
WHERE price < 50;

Find available products with rating at least 4:

SELECT * FROM products
WHERE available = true
AND rating >= 4;

Find products with names containing desk:

SELECT * FROM products
WHERE name ILIKE '%desk%';

Find the three most expensive products:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

Run each query.

Change values.

Break things.

Fix things.

This is how SQL becomes normal.

Mini Challenge

Create a table named students.

It should have:

id;
name;
city;
age;
active;
score.

Insert at least eight students.

Then write queries to:

show all students;
show students from one city;
show students older than 18;
show active students;
show students with score greater than or equal to 80;
show students from one city and active;
show students whose name contains an;
sort students by score from highest to lowest;
show only the top three students by score.

This challenge is important.

Because filtering and sorting are everywhere.

If you can write these queries, you are no longer just storing data.

You are controlling data.

Carefully.

Hopefully.

Summary

Today you learned:

WHERE filters rows;
= checks equality;
!= checks not equal;
> checks greater than;
< checks less than;
>= checks greater than or equal;
<= checks less than or equal;
AND requires all conditions to be true;
OR requires at least one condition to be true;
parentheses make complex logic clear;
LIKE searches text patterns;
ILIKE searches text patterns without case sensitivity;
ORDER BY sorts results;
ASC sorts ascending;
DESC sorts descending;
LIMIT limits returned rows;
OFFSET skips rows.

This is a big step.

Now you can ask PostgreSQL precise questions.

Not just:

SELECT * FROM products;

But:

SELECT name, price
FROM products
WHERE available = true
AND price < 100
ORDER BY price ASC
LIMIT 5;

That is real database power.

Small query.

Big usefulness.

Very PostgreSQL.

Next Lesson

In the next lesson, we will learn relationships with primary keys and foreign keys.

This is where tables start talking to each other.

Students and courses.

Users and orders.

Products and categories.

One table is useful.

Connected tables are where relational databases become powerful.

And slightly more dramatic.