SELECT Queries

Basic SELECT

SELECT is the foundation of SQL — it’s how you ask the database a question and get rows back. At its simplest it retrieves columns from a table, but its real power comes from combining filtering, sorting, computation, and aggregation in a single declarative statement. Understanding SELECT well means you spend less time writing application-layer data processing code.

-- All columns, all rows — useful for exploration but avoid in production code
SELECT * FROM products;

-- Specific columns only — preferred: explicit, efficient, resilient to schema changes
SELECT name, price FROM products;

Prefer listing specific columns over SELECT * in production code. SELECT * breaks if a column is added or reordered, and it fetches data you may not need.

Column Aliases

Aliases let you rename columns in the output, which is essential when computing new values — the expression itself isn’t a valid column name, so you need to give it one. Aliases also make result sets more readable for the code that consumes them.

SELECT
    name                        AS product_name,
    price                       AS unit_price,
    price * 1.08                AS price_with_tax,   -- computed value needs a name
    UPPER(name)                 AS name_upper
FROM products;

The alias is purely for output — you can’t reference it in the WHERE clause of the same query (see the execution order in the FAQ).

SELECT DISTINCT

DISTINCT removes duplicate rows from the result. This is useful when you want to know the set of distinct values in a column — for example, all categories that actually have products, or all countries where customers are located.

-- All unique categories in the products table
SELECT DISTINCT category FROM products ORDER BY category;

-- Distinct combinations of two columns
SELECT DISTINCT category, brand FROM products;

WHERE Clause

WHERE filters rows before they reach the output. Only rows where the condition evaluates to TRUE are included. It’s the primary tool for narrowing large tables down to the rows you actually care about, and it’s where indexes do their work.

SELECT name, price FROM products WHERE price < 50.00;
SELECT name FROM products WHERE category = 'Electronics';
SELECT * FROM orders WHERE status != 'cancelled';

Comparison and Logical Operators

SQL’s comparison and logical operators let you express arbitrarily precise conditions. The key behaviors to know: AND binds tighter than OR, NULL comparisons always return NULL (use IS NULL instead of = NULL), and IN is cleaner than a long chain of OR conditions.

-- Numeric comparisons
SELECT * FROM products WHERE price BETWEEN 20.00 AND 100.00;
SELECT * FROM products WHERE stock > 0 AND price < 50.00;
SELECT * FROM products WHERE category = 'Books' OR category = 'Music';
SELECT * FROM products WHERE NOT is_archived;

-- IN replaces multiple OR conditions — cleaner and often faster
SELECT * FROM products WHERE category IN ('Books', 'Music', 'Games');

-- Pattern matching with LIKE (case-sensitive) and ILIKE (case-insensitive)
SELECT * FROM products WHERE name LIKE 'Wire%';       -- starts with "Wire"
SELECT * FROM products WHERE name ILIKE '%keyboard%'; -- contains "keyboard"
SELECT * FROM products WHERE sku LIKE 'EL-____';      -- 4 chars after "EL-"

NULL Handling

NULL represents an unknown or missing value — not zero, not an empty string. Comparisons with NULL using = or != always return NULL (not TRUE or FALSE), so those rows are silently excluded. This is one of the most common sources of unexpected empty result sets.

SELECT * FROM employees WHERE manager_id IS NULL;         -- top-level employees
SELECT * FROM products WHERE discontinued_at IS NOT NULL; -- discontinued items

-- COALESCE returns the first non-NULL value — use for display or calculation fallbacks
SELECT name, COALESCE(discount_price, price) AS effective_price FROM products;

ORDER BY

Without ORDER BY, PostgreSQL can return rows in any order — the order may seem consistent in development but will vary under load or after a VACUUM. Always include ORDER BY when the order of results matters to your application, especially for pagination.

-- Ascending (default)
SELECT name, price FROM products ORDER BY price;

-- Descending
SELECT name, price FROM products ORDER BY price DESC;

-- Sort by multiple columns: primary sort by category, secondary by price within each category
SELECT name, category, price FROM products ORDER BY category ASC, price DESC;

-- Control where NULLs appear — by default NULLs sort last in ASC, first in DESC
SELECT name, discontinued_at FROM products ORDER BY discontinued_at NULLS LAST;

You can sort by column alias or by column position (though position-based sorting is fragile and best avoided):

SELECT name, price * 1.08 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC;

LIMIT and OFFSET

LIMIT and OFFSET are how you implement pagination. They’re simple and work well for small result sets, but have a scalability problem at large offsets that’s worth understanding before you design pagination into a high-traffic feature.

-- Top 5 most expensive products
SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

-- Page 3 of results, 10 per page (skip first 20)
SELECT name, price FROM products ORDER BY name LIMIT 10 OFFSET 20;

Always pair LIMIT/OFFSET with ORDER BY. Without an explicit sort order, PostgreSQL can return rows in any order, making pagination results unpredictable.

Why Large OFFSETs Are Slow

OFFSET 10000 tells PostgreSQL to scan and discard the first 10,000 rows before returning the next batch. The work grows linearly with the offset. For paginating deep into large tables, keyset pagination is far more efficient because it uses an index to jump directly to the right position.

-- Instead of OFFSET, remember the last seen ID and filter on it
-- Page 1: no filter needed
SELECT id, name, price FROM products ORDER BY id LIMIT 20;

-- Page 2: filter using the last id returned from page 1 (e.g., 20)
-- This uses the index on id and stays fast regardless of page depth
SELECT id, name, price FROM products WHERE id > 20 ORDER BY id LIMIT 20;

Computed Expressions in SELECT

You can compute new values directly in SELECT using arithmetic, functions, and conditional logic. This moves simple data transformations into the database where they run close to the data, avoiding an extra processing step in application code.

SELECT
    name,
    price,
    price * 0.90                                    AS sale_price,
    ROUND(price * 1.08, 2)                          AS price_with_tax,
    CASE
        WHEN price < 25  THEN 'Budget'
        WHEN price < 100 THEN 'Mid-range'
        ELSE 'Premium'
    END                                             AS price_tier,    -- conditional bucketing
    CURRENT_DATE - created_at::DATE                 AS days_since_added
FROM products;

CASE WHEN ... THEN ... ELSE ... END is SQL’s conditional expression — equivalent to an if/else. It’s available anywhere an expression is valid.

SELECT Without FROM

PostgreSQL lets you run SELECT without referencing any table. This is useful for quick calculations, testing expressions, and confirming behavior before using an expression in a real query.

SELECT 2 + 2;
SELECT NOW();
SELECT UPPER('hello world');
SELECT ROUND(3.14159, 2);
SELECT '2024-01-01'::DATE + INTERVAL '90 days';

Filtering with WHERE vs HAVING

A common point of confusion: WHERE filters individual rows before aggregation; HAVING filters groups after aggregation. You’ll use HAVING together with GROUP BY — but the key rule is that WHERE cannot reference aggregate functions like SUM() or COUNT().

-- WHERE filters rows before grouping; HAVING filters the groups after
SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
WHERE is_archived = FALSE       -- filter individual rows first
GROUP BY category
HAVING COUNT(*) >= 5            -- then filter groups by aggregate result
ORDER BY avg_price DESC;

This reads as: “For each category that has at least 5 active (non-archived) products, show the product count and average price, sorted by average price descending.”

Putting It All Together

Here’s a query that uses most of the clauses covered in this tutorial:

SELECT
    p.name,
    p.category,
    p.price,
    ROUND(p.price * 1.08, 2)  AS price_with_tax,
    CASE
        WHEN p.stock = 0 THEN 'Out of stock'
        WHEN p.stock < 10 THEN 'Low stock'
        ELSE 'In stock'
    END AS stock_status
FROM products p
WHERE p.is_archived = FALSE
  AND p.price BETWEEN 10.00 AND 500.00
  AND p.category ILIKE '%electronics%'
ORDER BY p.price ASC, p.name ASC
LIMIT 20 OFFSET 0;

The alias p after the table name is a table alias — it shortens repeated references. You’ll rely on table aliases heavily once you start writing JOINs, covered in the next tutorial.