Querying Data

The SELECT statement is the workhorse of SQL. PostgreSQL supports the full SQL standard plus many extensions that make complex queries concise and efficient.

Basic SELECT

-- Retrieve all columns
SELECT * FROM products;

-- Retrieve specific columns with an alias
SELECT name, price, price * 0.9 AS discounted_price
FROM products;

Filtering with WHERE

SELECT name, price
FROM products
WHERE price < 50
  AND category_id = 3;

-- Pattern matching
SELECT name FROM products WHERE name ILIKE '%shirt%';

-- IN list
SELECT * FROM products WHERE category_id IN (1, 2, 5);

-- Range
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

ILIKE is PostgreSQL's case-insensitive version of LIKE.

Sorting and Limiting

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10
OFFSET 20;

LIMIT and OFFSET implement pagination. For large datasets, keyset (cursor) pagination is more efficient.

Aggregation

SELECT category_id,
       COUNT(*)        AS total,
       AVG(price)      AS avg_price,
       MAX(price)      AS max_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 25
ORDER BY avg_price DESC;

The HAVING clause filters groups after aggregation, while WHERE filters individual rows before aggregation.

Joins

-- INNER JOIN: only rows with a match in both tables
SELECT p.name, c.name AS category
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- LEFT JOIN: all products, even those without a category
SELECT p.name, c.name AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;

Subqueries and CTEs

Common Table Expressions (CTEs) make complex queries readable:

WITH expensive AS (
  SELECT * FROM products WHERE price > 100
)
SELECT name, price FROM expensive ORDER BY price DESC;

Mastering SELECT with filtering, aggregation, and joins lets you answer almost any question your data can answer. PostgreSQL's query planner optimises these queries automatically, choosing the most efficient execution plan based on available indexes and table statistics.