SQL Mastery
Beginner 14 min read SQL Mastery

SQL Query Fundamentals

Read SQL the way the engine does

SELECT, FROM, WHERE, ORDER BY, LIMIT and projections are the backbone of every query you will ever write — and the order they execute in explains most query surprises.

SELECTWHEREORDER BYprojectionexecution order
Before we start

What you will learn

  • Write correct SELECT/WHERE/ORDER BY/LIMIT queries
  • Understand logical execution order (FROM → WHERE → SELECT → ORDER)
  • Avoid the classic scope traps that surprise new SQL writers
Introduction

What is this?

A query requests rows from tables and narrows them: FROM names the table, WHERE filters rows, SELECT picks columns, ORDER BY sorts, LIMIT bounds. The logical order of operations — FROM, then WHERE, then SELECT, then ORDER BY — explains why aliases can't be used in WHERE and why filtering happens before sorting.

Motivation

Why do you need it?

  • SQL is the interface to every warehouse, lakehouse and OLAP engine; it is the lingua franca of data roles.
  • Understanding execution order turns confusing plan behavior into predictable logic.
  • A correctly scoped query is the difference between a correct report and a silently miscounted one.
Think about it

Mental model

Imagine

Grocery shopping as a checklist

Think of

A SQL query

You enter the store (FROM), skip aisles that don't match the list (WHERE), take only the items you need (SELECT), arrange them on the belt by aisle (ORDER BY), and the cashier handles at most 10 items (LIMIT).

Mechanics

How it works

  • FROM picks the source row set (table or subquery/CTE).
  • WHERE tests each row and keeps only matches — no ordering, no aliases from SELECT yet.
  • SELECT computes the output columns and expressions, including aliases.
  • GROUP BY/HAVING (if present) run between WHERE and SELECT for row collapsing.
  • ORDER BY sorts the final result; LIMIT/OFFSET trims it last.

Logical execution order of a SELECT.

FROM
WHERE
GROUP BY / HAVING
SELECT
ORDER BY / LIMIT
Reference

Key commands

SELECT * FROM orders WHERE region = 'EU' ORDER BY ts DESC LIMIT 10;

Latest 10 EU orders.

SELECT DISTINCT region FROM orders;

Unique regions.

SELECT COUNT(*) FROM orders;

Total row count.

Hands-on

Examples

The canonical query anatomy

The canonical query anatomy
SELECT country, COUNT(*) AS orders
FROM sales
WHERE amount > 0
GROUP BY country
ORDER BY orders DESC
LIMIT 5;

Rows are filtered, grouped, counted, then sorted and capped — reading top to bottom mirrors execution only roughly; the engine actually goes FROM → WHERE → GROUP → SELECT → ORDER.

Real world

In production

  • A daily revenue query filters to today's window, groups by channel, and orders before the BI tool paginates.
  • An audit script SELECTs row counts per source with WHERE clauses matching table partitions.
  • A paginated admin export uses ORDER BY id + LIMIT/OFFSET (or keyset) to walk a large table.
Guidelines

Best practices

  • Select explicit columns, not `*`, so renames don't silently break consumers.
  • Filter in WHERE, not after fetching — push predicates to the engine.
  • Write WHERE against raw columns, not SELECT aliases (aliases aren't in scope).
  • Always ORDER BY a deterministic key when paginating.
Watch out

Common mistakes

  • Using a SELECT alias inside WHERE — it isn't defined yet and errors.
  • Assuming WHERE filters before GROUP BY math — that's what HAVING is for.
  • Forgetting that NULL comparisons in WHERE need IS NULL, silently dropping rows.
Root cause analysis

Common issues

Error
Query 'works' but returns zero rows
Root cause

WHERE compared a numeric column to a string that never matches

Fix

Coerce the predicate with CAST and verify a sample row actually satisfies it.

Error
ORDER BY says column doesn't exist
Root cause

Referencing a SELECT alias the engine resolves out of scope

Fix

Repeat the expression in ORDER BY or use a derived table/CTE.

When things break

Debugging tips

  • Simplify: comment out GROUP BY/ORDER pieces until the row set looks right.
  • Check row counts first; a WHERE that matches nothing looks suspiciously like 'empty success'.
  • Re-read the predicate against sample data — a `19.99` string vs `19.99` number mismatch is common.
Interviews

Interview checkpoint

Practice

Hands-on lab

Scaffold a Practice Database and Write Your First Queries
25 min · beginner
In progress
Objectives
  • Create a small schema and seed data
  • Write filtered, ordered, bounded queries
  • Prove execution order with a WHERE-alias trap

Scaffold a Practice Database and Write Your First Queries

25 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which clause executes before SELECT in the logical order?

2

A SELECT alias can be referenced inside the WHERE clause of the same query.

Go deeper

Official documentation

PostgreSQL SELECT docs
docs
SQLite SELECT docs
docs