10 Essential SQL Queries Every Developer Should Know (With Examples)

Whether you're building web applications, analyzing data, or preparing for technical interviews, mastering SQL queries is non-negotiable. After years of database development, I've identified the 10 query patterns that solve 90% of real-world data challenges.

These aren't just queries to memorize - they're patterns you'll use daily in production applications.

1. SELECT with WHERE - Filtering Data

The foundation of all SQL queries. Master filtering to retrieve exactly what you need:

-- Basic filtering
SELECT first_name, last_name, email
FROM users
WHERE status = 'active'
  AND created_at >= '2026-01-01'
  AND email LIKE '%@company.com';

-- Multiple conditions with OR
SELECT product_name, price, category
FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price BETWEEN 100 AND 1000
  AND stock_quantity > 0;

Pro Tips:

Use AND for conditions that must all be true
Use OR when any condition satisfies your requirements
BETWEEN is inclusive on both ends
LIKE with % is powerful but can be slow on large datasets

Performance note: LIKE '%value' (leading wildcard) cannot use an index. LIKE 'value%' can. For text search on large tables, use full-text search (tsvector/tsquery in PostgreSQL) rather than LIKE.

2. JOIN - Combining Related Data

JOINs are where SQL truly shines. Understanding joins is crucial for working with normalized databases:

-- INNER JOIN: Only matching records
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';

-- LEFT JOIN: All records from left table
SELECT
    c.customer_name,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

When to Use Each JOIN:

INNER JOIN: Both tables must have matching records
LEFT JOIN: Keep all records from left table, even without matches
RIGHT JOIN: Keep all records from right table (rarely used)
FULL OUTER JOIN: Keep all records from both tables

Performance note: JOIN performance depends on indexes on the ON clause columns. An unindexed JOIN on a 10M-row table can take minutes; with an index, milliseconds. Run EXPLAIN ANALYZE to see whether the optimizer is doing a sequential scan or an index scan.

3. GROUP BY with Aggregations - Summarizing Data

Aggregations turn raw data into insights:

-- Sales summary by category
SELECT
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    SUM(stock_quantity * price) as total_inventory_value
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY total_inventory_value DESC;

Key Aggregation Functions:

COUNT(*): Total rows
COUNT(column): Non-NULL values
SUM(column): Total of numeric values
AVG(column): Average value
MIN/MAX(column): Minimum/maximum values

Remember:

WHERE filters rows BEFORE grouping
HAVING filters groups AFTER aggregation
All non-aggregated columns must be in GROUP BY

Performance note: GROUP BY on unindexed columns forces a full table scan. For reporting queries that run frequently, a partial index or a materialized view can cut execution time significantly.

4. Subqueries - Queries Within Queries

Subqueries enable complex logic by nesting queries:

-- Find customers who spent more than average
SELECT
    customer_id,
    customer_name,
    total_spent
FROM (
    SELECT
        c.customer_id,
        c.customer_name,
        SUM(o.total_amount) as total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
) customer_totals
WHERE total_spent > (
    SELECT AVG(order_total)
    FROM (
        SELECT SUM(total_amount) as order_total
        FROM orders
        GROUP BY customer_id
    ) averages
);

-- Using IN with subquery
SELECT product_name, price
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE category_name IN ('Electronics', 'Computers')
);

Subquery Types:

Scalar subquery: Returns single value
Column subquery: Returns single column (use with IN)
Row subquery: Returns single row
Table subquery: Returns full result set (in FROM clause)

5. Window Functions - Advanced Analytics

Window functions perform calculations across related rows without grouping:

-- Running total and rankings
SELECT
    order_date,
    customer_id,
    total_amount,
    -- Running total per customer
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) as running_total,
    -- Rank orders by amount
    RANK() OVER (
        ORDER BY total_amount DESC
    ) as amount_rank,
    -- Previous order amount
    LAG(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) as previous_order_amount
FROM orders;

Common Window Functions:

ROW_NUMBER(): Unique sequential number
RANK(): Rank with gaps for ties
DENSE_RANK(): Rank without gaps
LAG/LEAD(): Access previous/next row
FIRST_VALUE/LAST_VALUE(): First/last value in partition

6. CASE Statements - Conditional Logic

Add if-then logic directly in your queries:

-- Customer segmentation
SELECT
    customer_id,
    customer_name,
    total_orders,
    total_spent,
    CASE
        WHEN total_spent >= 10000 THEN 'VIP'
        WHEN total_spent >= 5000 THEN 'Premium'
        WHEN total_spent >= 1000 THEN 'Regular'
        ELSE 'New'
    END as customer_segment,
    CASE
        WHEN total_orders >= 50 THEN 'Frequent Buyer'
        WHEN total_orders >= 20 THEN 'Regular Buyer'
        WHEN total_orders >= 5 THEN 'Occasional Buyer'
        ELSE 'Rare Buyer'
    END as purchase_frequency
FROM customer_summary;

-- Conditional aggregation
SELECT
    product_category,
    COUNT(*) as total_products,
    SUM(CASE WHEN price > 1000 THEN 1 ELSE 0 END) as premium_products,
    SUM(CASE WHEN stock_quantity = 0 THEN 1 ELSE 0 END) as out_of_stock
FROM products
GROUP BY product_category;

7. CTEs (Common Table Expressions) - Readable Complex Queries

CTEs make complex queries maintainable and readable:

-- Multi-step analysis with CTEs
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        customer_id,
        SUM(total_amount) as monthly_total
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY 1, 2
),
customer_stats AS (
    SELECT
        customer_id,
        COUNT(DISTINCT month) as active_months,
        AVG(monthly_total) as avg_monthly_spend,
        MAX(monthly_total) as peak_month_spend
    FROM monthly_sales
    GROUP BY customer_id
)
SELECT
    c.customer_name,
    cs.active_months,
    cs.avg_monthly_spend,
    cs.peak_month_spend,
    CASE
        WHEN cs.active_months >= 10 THEN 'Highly Engaged'
        WHEN cs.active_months >= 6 THEN 'Regular'
        ELSE 'Inactive Risk'
    END as engagement_level
FROM customer_stats cs
JOIN customers c ON cs.customer_id = c.customer_id
WHERE cs.avg_monthly_spend > 100
ORDER BY cs.avg_monthly_spend DESC;

Why Use CTEs:

Improves query readability
Easier to debug step-by-step
Can be recursive (for hierarchical data)
Better than nested subqueries for complex logic

8. UNION and UNION ALL - Combining Results

Combine results from multiple queries:

-- Combine data from multiple tables
SELECT
    'Customer' as user_type,
    customer_id as user_id,
    email,
    created_at
FROM customers
WHERE status = 'active'

UNION ALL

SELECT
    'Employee' as user_type,
    employee_id as user_id,
    work_email as email,
    hire_date as created_at
FROM employees
WHERE status = 'active';

UNION vs UNION ALL:

UNION: Removes duplicates (slower)
UNION ALL: Keeps all rows (faster, use when no duplicates expected)

9. EXISTS and NOT EXISTS - Efficient Filtering

Check for existence without retrieving full data:

-- Customers with at least one order
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- Products never ordered
SELECT product_id, product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

EXISTS vs IN:

EXISTS stops searching after first match (faster for large datasets)
IN retrieves complete list (faster for small subqueries)
EXISTS handles NULL values better

10. INSERT, UPDATE, DELETE - Data Manipulation

Writing data is as important as reading it:

-- INSERT with multiple rows
INSERT INTO customers (customer_name, email, status)
VALUES
    ('John Doe', 'john@example.com', 'active'),
    ('Jane Smith', 'jane@example.com', 'active'),
    ('Bob Johnson', 'bob@example.com', 'pending');

-- INSERT from SELECT
INSERT INTO archived_orders
SELECT * FROM orders
WHERE order_date < '2023-01-01';

-- UPDATE with JOIN
UPDATE products p
SET stock_quantity = stock_quantity - oi.quantity
FROM order_items oi
WHERE p.product_id = oi.product_id
  AND oi.order_id = 12345;

-- DELETE with subquery
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '2 years'
)
AND status = 'inactive';

Data Manipulation Best Practices:

Always test with SELECT first: Convert your DELETE/UPDATE to SELECT to preview affected rows
Use transactions for multiple changes: Wrap in BEGIN/COMMIT for atomicity
Add WHERE clauses: Never forget WHERE in UPDATE/DELETE!
Consider performance: Batch large operations
Backup before bulk changes: Safety first

Putting It All Together

Here's a real-world example combining multiple concepts:

-- Comprehensive customer analysis
WITH customer_orders AS (
    SELECT
        c.customer_id,
        c.customer_name,
        c.email,
        COUNT(o.order_id) as total_orders,
        SUM(o.total_amount) as lifetime_value,
        MAX(o.order_date) as last_order_date,
        MIN(o.order_date) as first_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name, c.email
),
customer_segments AS (
    SELECT
        *,
        CASE
            WHEN lifetime_value >= 10000 THEN 'VIP'
            WHEN lifetime_value >= 5000 THEN 'Premium'
            WHEN lifetime_value >= 1000 THEN 'Standard'
            ELSE 'Basic'
        END as segment,
        CASE
            WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
            WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'
            ELSE 'Churned'
        END as status
    FROM customer_orders
)
SELECT
    segment,
    status,
    COUNT(*) as customer_count,
    AVG(total_orders) as avg_orders,
    AVG(lifetime_value) as avg_ltv,
    SUM(lifetime_value) as total_revenue
FROM customer_segments
GROUP BY segment, status
ORDER BY
    CASE segment
        WHEN 'VIP' THEN 1
        WHEN 'Premium' THEN 2
        WHEN 'Standard' THEN 3
        ELSE 4
    END,
    customer_count DESC;

Most Frequently Asked SQL Queries in Technical Interviews

Know all 10 patterns above — then drill these specifically. These are the query types that appear in the majority of data and backend engineering interviews.

Deduplication: Keep One Row Per Group

-- Keep the most recent order per customer
SELECT customer_id, order_id, order_date, total_amount
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

Why it comes up: Real databases have duplicates. Interviewers want ROW_NUMBER, not a workaround with MAX and a self-join. This is one of the most commonly tested patterns at every level.

Find Records Missing From Another Table

-- Customers who have never placed an order
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Why it comes up: "Find records that don't exist in table B" is one of the most-asked SQL patterns. The correct answer is LEFT JOIN + IS NULL (or NOT EXISTS). Using NOT IN with a subquery that could return NULLs is a silent trap: NOT IN (SELECT ... WHERE col IS NULL) always returns zero rows.

Percentage of Total

-- Each product's share of total sales
SELECT
    product_name,
    sales,
    ROUND(sales * 100.0 / SUM(sales) OVER (), 2) AS pct_of_total
FROM product_sales
ORDER BY sales DESC;

Why it comes up: Tests whether you know to use a window function for relative share rather than a self-join or correlated subquery. SUM(...) OVER () with no PARTITION BY gives the grand total across all rows.

Period-Over-Period Comparison

-- Month-over-month revenue change per product
WITH monthly AS (
    SELECT
        product_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY 1, 2
)
SELECT
    product_id,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month))
        / NULLIF(LAG(revenue) OVER (PARTITION BY product_id ORDER BY month), 0) * 100,
        2
    ) AS pct_change
FROM monthly;

Why it comes up: Period-over-period analysis appears in almost every analytics interview. Interviewers want LAG, not a self-join. Always handle NULL with NULLIF to avoid division-by-zero on the first period.

Self-Join for Hierarchical Data

-- Each employee with their manager's name
SELECT
    e.employee_name,
    e.department,
    m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Why it comes up: Self-joins test whether you understand that a table can appear on both sides of a JOIN with different aliases. Common for org charts, category trees, and parent-child relationships.

Practice Makes Perfect

To truly master these queries:

Practice daily: Write queries for your own projects
Use real data: Work with actual databases, not just examples
Explain to others: Teaching reinforces understanding
Read execution plans: Understand query performance
Challenge yourself: Solve problems on LeetCode SQL, HackerRank

Next Steps

Want to go deeper? Check out:

SQL Crash Course - 270+ pages with 350+ examples
Mastering SQL Window Functions - Advanced analytics
SQL Interview Questions - Ace your next interview

Which of these queries do you use most often? Have questions about specific SQL patterns? Get in touch or share your experience in the comments!