SELECT, WHERE, and ORDER BY

Querying Data with SELECT

The SELECT statement is the heart of SQL. Every data retrieval operation starts with SELECT, and mastering its clauses — WHERE, ORDER BY, LIMIT, DISTINCT — gives you the foundation for increasingly sophisticated queries.

The full logical order in which SQL processes a SELECT statement is:

1. FROM — identify the source table(s)
2. WHERE — filter rows
3. GROUP BY — group rows (covered in the next chapter)
4. HAVING — filter groups (covered in the next chapter)
5. SELECT — compute the output columns
6. DISTINCT — remove duplicates
7. ORDER BY — sort the results
8. LIMIT / OFFSET — paginate

Understanding this order is important because it explains why, for example, you cannot reference a column alias from SELECT inside a WHERE clause — WHERE is evaluated before SELECT.

The Basic SELECT Syntax

SELECT column1, column2, expression AS alias
FROM   table_name
WHERE  condition
ORDER BY column1 ASC, column2 DESC
LIMIT  n
OFFSET m;

Every clause except SELECT and FROM is optional, but when you use them, the order above must be respected.

Selecting Columns and Expressions

You can select literal values, column names, and computed expressions:

SELECT
    name,
    price,
    stock,
    price * stock          AS inventory_value,
    price * 0.90           AS discounted_price,
    UPPER(name)            AS name_uppercase,
    LENGTH(name)           AS name_length
FROM products;

PostgreSQL evaluates expressions row-by-row. The computed columns appear in the result set with the alias name but do not modify the stored data.

Filtering with WHERE

The WHERE clause filters rows using boolean conditions. Only rows for which the condition evaluates to TRUE are included in the result.

Comparison Operators

-- Equal
WHERE price = 29.99

-- Not equal (both notations work)
WHERE category <> 'electronics'
WHERE category != 'electronics'

-- Greater / less than
WHERE price > 100.00
WHERE price <= 50.00
WHERE price BETWEEN 20.00 AND 100.00   -- equivalent to price >= 20 AND price <= 100

Logical Operators: AND, OR, NOT

-- AND: both conditions must be true
SELECT name, price, stock
FROM products
WHERE price < 100.00 AND stock > 50;

-- OR: at least one condition must be true
SELECT name, category
FROM products
WHERE category = 'furniture' OR category = 'stationery';

-- NOT: negates a condition
SELECT name, price
FROM products
WHERE NOT category = 'electronics';

-- Mixing AND and OR: use parentheses to control precedence
SELECT name, price, category
FROM products
WHERE (category = 'electronics' OR category = 'furniture')
  AND price > 100.00;

AND has higher precedence than OR, so always use parentheses when combining both to make intent explicit.

Pattern Matching with LIKE and ILIKE

-- LIKE is case-sensitive
WHERE name LIKE 'Laptop%'      -- starts with 'Laptop'
WHERE name LIKE '%Keyboard'    -- ends with 'Keyboard'
WHERE name LIKE '%USB%'        -- contains 'USB'
WHERE name LIKE 'M_use'        -- M followed by any character, then 'use'

-- ILIKE is case-insensitive (PostgreSQL extension)
WHERE name ILIKE '%keyboard%'  -- matches 'Keyboard', 'KEYBOARD', 'keyboard'

IN and NOT IN

-- Cleaner than multiple OR conditions
SELECT name, category, price
FROM products
WHERE category IN ('electronics', 'furniture');

-- Exclude certain values
SELECT name
FROM products
WHERE name NOT IN ('Notebook A5', 'Ballpoint Pens 12pk');

Handling NULL Values

NULL is a special value in SQL meaning "unknown" or "absent." It behaves differently from regular values:

-- NULL comparisons use IS NULL / IS NOT NULL, not = or !=
SELECT name FROM products WHERE description IS NULL;
SELECT name FROM products WHERE description IS NOT NULL;

-- NULL in arithmetic produces NULL
SELECT NULL + 5;          -- returns NULL
SELECT COALESCE(NULL, 0); -- returns 0 (use COALESCE to substitute a default)

A common mistake is writing WHERE description = NULL — this always returns no rows because NULL equals nothing, not even itself.

Sorting with ORDER BY

ORDER BY sorts the result set by one or more columns or expressions:

-- Single column, ascending (default)
SELECT name, price FROM products ORDER BY price;
SELECT name, price FROM products ORDER BY price ASC;   -- same thing

-- Single column, descending
SELECT name, price FROM products ORDER BY price DESC;

-- Multiple columns: sort by category first, then by price within each category
SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

-- Sort by an expression
SELECT name, price, stock
FROM products
ORDER BY price * stock DESC;  -- most valuable inventory first

-- NULLS FIRST / NULLS LAST: control where NULLs appear
SELECT name, published_at FROM books ORDER BY published_at NULLS LAST;

Limiting Results with LIMIT and OFFSET

LIMIT restricts the number of rows returned. OFFSET skips a number of rows before starting to return results. Together, they implement pagination:

-- First page: top 5 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 0;

-- Second page
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 5;

-- Third page
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5 OFFSET 10;

The formula for pagination is: OFFSET = (page_number - 1) * page_size.

Important: always pair LIMIT/OFFSET with ORDER BY. Without explicit ordering, PostgreSQL returns rows in an undefined order that may change between queries.

For very large datasets, LIMIT/OFFSET becomes slow at high offsets because PostgreSQL must read and discard all the skipped rows. Keyset pagination (using WHERE id > last_seen_id) is more efficient for deep pagination.

Removing Duplicates with DISTINCT

DISTINCT removes duplicate rows from the result:

-- Get all unique categories
SELECT DISTINCT category FROM products ORDER BY category;

-- DISTINCT on multiple columns: removes rows where ALL specified columns are identical
SELECT DISTINCT category, stock > 50 AS well_stocked
FROM products
ORDER BY category;

DISTINCT can be expensive on large tables because it requires sorting or hashing the entire result set to identify duplicates. Use it only when you genuinely need unique values.

Computed Columns and String Functions

PostgreSQL provides a rich library of built-in functions for manipulating data in SELECT:

SELECT
    UPPER(name)                                AS name_upper,
    LOWER(name)                                AS name_lower,
    TRIM('  hello  ')                          AS trimmed,
    CONCAT(first_name, ' ', last_name)         AS full_name,
    SUBSTRING(name FROM 1 FOR 10)              AS name_short,
    ROUND(price, 0)                            AS price_rounded,
    ABS(-42)                                   AS absolute_value,
    CURRENT_TIMESTAMP                          AS now_utc,
    TO_CHAR(created_at, 'YYYY-MM-DD')          AS created_date
FROM products;

These functions execute inside the database engine — no need to fetch rows to your application and process them there.

With SELECT, WHERE, ORDER BY, LIMIT, and DISTINCT mastered, you can retrieve any subset of your data in any order. The next lesson covers the other half of SQL DML: writing data with INSERT, UPDATE, and DELETE.