SQL Data Query Language (DQL)

DQL is the subset of SQL used to retrieve data from a database. It consists of one statement: SELECT. Everything from simple column retrieval to complex multi-table analytical queries uses SELECT.

DQL is read-only — it never modifies data.

The SELECT Statement

At its simplest, SELECT retrieves columns from a table:

SELECT column1, column2
FROM table_name;

A fully featured SELECT can include filtering, sorting, grouping, joining, and windowing:

SELECT
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
  AND o.created_at >= '2025-01-01'
GROUP BY c.customer_id, c.name
HAVING SUM(o.amount) > 500
ORDER BY total_spent DESC
LIMIT 20;

SQL Clause Execution Order

SQL clauses are written in this order:

SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

But they are executed in a different order:

1. FROM / JOIN   — identify and join source tables
2. WHERE         — filter rows
3. GROUP BY      — group rows into aggregates
4. HAVING        — filter groups
5. SELECT        — compute output columns
6. DISTINCT      — remove duplicates
7. ORDER BY      — sort
8. LIMIT / OFFSET — restrict row count

Understanding execution order explains why you can’t use a SELECT alias in WHERE (WHERE runs before SELECT computes aliases), but can reference it in ORDER BY (ORDER BY runs after SELECT).

Column Selection and Expressions

-- Select all columns (use only for exploration)
SELECT * FROM products;

-- Select specific columns
SELECT product_id, name, price FROM products;

-- Compute expressions
SELECT
    name,
    price,
    price * 0.1 AS tax,
    price * 1.1 AS price_with_tax
FROM products;

-- String expressions
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(email) AS email_upper,
    LENGTH(bio) AS bio_length
FROM users;

-- Conditional values
SELECT
    name,
    price,
    CASE
        WHEN price >= 100 THEN 'Expensive'
        WHEN price >= 50  THEN 'Mid-range'
        ELSE 'Budget'
    END AS price_tier
FROM products;

Filtering with WHERE

SELECT name, price FROM products
WHERE category = 'electronics'
  AND price < 300
  AND is_active = TRUE;

-- Pattern matching
WHERE name LIKE 'Pro%'
WHERE email ILIKE '%@gmail.com'  -- PostgreSQL: case-insensitive

-- Range
WHERE price BETWEEN 50 AND 200

-- List
WHERE status IN ('pending', 'processing', 'shipped')

-- NULL check
WHERE phone IS NULL
WHERE discount IS NOT NULL

Joining Tables

-- INNER JOIN: rows in both tables
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- LEFT JOIN: all rows from left, NULLs where no right match
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Multiple joins
SELECT c.name, p.name AS product, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Grouping and Aggregation

-- GROUP BY: one result row per group
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    SUM(stock) AS total_stock
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- HAVING: filter groups (not individual rows)
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

Aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX(), STRING_AGG(), ARRAY_AGG()

Subqueries in DQL

-- Scalar subquery in SELECT
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS overall_avg
FROM products;

-- Subquery in WHERE (IN)
SELECT name FROM products
WHERE category_id IN (
    SELECT category_id FROM categories WHERE is_featured = TRUE
);

-- Derived table in FROM
SELECT * FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
) t
WHERE total > 1000;

Common Table Expressions (CTEs)

CTEs name intermediate result sets for readability and reuse:

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
),
ranked_months AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_totals
)
SELECT
    month,
    revenue,
    revenue - prev_revenue AS change,
    ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS pct_change
FROM ranked_months
ORDER BY month DESC;

Window Functions

Window functions compute a value across a set of rows without collapsing them — you keep all original rows plus the computed value:

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank,
    SUM(salary) OVER (ORDER BY hire_date) AS running_payroll
FROM employees;

Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTILE(), SUM() OVER, AVG() OVER, COUNT() OVER

DISTINCT: Remove Duplicate Rows

-- All unique city values
SELECT DISTINCT city FROM customers;

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

UNION: Combine Multiple Queries

-- Stack rows from two queries (removes duplicates)
SELECT name, email FROM customers
UNION
SELECT company_name, contact_email FROM suppliers;

-- UNION ALL: keeps all rows including duplicates (faster)
SELECT product_id FROM featured_products
UNION ALL
SELECT product_id FROM promoted_products;

Practical DQL Query Template

SELECT
    t1.column_a,
    t2.column_b,
    SUM(t1.amount) AS total,
    COUNT(DISTINCT t1.id) AS unique_count
FROM table1 t1
JOIN table2 t2 ON t1.fk = t2.pk
WHERE t1.status = 'active'
  AND t1.created_at >= '2025-01-01'
GROUP BY t1.column_a, t2.column_b
HAVING SUM(t1.amount) > 100
ORDER BY total DESC
LIMIT 50;