Select_queries

Chapter 8: SELECT - Querying Data

Mastering Data Retrieval in PostgreSQL

---

8.1 SELECT Fundamentals

The SELECT statement is the primary means of retrieving data from PostgreSQL. It allows you to specify exactly what data you want and how it should be presented.

                    SELECT Query Flow
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                     SELECT Query Processing                         │
    │                                                                      │
    │  SELECT columns          ← Projection (what columns)               │
    │  FROM table             ← Source (which table)                     │
    │  WHERE conditions       ← Selection (which rows)                  │
    │  GROUP BY grouping      ← Grouping (aggregation)                  │
    │  HAVING group_conditions← Filter groups                           │
    │  ORDER BY sorting       ← Sorting (output order)                  │
    │  LIMIT n;              ← Limiting (how many)                      │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

    Processing Order:
    =================

    1. FROM     → Identify source tables
    2. WHERE    → Filter rows
    3. GROUP BY → Group rows
    4. HAVING   → Filter groups
    5. SELECT   → Project columns
    6. ORDER BY → Sort results
    7. LIMIT    → Limit results
    ========================================================================

---

8.2 Basic SELECT Syntax

-- Create sample tables for examples
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES
    ('john_doe', 'john@example.com'),
    ('jane_smith', 'jane@example.com'),
    ('bob_wilson', 'bob@example.com');

INSERT INTO orders (user_id, total_amount, status) VALUES
    (1, 150.00, 'completed'),
    (1, 75.50, 'pending'),
    (2, 200.00, 'completed'),
    (3, 50.00, 'cancelled');

-- Basic SELECT statements

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT username, email FROM users;

-- Select with WHERE clause
SELECT * FROM users WHERE is_active = TRUE;

-- Select with multiple conditions
SELECT * FROM orders
WHERE status = 'completed' AND total_amount > 100;

---

8.3 Column Expressions

-- Arithmetic expressions
SELECT
    id,
    total_amount,
    total_amount * 1.10 AS with_tax,
    total_amount / 2 AS half_price
FROM orders;

-- String concatenation
SELECT
    username || ' <' || email || '>' AS display_name
FROM users;

-- Using functions
SELECT
    UPPER(username) AS username_upper,
    LOWER(email) AS email_lower,
    INITCAP(username) AS username_proper
FROM users;

-- Column aliases
SELECT
    u.username AS "User Name",
    u.email AS "Email Address"
FROM users u;  -- Table alias

-- DISTINCT - remove duplicates
SELECT DISTINCT status FROM orders;
SELECT DISTINCT user_id, status FROM orders;  -- Unique combinations

-- Count with DISTINCT
SELECT COUNT(DISTINCT user_id) AS unique_customers FROM orders;

---

8.4 WHERE Clause

The WHERE clause filters rows based on conditions.

-- Basic comparisons
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id != 1;
SELECT * FROM users WHERE id <> 1;  -- Not equal alternative

-- Comparison operators
SELECT * FROM orders WHERE total_amount > 100;
SELECT * FROM orders WHERE total_amount >= 100;
SELECT * FROM orders WHERE total_amount < 100;
SELECT * FROM orders WHERE total_amount <= 100;

-- BETWEEN (inclusive)
SELECT * FROM orders
WHERE total_amount BETWEEN 50 AND 150;

-- IN (multiple values)
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM orders WHERE status IN ('pending', 'completed');

-- LIKE (pattern matching)
SELECT * FROM users WHERE username LIKE 'j%';     -- Starts with j
SELECT * FROM users WHERE username LIKE '%ohn%';  -- Contains ohn
SELECT * FROM users WHERE username LIKE 'j_n%';   -- j + any char + n

-- ILIKE (case-insensitive)
SELECT * FROM users WHERE username ILIKE 'JOHN%';

-- REGEXP (regular expressions)
SELECT * FROM users WHERE username ~ '^[a-z]+$';
SELECT * FROM users WHERE username ~* 'john';  -- Case-insensitive

-- NULL handling
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- Combining conditions
SELECT * FROM orders
WHERE status = 'completed'
  AND total_amount > 100
  AND created_at > '2024-01-01';

-- OR conditions
SELECT * FROM orders
WHERE status = 'completed' OR status = 'pending';

-- IN with subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

---

8.5 ORDER BY

Sort query results using ORDER BY.

-- Basic sorting (ascending by default)
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY username ASC;  -- Explicit

-- Descending order
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple columns
SELECT * FROM orders
ORDER BY user_id ASC, total_amount DESC;

-- NULLS FIRST/LAST
SELECT * FROM users ORDER BY email NULLS FIRST;
SELECT * FROM users ORDER BY email NULLS LAST;

-- By expression
SELECT username, LENGTH(username) AS name_length
FROM users
ORDER BY LENGTH(username) DESC;

-- By column position (1-based)
SELECT id, username, email
FROM users
ORDER BY 3;  -- Order by 3rd column (email)

-- Random order
SELECT * FROM users ORDER BY RANDOM();

-- Order by with LIMIT (useful for top-N queries)
SELECT * FROM orders
ORDER BY total_amount DESC
LIMIT 3;

---

8.6 LIMIT, OFFSET, and FETCH

Control the number of rows returned.

-- LIMIT
SELECT * FROM users LIMIT 10;

-- OFFSET (skip rows)
SELECT * FROM users OFFSET 5;

-- LIMIT with OFFSET (pagination)
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;  -- Page 3 (21-30)

-- FETCH (SQL standard, similar to LIMIT)
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;
SELECT * FROM users OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

-- Practical pagination pattern
-- Page 1
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 0;
-- Page 2
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10;
-- Page 3
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 20;

-- Keyset pagination (more efficient for large offsets)
-- Instead of OFFSET, use WHERE clause
SELECT * FROM orders
WHERE id > 10
ORDER BY id
LIMIT 10;

-- Get total count with LIMIT
SELECT
    (SELECT COUNT(*) FROM orders) AS total_count,
    *
FROM orders
ORDER BY id
LIMIT 10;

---

8.7 CASE Expressions

Add conditional logic to queries.

-- Simple CASE
SELECT
    username,
    CASE status
        WHEN 'completed' THEN 'Done'
        WHEN 'pending' THEN 'In Progress'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_text
FROM orders;

-- Searched CASE
SELECT
    total_amount,
    CASE
        WHEN total_amount < 50 THEN 'Small Order'
        WHEN total_amount >= 50 AND total_amount < 150 THEN 'Medium Order'
        WHEN total_amount >= 150 THEN 'Large Order'
    END AS order_size
FROM orders;

-- CASE in aggregation
SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders;

-- CASE with NULL handling
SELECT
    username,
    COALESCE(email, 'No email provided') AS email
FROM users;

-- NULLIF for division by zero
SELECT
    total_amount,
    order_count,
    CASE
        WHEN order_count > 0 THEN total_amount / order_count
        ELSE 0
    END AS avg_order_value
FROM (
    SELECT
        SUM(total_amount) AS total_amount,
        COUNT(*) AS order_count
    FROM orders
) sub;

---

8.8 JOINs in SELECT

Combine data from multiple tables.

-- Create additional sample tables
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    price DECIMAL(10,2)
);

-- INNER JOIN
SELECT
    o.id AS order_id,
    u.username,
    o.total_amount,
    o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- Equivalent to (INNER is default)
SELECT o.id, u.username
FROM orders o
JOIN users u ON o.user_id = u.id;

-- LEFT JOIN (include all from left table)
SELECT
    u.username,
    o.id AS order_id,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN (include all from right table)
SELECT
    u.username,
    o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN (include all from both)
SELECT
    u.username,
    o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Multiple JOINs
SELECT
    o.id AS order_id,
    u.username,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

---

8.9 Subqueries in SELECT

Use subqueries for complex data retrieval.

-- Subquery in WHERE
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = TRUE
);

-- Subquery in FROM (derived table)
SELECT
    order_count,
    COUNT(*) AS user_count
FROM (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) user_orders
GROUP BY order_count;

-- Subquery in SELECT (scalar subquery)
SELECT
    u.username,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- Correlated subquery
SELECT
    o.*,
    (SELECT AVG(total_amount) FROM orders WHERE user_id = o.user_id) AS avg_order_value
FROM orders o;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Subquery with ALL/ANY
SELECT * FROM orders
WHERE total_amount > ALL (
    SELECT total_amount FROM orders WHERE user_id = 1
);

SELECT * FROM orders
WHERE total_amount > ANY (
    SELECT total_amount FROM orders WHERE user_id = 1
);

---

8.10 Practical Examples

-- E-commerce: Get top spending customers
SELECT
    u.id,
    u.username,
    u.email,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.id) AS order_count,
    AVG(o.total_amount) AS avg_order_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC
LIMIT 10;

-- Get month-over-month revenue growth
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) /
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        2
    ) AS growth_percent
FROM monthly_revenue
ORDER BY month;

-- Find users with no orders
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Alternative using LEFT JOIN
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

---

8.11 Best Practices

                    SELECT Query Best Practices
    ========================================================================

    1. Specify Columns Explicitly
       ┌──────────────────────────────────────────────────────────────┐
       │  SELECT * FROM users;                                       │
       │                                                              │
       │  ❌ Retrieves all columns                                   │
       │  ❌ Returns changeable data (password_hash exposed)        │
       │  ❌ No control over column order                           │
       │                                                              │
       │  SELECT id, username, email FROM users;                     │
       │                                                              │
       │  ✓ Only retrieves needed columns                            │
       │  ✓ Better performance                                       │
       │  ✓ Explicit about data being used                          │
       └──────────────────────────────────────────────────────────────┘

    2. Use Proper Indexing
       ┌──────────────────────────────────────────────────────────────┐
       │  • Index columns used in WHERE clauses                     │
       │  • Index foreign key columns                                │
       │  • Use EXPLAIN to analyze query plans                      │
       └──────────────────────────────────────────────────────────────┘

    3. Avoid SELECT in Loops
       ┌──────────────────────────────────────────────────────────────┐
       │  ❌ N+1 Query Problem:                                      │
       │  FOR user IN (SELECT * FROM users) LOOP                    │
       │    SELECT COUNT(*) FROM orders WHERE user_id = user.id;   │
       │  END LOOP;                                                  │
       │                                                              │
       │  ✓ Use JOIN or subquery instead:                            │
       │  SELECT u.*, COUNT(o.id) FROM users u                       │
       │  LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.*      │
       └──────────────────────────────────────────────────────────────┘

    4. Use Keyset Pagination for Large Tables
       ┌──────────────────────────────────────────────────────────────┐
       │  ❌ OFFSET for deep pagination:                             │
       │  SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000  │
       │                                                              │
       │  ✓ Keyset pagination:                                        │
       │  SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT │
       └──────────────────────────────────────────────────────────────┘

    5. Use EXPLAIN ANALYZE
       ┌──────────────────────────────────────────────────────────────┐
       │  EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;         │
       │                                                              │
       │  • Shows actual execution time                               │
       │  • Reveals sequential scans vs index scans                  │
       │  • Identifies missing indexes                               │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 9: INSERT - Adding Data

---

Last Updated: February 2026