The SQL SELECT Statement

SELECT retrieves data from one or more tables. It’s the most-used statement in SQL — every read operation, every report, every analytical query uses it. Understanding SELECT deeply is what separates someone who can follow a tutorial from someone who can write queries for real problems.

Basic Syntax

SELECT column1, column2
FROM table_name;

Select everything:

SELECT * FROM products;

Use SELECT * for exploration, not production queries — it returns unnecessary columns and breaks if schema changes.

Selecting Specific Columns

SELECT product_id, name, price
FROM products;

You control which columns appear and in what order.

Column Aliases

Rename output columns with AS:

SELECT
    product_id AS id,
    name AS product_name,
    price AS unit_price,
    price * 1.2 AS price_with_markup
FROM products;

Expressions in SELECT

Compute values on the fly:

SELECT
    first_name || ' ' || last_name AS full_name,   -- string concat (PostgreSQL)
    UPPER(email) AS email_upper,
    EXTRACT(YEAR FROM created_at) AS signup_year,
    amount * 1.1 AS amount_with_tax,
    ROUND(price, 0) AS rounded_price
FROM customers;

CASE WHEN: Conditional Values

SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 500 THEN 'Large'
        WHEN amount >= 100 THEN 'Medium'
        ELSE 'Small'
    END AS order_size,
    CASE status
        WHEN 'completed' THEN 'Fulfilled'
        WHEN 'cancelled' THEN 'Void'
        ELSE 'In Progress'
    END AS status_label
FROM orders;

DISTINCT: Unique Values Only

-- All distinct cities where customers are located
SELECT DISTINCT city FROM customers ORDER BY city;

-- Distinct city + country combinations
SELECT DISTINCT city, country FROM customers ORDER BY country, city;

DISTINCT applies to the whole output row, not just the first column.

Aggregate Functions

SELECT
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
WHERE status = 'completed';

GROUP BY with SELECT

Aggregate functions in SELECT require all non-aggregate columns to appear in GROUP BY:

SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

Window Functions in SELECT

Window functions compute values across rows without collapsing them into groups:

SELECT
    employee_id,
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

The key difference from GROUP BY: you still get one row per employee — the window function adds a computed column to each row.

Subquery in SELECT Column

A scalar subquery inside SELECT computes a value per row:

SELECT
    customer_id,
    name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
    (SELECT MAX(amount) FROM orders o WHERE o.customer_id = c.customer_id) AS max_order
FROM customers c
ORDER BY order_count DESC;

For large tables, this is usually slower than a JOIN + GROUP BY. Use it when the subquery logic is difficult to express as a join.

SELECT with NULL Handling

SELECT
    name,
    COALESCE(phone, 'No phone') AS phone_display,
    NULLIF(discount, 0) AS discount_or_null,
    CASE WHEN email IS NULL THEN 'Missing' ELSE 'Present' END AS email_status
FROM customers;

COALESCE(a, b): returns first non-null value
NULLIF(a, b): returns NULL if a = b, otherwise returns a

Practical Query Patterns

Pivot-style with CASE WHEN:

SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue
FROM orders
GROUP BY 1
ORDER BY 1 DESC;

Top N per group:

SELECT product_id, name, category, sales_count
FROM (
    SELECT
        product_id,
        name,
        category,
        sales_count,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_count DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;