10 Simple Steps to Fully Understand SQL

Many programmers view SQL as a daunting beast, but SQL is one of the few declarative languages, operating very differently from procedural, object‑oriented, or functional languages.

The article is written for three audiences: (1) people who use SQL at work but don’t fully understand it, (2) developers who are proficient with SQL but lack knowledge of its logical syntax, and (3) anyone who wants to teach SQL to others.

10 Simple Steps to Fully Understand SQL

1. SQL is a declarative language

SQL declares the result you want from the underlying data instead of describing how to obtain it. The database engine decides the best way to produce the result set.

SELECT first_name, last_name FROM employees WHERE salary > 100000

This query simply asks for the names of employees earning more than 100 000; it does not care where the rows come from.

2. SQL syntax order differs from execution order

The syntactic order of a SELECT statement is:

SELECT [DISTINCT]

FROM

WHERE

GROUP BY

HAVING

UNION

ORDER BY

However, the engine executes the clauses in this order:

FROM

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

UNION

ORDER BY

Key points:

FROM is the first step – the database loads data from disk into memory.

SELECT is evaluated after FROM and GROUP BY, which is why column aliases defined in SELECT cannot be used in WHERE.

SELECT A.x + A.y AS z FROM A WHERE z = 10 -- z cannot be used here because SELECT runs later

To reuse an alias you must either repeat the expression or use a derived table, common table expression, or view.

SELECT A.x + A.y AS z FROM A WHERE (A.x + A.y) = 10

3. The core of SQL is table references

The real focus of a query is the tables it references, not the individual columns. The FROM clause produces a combined (Cartesian) table of all referenced tables.

<from clause> ::= FROM <table reference> [ { <comma> <table reference> } ... ]

Example:

FROM a, b

produces a Cartesian product of tables a and b .

4. Flexible table references make queries powerful

JOINs are special table references. The SQL standard defines table references as:

<table reference> ::= <table name> | <derived table> | <joined table>

Example of a join expressed with a comma:

FROM a1 JOIN a2 ON a1.id = a2.id, b

Although uncommon, it demonstrates that the final output includes columns from all three tables.

5. Prefer explicit JOIN syntax

Using commas for joins reduces readability and can cause errors. Explicit JOINs make the relationship clear and allow you to specify inner, left, right, or full outer joins.

FROM a, b, c, d, e, f, g, h WHERE a.a1 = b.bx AND a.a2 = c.c1 AND d.d1 = b.bc

Using JOINs avoids such confusion.

6. Types of joins

EQUI JOIN (INNER JOIN or OUTER JOIN)

SEMI JOIN (implemented with IN or EXISTS)

ANTI JOIN (implemented with NOT IN or NOT EXISTS)

CROSS JOIN (Cartesian product)

DIVISION (advanced relational operation)

Examples:

-- INNER JOIN author JOIN book ON author.id = book.author_id -- LEFT OUTER JOIN author LEFT OUTER JOIN book ON author.id = book.author_id

-- SEMI JOIN using IN FROM author WHERE author.id IN (SELECT book.author_id FROM book) -- SEMI JOIN using EXISTS FROM author WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)

-- ANTI JOIN using NOT IN FROM author WHERE author.id NOT IN (SELECT book.author_id FROM book)

-- CROSS JOIN author CROSS JOIN book

7. Derived tables (subqueries) act like variables

FROM (SELECT * FROM author) a

Derived tables can be reused with common table expressions (CTEs):

WITH a AS ( SELECT first_name, last_name, CURRENT_DATE - date_of_birth AS age FROM author ) SELECT * FROM a WHERE age > 10000

8. GROUP BY operates on table references

Applying GROUP BY to a FROM clause creates a new table reference that aggregates rows.

SELECT A.x, A.y, SUM(A.z) FROM A GROUP BY A.x, A.y

Note that MySQL’s lax handling of GROUP BY can be confusing; stick to the standard.

9. SELECT is a projection (mapping) of relations

SELECT maps source tables to a result set, applying expressions, aggregates, and window functions. Important rules include:

You may only select columns that come from table references.

If GROUP BY is present, you may only use grouped columns or aggregates.

Without GROUP BY, window functions can replace aggregates.

You cannot mix ordinary functions with aggregates in a non‑grouped SELECT.

10. Simple keywords: DISTINCT, UNION, ORDER BY, OFFSET

DISTINCT removes duplicate rows after projection.

UNION combines two result sets and removes duplicates; UNION ALL keeps all rows.

EXCEPT removes rows of the second query from the first.

INTERSECT keeps rows present in both queries.

Ordering is performed last with ORDER BY and optional OFFSET/FETCH (or LIMIT/OFFSET depending on the database).

Practising these ten steps will deepen your understanding of SQL and help you avoid common pitfalls.