SQL Query Fundamentals
1. Relational Thinking
Relational databases organize information into tables connected by keys. Effective SQL begins by
understanding the entities represented by those tables and the relationships between them. A query
should express the data question clearly before optimization begins.
Key takeaway: Relational databases organize information into tables connected by keys.
SQL Query Fundamentals
2. Selecting Data
SELECT chooses columns and expressions from a data source. Explicit column lists are preferable to
SELECT star in production queries because they document intent and reduce unnecessary data
transfer. Aliases can make calculated fields and joined tables easier to read.
Key takeaway: SELECT chooses columns and expressions from a data source.
SQL Query Fundamentals
3. Filtering Rows
WHERE restricts rows before later query stages. Conditions can compare values, test ranges, match
sets, and handle nulls. Because null represents missing or unknown information, it requires IS NULL
or IS NOT NULL rather than ordinary equality comparisons.
Key takeaway: WHERE restricts rows before later query stages.
SQL Query Fundamentals
4. Joining Tables
Joins combine related rows. INNER JOIN keeps matching rows, while LEFT JOIN preserves all rows
from the left side even when no match exists. Join conditions should use the correct keys; accidental
many-to-many joins can multiply rows and produce misleading totals.
Key takeaway: Joins combine related rows.
SQL Query Fundamentals
5. Aggregation
Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize groups of rows.
GROUP BY defines those groups, while HAVING filters after aggregation. Understanding the
difference between WHERE and HAVING prevents both syntax mistakes and incorrect results.
Key takeaway: Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize groups
of rows.
SQL Query Fundamentals
6. Ordering and Limits
ORDER BY creates deterministic presentation when the ordering columns uniquely establish
sequence. LIMIT or equivalent syntax can reduce result size, but a limit without an order may return
an arbitrary subset. Stable ordering is especially important for pagination.
Key takeaway: ORDER BY creates deterministic presentation when the ordering columns uniquely
establish sequence.
SQL Query Fundamentals
7. Subqueries and CTEs
Subqueries and common table expressions break complex logic into understandable stages. CTEs
can make a query easier to review by naming intermediate results. They are a readability tool first;
performance behavior depends on the database engine and version.
Key takeaway: Subqueries and common table expressions break complex logic into understandable
stages.
SQL Query Fundamentals
8. Indexes
Indexes can accelerate selective lookups and joins, but they also consume storage and add work to
writes. Useful indexes reflect real query patterns. Query plans help determine whether the database
is scanning, seeking, sorting, or joining data as expected.
Key takeaway: Indexes can accelerate selective lookups and joins, but they also consume storage
and add work to writes.
SQL Query Fundamentals
9. Transactions
Transactions group related changes into a unit of work. Atomicity protects against partial updates,
while isolation controls how concurrent operations interact. Transactions should be as short as
practical because long-running work can hold locks or retain old data versions.
Key takeaway: Transactions group related changes into a unit of work.
SQL Query Fundamentals
10. Query Review
Before shipping a query, verify correctness with representative data, inspect its execution plan, and
consider behavior as tables grow. Clear naming and formatting help reviewers see joins and
predicates. Performance tuning should preserve correctness rather than trading accurate results for
speed.
Key takeaway: Before shipping a query, verify correctness with representative data, inspect its
execution plan, and consider behavior as tables grow.