SQL SELECT Statement

SELECT is the most frequently used SQL statement. It retrieves data from one or more tables, and is the entry point for nearly every analytical query, report, and application query you’ll write.

Basic Syntax

SELECT column1, column2
FROM table_name;

Select all columns:

SELECT * FROM products;

Using SELECT * is convenient for exploration but avoid it in production queries — it returns all columns even if you only need a few, wastes bandwidth, and breaks if table structure changes.

Selecting Specific Columns

SELECT product_id, name, price
FROM products;

Column order in SELECT determines output column order, not storage order.

Column Aliases (AS)

Rename columns in the result set using AS:

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

AS is optional — price unit_price works too — but AS makes it explicit and easier to read.

Expressions and Calculations

You can compute values directly in SELECT:

SELECT
    order_id,
    quantity,
    unit_price,
    quantity * unit_price AS line_total,
    quantity * unit_price * 0.1 AS tax_estimate,
    ROUND(quantity * unit_price * 1.1, 2) AS total_with_tax
FROM order_items;

Common SQL expression types:

Arithmetic: +, -, *, /, %
String: || (concatenation in PostgreSQL), CONCAT(), UPPER(), LOWER(), TRIM()
Date: EXTRACT(), DATE_TRUNC(), AGE(), NOW()
Conditional: CASE WHEN ... END
Null handling: COALESCE(), NULLIF()

String Operations

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(email) AS email_upper,
    LENGTH(description) AS desc_length,
    LEFT(description, 100) AS description_preview,
    TRIM(BOTH ' ' FROM notes) AS cleaned_notes
FROM customers;

Conditional Column Values with CASE WHEN

SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 1000 THEN 'High Value'
        WHEN amount >= 100  THEN 'Medium Value'
        ELSE 'Low Value'
    END AS order_tier,
    CASE status
        WHEN 'completed' THEN 'Done'
        WHEN 'cancelled' THEN 'Void'
        ELSE 'Pending'
    END AS status_label
FROM orders;

DISTINCT: Remove Duplicate Rows

DISTINCT eliminates duplicate rows from results:

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

-- Unique combinations of city AND country
SELECT DISTINCT city, country FROM customers ORDER BY country, city;

DISTINCT applies to the entire row — if any column differs, the row is considered unique.

SELECT with WHERE

Add a WHERE clause to filter rows:

SELECT product_id, name, price
FROM products
WHERE category = 'electronics'
  AND price < 500
  AND is_active = TRUE
ORDER BY price ASC;

SELECT with ORDER BY

Sort results with ORDER BY:

SELECT name, price, category
FROM products
ORDER BY
    category ASC,    -- primary sort: category A → Z
    price DESC;      -- secondary sort: most expensive first within each category

NULL Handling in SELECT

NULL needs special treatment:

SELECT
    name,
    phone,
    COALESCE(phone, 'No phone on file') AS phone_display,
    CASE WHEN phone IS NULL THEN 'Missing' ELSE 'Present' END AS phone_status
FROM customers;

COALESCE(a, b, c) returns the first non-null value in the list.

Computed Columns with Aggregate Functions

Aggregates in SELECT require GROUP BY for all non-aggregate columns:

SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    SUM(stock_quantity) AS total_stock
FROM products
GROUP BY category
ORDER BY product_count DESC;

SELECT with Subquery in Column List

Scalar subqueries in the column list execute once per row:

SELECT
    customer_id,
    name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS total_orders,
    (
        SELECT SUM(amount)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS lifetime_value
FROM customers c;

For large tables, this is usually slower than a JOIN + GROUP BY. Use it for readability in small queries or when the subquery is non-trivial to express as a join.

SELECT with Window Functions

Window functions compute values across rows without collapsing them:

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    RANK() OVER (ORDER BY amount DESC) AS amount_rank,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS customer_order_number
FROM orders;

SELECT INTO (Create a New Table from a Query)

-- PostgreSQL / SQL Server: create a new table from query results
SELECT *
INTO products_backup
FROM products
WHERE is_active = TRUE;

-- MySQL / PostgreSQL (CREATE TABLE AS SELECT)
CREATE TABLE high_value_customers AS
SELECT customer_id, name, email
FROM customers
WHERE lifetime_value > 1000;

Common Patterns

Select with a computed rank:

SELECT
    name,
    price,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products
LIMIT 10;

Pivot-style reporting with CASE WHEN:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count
FROM orders
GROUP BY 1
ORDER BY 1;