The Complete Guide to SQL SELECT Statements: From Basics to Advanced

If there’s one SQL statement I’ve written more than any other, it’s SELECT. It’s the workhorse of every database interaction I’ve ever built, from the simplest “give me all the rows” query to complex analytical reports involving multiple joins, subqueries, and window functions. In this guide, I want to take you from the absolute basics of SELECT all the way to some of its more advanced capabilities, using SQLite examples throughout since it’s the engine I reach for most often when experimenting.

The Absolute Basics

At its simplest, SELECT retrieves data from a table.

SELECT * FROM users;

The * means “give me every column.” In practice, I almost always avoid * in real application code and instead name the columns I actually need:

SELECT id, username, email FROM users;

This matters more than it might seem — explicit column lists are more resilient to schema changes and make queries self-documenting.

Filtering Rows With WHERE

The WHERE clause filters which rows are returned.

SELECT id, username FROM users WHERE username = 'ahmad';

You can combine conditions with AND, OR, and NOT:

SELECT * FROM users
WHERE created_at > '2024-01-01' AND username LIKE 'a%';

Sorting With ORDER BY

SELECT id, username, created_at
FROM users
ORDER BY created_at DESC;

You can sort by multiple columns, with different directions for each:

SELECT * FROM products
ORDER BY category ASC, price DESC;

Limiting Results

SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

Combined with OFFSET, this becomes the basis of pagination:

SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

Aliasing Columns and Tables

SELECT username AS name, email AS contact
FROM users AS u;

Aliases make output more readable and are essential once you start joining multiple tables with overlapping column names.

Aggregation Functions

SQL becomes genuinely powerful once you start summarizing data instead of just listing it row by row.

SELECT COUNT(*) FROM orders;
SELECT AVG(price) FROM products;
SELECT MAX(price), MIN(price) FROM products;
SELECT SUM(quantity) FROM order_items;

Grouping With GROUP BY

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

This groups rows by category and computes aggregates within each group.

Filtering Groups With HAVING

WHERE filters individual rows before grouping; HAVING filters groups after aggregation.

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

I mixed these up constantly when I was learning SQL — a good rule of thumb I use now: if the condition involves an aggregate function like AVG() or COUNT(), it belongs in HAVING, not WHERE.

Joining Tables

Joins are where SELECT really starts to shine, letting you combine related data across multiple tables.

INNER JOIN

Returns only rows that have matches in both tables.

SELECT orders.id, users.username, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;

LEFT JOIN

Returns all rows from the left table, with matching rows from the right table where they exist, and NULL where they don’t.

SELECT users.username, orders.id AS order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

This is one of my most-used join types — it’s perfect for questions like “show me every user, along with any orders they’ve placed, including users who haven’t ordered anything yet.”

Multiple Joins

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

Subqueries

A subquery is a SELECT nested inside another statement.

SELECT username
FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 500
);

Subqueries can also appear in the SELECT list itself:

SELECT username,
       (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

Common Table Expressions (CTEs)

CTEs, defined with WITH, let me break complex queries into readable, named building blocks instead of deeply nested subqueries.

WITH high_value_orders AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 1000
)
SELECT users.username, high_value_orders.total_spent
FROM high_value_orders
JOIN users ON users.id = high_value_orders.user_id;

Recursive CTEs

SQLite also supports recursive CTEs, which are genuinely useful for hierarchical data like category trees or organizational charts.

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Window Functions

Window functions let you compute values across a set of rows related to the current row, without collapsing the result into a single grouped row the way GROUP BY does.

SELECT username, total,
       RANK() OVER (ORDER BY total DESC) AS spending_rank
FROM orders
JOIN users ON users.id = orders.user_id;

Another common pattern — running totals:

SELECT id, total,
       SUM(total) OVER (ORDER BY id) AS running_total
FROM orders;

Window functions with PARTITION BY let you compute values within subgroups while still returning every individual row:

SELECT category, name, price,
       AVG(price) OVER (PARTITION BY category) AS avg_category_price
FROM products;

CASE Expressions

CASE lets me add conditional logic directly inside a SELECT.

SELECT username,
       CASE
           WHEN total > 1000 THEN 'VIP'
           WHEN total > 100 THEN 'Regular'
           ELSE 'New'
       END AS customer_tier
FROM orders
JOIN users ON users.id = orders.user_id;

Set Operations: UNION, INTERSECT, EXCEPT

SELECT username FROM users WHERE username LIKE 'a%'
UNION
SELECT username FROM archived_users WHERE username LIKE 'a%';

UNION removes duplicates by default; UNION ALL keeps them and is faster since it skips the deduplication step.

SELECT product_id FROM current_inventory
INTERSECT
SELECT product_id FROM discontinued_list;

DISTINCT

SELECT DISTINCT category FROM products;

Useful for quickly getting a unique list of values from a column, though I’m careful with it on large tables since it requires sorting or hashing under the hood.

Best Practices

• Avoid SELECT * in application code — always list the columns you actually need.
• Index columns used in WHERE, JOIN, and ORDER BY clauses for meaningfully better performance on larger tables.
• Prefer CTEs over deeply nested subqueries for readability, especially in complex reporting queries.
• Use EXPLAIN QUERY PLAN in SQLite to understand how a query is being executed before optimizing it blindly.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 5;

• Be deliberate about INNER JOIN versus LEFT JOIN — accidentally using an inner join when you needed a left join is one of the most common bugs I’ve seen (and made myself).
• Use UNION ALL instead of UNION whenever you know duplicates aren’t possible or don’t matter, for better performance.

Frequently Asked Questions

What’s the difference between WHERE and HAVING? WHERE filters individual rows before any grouping happens; HAVING filters aggregated groups after GROUP BY has already run.

When should I use a CTE instead of a subquery? Whenever a nested subquery starts becoming hard to read, or you need to reference the same derived result multiple times in one query — CTEs improve clarity in both cases.

Are window functions supported in SQLite? Yes, since SQLite version 3.25, window functions like RANK(), ROW_NUMBER(), and SUM() OVER() are fully supported.

How do I get the top N rows per group? A common pattern is combining ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) inside a CTE, then filtering on that row number in the outer query.

Is SELECT * bad practice? In application code, generally yes — it can silently break things when the schema changes and it retrieves data you don’t actually need, hurting both performance and maintainability.

Wrapping Up

SELECT looks deceptively simple at first glance, but it’s genuinely one of the deepest, most expressive parts of the entire SQL language. Every time I think I’ve seen everything it can do, I find another combination of clauses that solves a problem more elegantly than I expected. If you take the time to really internalize joins, subqueries, CTEs, and window functions, you’ll find that the vast majority of real-world data problems can be solved with a single well-constructed SELECT statement — no application-side processing required.