SQL Advice I’d Give My Beginner Self

Tutorial videos feel productive. You’re absorbing information, following along, nodding at the screen. But passive learning doesn’t build SQL skills — writing queries that fail, reading the error message, and fixing them does.

The moment you switch from following someone else’s query to writing your own against unfamiliar data is when the real learning starts. It’s uncomfortable. Do it earlier than feels ready.

SQL clauses are written in one order but executed in a completely different order. This is the source of more beginner confusion than almost anything else — including why you can’t use a column alias in a WHERE clause, or why aggregate functions don’t work where you expect them to.

The execution order is:

-- How SQL actually runs your query (not how you write it)
FROM      -- 1. Which table(s)?
JOIN      -- 2. Combine with other tables
WHERE     -- 3. Filter rows (aliases don't exist yet)
GROUP BY  -- 4. Group the filtered rows
HAVING    -- 5. Filter groups
SELECT    -- 6. Choose columns (aliases created here)
ORDER BY  -- 7. Sort (aliases now available)
LIMIT     -- 8. Restrict rows returned

Once this clicks, most syntax errors start to make sense instead of feeling random.

Most SQL curricula teach subqueries first because they’re simpler to introduce. But CTEs — Common Table Expressions — are almost always the better tool in practice, and they’re easier to read and debug.

A subquery buried inside another query is hard to test in isolation. A CTE is named, sits at the top of your query, and can be referenced like a table. When something breaks, you can test each CTE step independently.

-- Subquery approach (harder to read and debug)
SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 1000;

-- CTE approach (clean, testable, readable)
WITH customer_totals AS (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 1000;

JOINs can silently multiply or drop rows — and if you don’t check, you’ll build analysis on wrong data without knowing it. A many-to-many relationship between two tables will duplicate rows. A poorly specified JOIN condition will drop rows you needed.

Make it a habit: after every JOIN, run a quick COUNT(*) and compare it to what you expected. Does the number make sense? If not, investigate before going further.

-- Check row count before joining
SELECT COUNT(*) FROM orders; -- e.g. returns 5,000

-- Check row count after joining
SELECT COUNT(*)
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- Still 5,000? Good. More? You have duplicates. Less? Rows dropped.

There’s a trap in SQL learning where you keep taking courses, finishing practice problems, and telling yourself you’ll build a project “once you know enough.” That threshold never arrives on its own — you have to set it yourself.

The truth is you know enough to start a project after week three or four. You won’t know everything you need — you’ll have to look things up, get stuck, and push through. That’s not a sign you’re not ready. That’s what doing a real project actually feels like.

A project built with imperfect SQL and honest documentation is worth more in a portfolio than ten course certificates. It shows you can apply what you’ve learned to an open-ended problem — which is exactly what the job requires.

Most beginners read an error message, feel panic, and immediately Google the entire thing. But SQL error messages are usually very precise about what went wrong and where. Learning to read them properly cuts debugging time in half.

Common ones to recognise immediately:

-- "Unknown column 'X' in field list"
-- → You referenced a column that doesn't exist or is misspelled

-- "Invalid use of group function"
-- → You used SUM/COUNT in WHERE. Use HAVING instead.

-- "Every derived table must have its own alias"
-- → Your subquery in FROM is missing an alias after the closing bracket

-- "Column X is ambiguous"
-- → Two joined tables have a column with the same name. Use table.column

Window functions have intimidating syntax. The OVER() clause, PARTITION BY, ROWS BETWEEN — it looks complex before you understand what it’s doing. But the concept is simple: a window function performs a calculation across a set of rows that are related to the current row, without collapsing them into a group.

-- Running total of sales by date, without collapsing rows
SELECT
    order_date,
    order_total,
    SUM(order_total) OVER (
        ORDER BY order_date
    ) AS running_total
FROM orders;

The key insight: GROUP BY collapses rows into one per group. Window functions keep all rows and add a new calculated column alongside them. Once that clicks, PARTITION BY (which is just GROUP BY for window functions) makes immediate sense.