SQL Mentoring Series — Part 1 of 3: Relational Foundations and Core Querying

SQL Mentoring Series — Part 1 of 3: Relational Foundations and Core Querying

SQL Mentoring Series — Part 1 of 3

Part 1: Relational Foundations and Core Querying · Part 2: Advanced Querying, Analytics and Transactional SQL · Part 3: Performance Engineering and Modern Data Architecture

A practical mentoring guide for junior developers learning how to think about relational databases, model business data, protect its integrity, and write reliable SQL queries.

Introduction — Learn SQL as an Engineer, Not as a List of Commands

A junior developer often begins SQL by memorising statements. SELECT reads data. INSERT creates rows. UPDATE changes rows. DELETE removes rows. Then come joins, keys, constraints, indexes, and suddenly SQL starts to feel like a collection of unrelated rules.

That is not the best way to learn it.

The better approach is to understand what a relational database is trying to achieve. A database is a structured model of business reality. A customer exists in the real world. An order is placed in the real world. A payment happens in the real world. The database stores facts that represent those things and events. SQL is the language we use to define, protect, retrieve, and later change those facts.

This first part of the mentoring guide builds that mental model from the beginning. We will start with what a relational database actually represents, then move into data modelling, normalization, data types, schema evolution, querying, and joins. By the end, you should not merely know how to write a query. You should be able to explain why the database has been designed in a particular way and what one row in your result actually means.

This matters because SQL errors are not always syntax errors. Many of the most dangerous SQL mistakes execute successfully. A join can return rows and still be logically wrong. A query can return a total and still count the same business fact several times. A table can accept data and still represent the business badly. Correct SQL therefore begins before the first SELECT is written.

Throughout this guide, keep asking four questions.

First: what business fact am I representing?

Second: what does one row mean?

Third: what rules must remain true regardless of which application writes the data?

Fourth: what exact result am I asking the database to return?

Those four questions will carry you through most of relational database engineering.

---

Module 1 — The SQL Mental Model

A database is a model of reality

Imagine an online retail business.

The company has customers. Customers place orders. Orders contain products. Payments are made. Deliveries happen.

None of those things physically lives inside SQL Server, PostgreSQL, MySQL, or another relational database. What the database contains are facts describing those things.

A customer row might contain a customer identifier, name, email address, and registration date. An order row might contain an order identifier, customer identifier, order date, and status.

This distinction sounds simple, but it changes how you design databases.

You are not asking, “What columns should I create?”

You are asking, “What facts does the business need to remember, and where should each fact live?”

A relational database organises these facts into relations, which we normally experience as tables. A table has rows and columns. A row represents one occurrence of something. A column represents one characteristic of that thing.

If the Customers table is designed correctly, one row represents one customer. If the Orders table is designed correctly, one row represents one order.

That phrase — one row represents — is going to become extremely important.

SQL is declarative

Developers coming from C#, Java, Python, or JavaScript are accustomed to imperative thinking.

In C#, you might retrieve a collection, loop through it, inspect each object, apply conditions, build a new list, and return the result.

SQL normally works differently.

You describe the result you want.

For example:

SELECT CustomerId, CustomerName
FROM Customers
WHERE IsActive = 1
ORDER BY CustomerName;

You are saying:

“Give me the identifiers and names of active customers, ordered by name.”

You are not telling SQL Server which page to read first, which CPU instruction to execute, whether to use an index, or which algorithm to use for sorting.

The database engine makes those decisions.

This gives us two important levels of thinking.

The logical level describes what the query means.

The physical level describes how the database engine obtains the result.

This separation is one of the most important concepts you can learn early.

Suppose two queries return exactly the same set of customers. Logically, they may be equivalent. Physically, one may read ten pages while another reads ten thousand. One may use an index seek while another scans a large structure.

Correctness comes first, but performance eventually requires understanding both levels.

Tables are not spreadsheets

A spreadsheet also has rows and columns, so beginners sometimes think of a database table as a more powerful spreadsheet.

That comparison quickly breaks down.

A relational table has rules. A column has a defined type. A primary key can guarantee identity. A foreign key can enforce a relationship. A unique constraint can prevent duplicate business identifiers. A check constraint can reject invalid values.

The database is therefore not merely storing values. It is protecting a model.

For example, if an order must belong to a valid customer, the relationship should not depend only on application code behaving correctly. A foreign key can make the database reject an order referencing a customer that does not exist.

That matters because applications change. New APIs appear. Imports are added. Reporting tools connect directly. Background workers write data. Developers run scripts.

The database may outlive several generations of application code.

Set-based thinking

Another important shift is thinking in sets rather than rows one at a time.

Suppose you need every unpaid invoice older than thirty days.

A procedural mindset may imagine:

“Read an invoice. Check its status. Check its date. If it qualifies, add it to the result. Repeat.”

SQL lets you describe the entire qualifying set.

SELECT InvoiceId, CustomerId, DueDate, Amount
FROM Invoices
WHERE Status = 'Unpaid'
  AND DueDate < @CutoffDate;

The database engine decides how to locate that set efficiently.

This is one reason row-by-row application loops are often a poor substitute for well-written SQL. Relational engines are designed to reason about sets.

The junior-to-professional shift

A beginner sees:

“Tables contain data and SQL gets the data.”

A stronger developer sees:

“The database represents business facts. Tables define the grain and relationships of those facts. SQL describes sets of information, and the optimizer decides how to obtain those sets physically.”

That mental model prepares you for everything that follows.

---

Module 2 — Modelling the Real World as Relational Data

Do not begin with tables

When someone gives you a new requirement, resist the urge to open your database tool and immediately create tables.

Start with the business.

Suppose you are building a property-development platform. The business talks about developments, plots, planning applications, contractors, invoices, inspections, and buyers.

Those nouns are clues. They may represent entities.

The business also uses verbs:

A development contains plots.

A contractor submits an invoice.

An inspector performs an inspection.

A buyer reserves a property.

Those verbs reveal relationships and events.

The first job of data modelling is to identify what must be represented before deciding how it will be represented physically.

Conceptual, logical, and physical models

It helps to think about modelling in three levels.

The conceptual model is the high-level business picture.

You might say:

“We have customers, orders, products, and payments. Customers place orders. Orders contain products. Payments settle orders.”

At this level you are not worrying about whether CustomerId should be an integer or UUID.

The logical model introduces entities, attributes, keys, and relationships.

Now you decide that a customer has an identifier, name, and email address. An order has an order identifier, customer identifier, date, and status.

The physical model turns those decisions into real database objects.

Now you decide whether the table is called Sales.Orders, whether the key is bigint, whether the status is constrained, which indexes exist, and how the schema will be deployed.

Separating these levels prevents implementation details from hiding modelling mistakes.

Grain: the question you must learn to ask

One of the strongest habits a junior developer can develop is asking:

What does one row represent?

This is the grain.

One row in Customers represents one customer.

One row in Orders represents one order.

One row in OrderItems represents one product line inside one order.

One row in Payments might represent one payment attempt, or one successful payment, depending on the business model.

That final example shows why grain is not obvious.

If you cannot clearly state what one row means, the table design is not finished.

Mixed grain creates confusion. Imagine an Orders table where some rows represent an entire order while other rows represent individual products. Every query becomes difficult because the meaning of a row is inconsistent.

Later, when we study joins and aggregation, grain becomes even more important because joining tables changes what one result row represents.

Cardinality

Cardinality describes how many records on one side can relate to records on the other.

A one-to-one relationship means one row corresponds to at most one row on the other side.

A one-to-many relationship is extremely common. One customer can place many orders.

A many-to-many relationship means many records on both sides can relate to many records on the other.

Orders and products provide a classic example. One order can contain many products, and one product can appear in many orders.

Relational databases normally resolve that many-to-many relationship using an intermediate table.

OrderItems may contain:

OrderId
ProductId
Quantity
UnitPrice

Now each row represents one product line belonging to one order.

Optionality

Cardinality is not enough. Ask whether the relationship is optional.

Can a customer exist without an order? Usually yes.

Can an order exist without a customer? Perhaps no, unless the business explicitly supports anonymous or guest orders.

Optionality influences whether a foreign-key column is nullable.

Do not make a column nullable simply because “maybe we will need it.” Nullable fields should represent genuine optionality or unknown information in the business.

Keys

A primary key identifies one row.

For example:

CustomerId bigint PRIMARY KEY

A foreign key references a row in another table and protects the relationship.

A natural key comes from the business world. Examples include a national registration number, ISBN, or external policy number.

A surrogate key is generated by the system and carries little or no business meaning.

A composite key uses several columns together.

For example, an order-item table might logically be unique by OrderId and ProductId, although many systems still add a surrogate OrderItemId for convenience and then protect the business uniqueness with a separate unique constraint.

The important point is that technical identity and business uniqueness are not always the same thing.

A generated CustomerId may identify the row internally, while a unique email address or external reference may enforce a separate business rule.

A practical modelling conversation

Imagine a junior developer says:

“I need an orders table. I will add CustomerName, CustomerEmail, ProductName, ProductPrice, Quantity, OrderDate, and OrderStatus.”

The senior question should be:

“What does one row represent?”

If the answer is one order, then product name and quantity immediately create a problem because one order can contain several products.

If the answer is one order item, then customer name and customer email will be repeated across every line of every order.

Modelling is therefore the discipline of placing facts at the correct grain.

That naturally leads us into normalization.

---

Module 3 — Normalization, Denormalization, and Data Integrity

Give every fact one authoritative home

Suppose a customer has placed fifty orders.

If the customer’s email address is copied into all fifty order rows, the same fact now exists fifty times.

The customer changes their email address.

How many rows must be updated?

Fifty.

What happens if only forty-nine are updated?

The database now contains two versions of the truth.

Normalization tries to prevent this kind of structural duplication.

Customer facts belong in the customer table.

Order facts belong in the order table.

Product facts belong in the product table.

The relationship between an order and a product belongs in the order-item table.

Functional dependency

A useful way to think about normalization is dependency.

Ask:

“What identity determines this value?”

Customer name depends on customer identity.

Order date depends on order identity.

Product description depends on product identity.

Quantity in an order line depends on the identity of that order line.

If a column depends on a different identity from the table’s grain, it may belong elsewhere.

First Normal Form

First Normal Form, often shortened to 1NF, removes repeating groups and multi-valued fields.

Do not create:

Product1
Product2
Product3

Do not store:

"101,205,309"

inside one field and pretend it is three separate product identifiers.

Instead, each relationship should be represented by its own row.

For example:

OrderId  ProductId  Quantity
1001     101        2
1001     205        1
1001     309        4

Now the database can query, constrain, join, and index the values properly.

Second Normal Form

Second Normal Form becomes particularly relevant when a table uses a composite key.

Suppose an order-item table is identified by OrderId plus ProductId.

Quantity may depend on the complete combination. It describes how many units of this product appear in this order.

But ProductName depends only on ProductId.

OrderDate depends only on OrderId.

Those columns therefore do not depend on the complete composite identity. They belong in the product and order tables respectively.

This prevents repeating order and product facts across every item row.

Third Normal Form

Third Normal Form removes dependencies between non-key descriptive values.

Imagine a customer table contains:

CustomerId
Postcode
Town

If Town is being stored purely because it is determined by Postcode, you should ask whether the design is representing one fact through another descriptive field rather than through the table’s key.

The precise normalization decision depends on the business and authoritative data source, but the principle remains:

Non-key attributes should describe the entity identified by the key rather than depend on other non-key attributes in a way that introduces avoidable duplication or anomalies.

Update, insertion, and deletion anomalies

Normalization protects against three classic problems.

An update anomaly occurs when one fact is repeated in several places and all copies must be changed.

An insertion anomaly occurs when you cannot record one fact without inventing another. If product information exists only inside order rows, you cannot create a product until someone orders it.

A deletion anomaly occurs when removing one fact accidentally removes another. Deleting a customer’s final order should not necessarily delete the customer’s identity.

Good relational design reduces these risks.

Constraints are part of the model

Normalization organises the data. Constraints enforce the rules.

A primary key ensures identity.

A foreign key protects relationships.

A unique constraint prevents prohibited duplication.

A CHECK constraint validates a condition.

NOT NULL makes required values mandatory.

A default can provide a defined value where the business rule genuinely allows one.

Consider:

CREATE TABLE Customers
(
    CustomerId bigint NOT NULL PRIMARY KEY,
    EmailAddress nvarchar(320) NOT NULL,
    IsActive bit NOT NULL
        CONSTRAINT DF_Customers_IsActive DEFAULT (1),

    CONSTRAINT UQ_Customers_Email
        UNIQUE (EmailAddress)
);

The application can still validate the email address and display a friendly message. But the database independently prevents two rows from using the same protected value.

That matters because not every write will come from the same user interface forever.

Denormalization

Normalization is not a religion.

Sometimes systems deliberately duplicate or pre-compute information to improve a measured workload.

Analytical warehouses often use denormalized star schemas because they are designed for large-scale reading and aggregation rather than transactional editing.

A reporting table may store pre-computed summaries.

A search index may duplicate information from several relational tables.

The difference is intentionality.

Accidental duplication is usually a modelling problem.

Deliberate denormalization is an architectural trade-off made for a known reason, with a plan for maintaining consistency.

A junior developer should therefore learn normalization first. You need to understand the clean model before you can safely decide when to break it.

---

Module 4 — Data Types and Physical Schema Design

A data type is a promise

A data type is not merely a container large enough to hold a value.

It is a statement about what that value means.

If a column contains a whole-number quantity, an integer type communicates that meaning.

If a column contains money, an exact decimal type usually expresses the requirement better than floating-point storage.

If a column contains a calendar date, a date type expresses the meaning far better than text.

Choosing the correct type improves correctness, validation, comparison, storage, and often performance.

Exact versus approximate numbers

This distinction matters enormously.

Integers represent whole numbers.

Fixed-precision decimal types represent exact decimal values within their declared precision and scale.

Floating-point types represent approximate numeric values.

Approximation is perfectly acceptable for many scientific measurements. It is generally a poor fit for authoritative financial amounts.

Imagine repeatedly calculating tax, interest, or balances using approximate binary floating-point representation. Tiny representation differences can accumulate or produce surprising comparisons.

For money, a type such as:

decimal(19,4)

is often a clearer choice because the required precision is explicit.

The exact precision should come from the domain rather than a copied convention.

Strings

Text requires several decisions.

How long can the value genuinely be?

Does the system require Unicode?

How will the value be compared and sorted?

A short code may have a very different requirement from a customer biography.

Avoid defining every string as the largest possible type simply because storage is cheap. Schema design should communicate reasonable expectations.

Unicode matters whenever the application must safely store names and text from different languages.

Collation controls comparison and ordering rules. Depending on the database and configured collation, comparisons may be case-sensitive or case-insensitive and may treat accents differently.

Collation can therefore affect searches and uniqueness rules, not merely display ordering.

Dates and times

Do not store dates as strings.

A string such as:

03/04/2026

is ambiguous. Is that 3 April or 4 March?

A real date type has defined semantics and can be compared, sorted, validated, and manipulated correctly.

Also distinguish different temporal meanings.

A birthday is a date.

An event start may be a local date and time tied to a time zone.

An audit timestamp may represent a specific instant and be stored in UTC.

Do not use one generic “date string” for all of these.

NULL

NULL does not mean zero.

It does not mean false.

It does not mean an empty string.

It represents an absent, unknown, or inapplicable value.

Suppose DeliveredAt is null.

That may correctly mean the order has not yet been delivered.

If you replace it with 1900-01-01, you have not removed uncertainty. You have replaced honest missing information with a false date.

On the other hand, allowing null everywhere makes the model vague.

If an order cannot legally exist without a customer, CustomerId should normally be non-nullable.

Use null to represent genuine optionality, not indecision during development.

Specialist types

Modern relational databases support more than simple numbers, strings, and dates.

JSON can store flexible semi-structured information.

Spatial types can represent geographical points and shapes.

UUIDs can provide globally generated identifiers.

Some platforms support arrays, ranges, and vector data.

These features are valuable when the business requirement genuinely matches them.

But a JSON column should not become an escape hatch from data modelling.

If CustomerEmail, OrderStatus, and TotalAmount are core business facts that are frequently queried and constrained, placing them inside an arbitrary JSON document usually gives up clarity and relational integrity without a good reason.

Use specialist types because the data is genuinely specialist, not because defining a schema feels inconvenient.

---

Module 5 — Creating and Evolving Databases Safely

Creating a table is creating a contract

When you write CREATE TABLE, you are not simply allocating storage.

You are defining a contract between the database and every application that uses it.

Consider:

CREATE TABLE Orders
(
    OrderId bigint NOT NULL,
    CustomerId bigint NOT NULL,
    OrderedAt datetime2 NOT NULL,
    Status varchar(30) NOT NULL,
    TotalAmount decimal(19,4) NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY (OrderId),

    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId),

    CONSTRAINT CK_Orders_TotalAmount
        CHECK (TotalAmount >= 0)
);

The table communicates several rules.

Every order has an identifier.

Every order references a customer.

Every order has a date, status, and amount.

Negative totals are not permitted.

These rules are part of the application architecture.

Schema organisation

Database schemas can group related objects and create useful ownership or security boundaries.

Instead of putting everything under dbo, a larger system might have:

Sales.Orders
Sales.OrderItems
Billing.Payments
Identity.Users
Reporting.MonthlyRevenue

Do not create schemas merely to make names look impressive. Use them when they communicate a meaningful functional or ownership boundary.

Identity and generated keys

Databases can generate technical identifiers using identity columns, sequences, or equivalent platform features.

A generated key is useful for internal identity.

But remember that generated numbers are not automatically suitable as gap-free business document numbers.

Transactions can roll back. Values may be cached. Inserts may fail. Gaps can occur.

If the business requires legally controlled numbering, treat that as a separate business requirement rather than assuming an identity column provides it.

Production databases evolve

The first version of a schema is never the final version.

You will add columns.

You will change relationships.

You will create indexes.

You will retire old fields.

The important engineering question is how to make those changes without breaking running applications.

Suppose CustomerName needs to become separate FirstName and LastName columns.

A risky deployment might immediately delete CustomerName and deploy new code.

What happens if the old application instance is still running?

What happens if deployment fails halfway through?

A safer approach is expand and contract.

First, expand the schema by adding the new columns while retaining the old one.

Then deploy application code capable of working with the new structure.

Backfill and validate existing data.

Allow all consumers to migrate.

Only after the old column is no longer required do you contract the schema by removing it.

This approach makes database changes compatible across deployment boundaries.

DELETE, TRUNCATE, and DROP are different

These commands can all make data disappear, but they do different things.

DELETE removes qualifying rows and can use a WHERE condition.

TRUNCATE removes all rows from a table using a different mechanism with different platform-specific restrictions and logging behaviour.

DROP removes the database object itself.

Never treat them as interchangeable.

A production engineer must understand whether the requirement is to remove selected data, empty a structure, or remove the structure entirely.

Migrations belong in version control

Database schema changes should be treated like application code.

They should be reviewed.

They should be tested against realistic data volumes.

They should be repeatable.

They should be promoted through environments.

EF Core migrations can help manage schema evolution, but the generated SQL still deserves engineering attention. A migration that works instantly against a developer database containing two hundred rows may behave very differently against a production table containing two hundred million rows.

Be especially careful with operations that may rewrite a large table, rebuild indexes, acquire long locks, or introduce required columns into existing data.

Roll-forward thinking

Developers often talk about rolling back deployments.

Database rollback is not always simple.

Suppose version two of your application stores new information in a new schema for three hours. If you revert the schema to version one, what happens to those new facts?

You may lose information.

You may no longer be able to interpret it.

For this reason, production database change strategies often prefer a compatible roll-forward correction rather than aggressive reversal.

The goal is not merely to deploy database changes.

The goal is to evolve the business contract without corrupting or losing business truth.

---

Module 6 — Reading Data with SELECT

SELECT is a business question

A query is not merely syntax. It is a precise question.

Suppose a product manager asks:

“Show active customers registered this year, newest first.”

Your job is to translate that statement into an exact relational request.

SELECT
    CustomerId,
    CustomerName,
    RegisteredAt
FROM Customers
WHERE IsActive = 1
  AND RegisteredAt >= @StartDate
ORDER BY RegisteredAt DESC, CustomerId DESC;

Every clause has a responsibility.

Projection

The SELECT list determines which columns appear in the result.

This is called projection.

Avoid routine use of:

SELECT *

It is useful during exploration, but application queries should normally request only the data they require.

Why?

You reduce unnecessary data transfer.

You make the contract of the query clearer.

You avoid accidental dependency on columns the application does not need.

You may also enable better index strategies later because a smaller set of required columns is easier to cover.

FROM

FROM identifies the source relation or relations.

In a single-table query, this seems obvious.

Later, when joins, derived tables, CTEs, and views appear, FROM becomes the place where the logical input dataset is constructed.

WHERE and predicates

WHERE removes rows that do not satisfy the condition.

The condition is a predicate.

WHERE Status = 'Pending'

describes a subset of the table.

You can combine predicates with AND, OR, and NOT.

Be careful with mixed boolean logic.

For example:

WHERE IsActive = 1
  AND Region = 'London'
   OR Region = 'Manchester'

may not mean what the reader initially assumes because operator precedence affects evaluation.

Use parentheses when the intended grouping matters.

WHERE IsActive = 1
  AND (Region = 'London' OR Region = 'Manchester')

Clarity is a correctness feature.

IN, BETWEEN, and LIKE

IN is useful when a value may match one of several alternatives.

WHERE Status IN ('Pending', 'Approved')

BETWEEN expresses an inclusive range.

For date-time values, however, developers should be careful with upper boundaries.

A pattern such as:

WHERE OrderedAt >= @StartDate
  AND OrderedAt < @NextDate

is often safer than trying to guess the final representable fraction of a second on the previous day.

LIKE provides simple wildcard matching.

A prefix search such as:

WHERE CustomerName LIKE 'Ah%'

has a known beginning.

A search such as:

WHERE CustomerName LIKE '%Ahmed%'

does not. Later, when we study indexes, you will see why a leading wildcard often makes traditional ordered-index navigation much less useful.

NULL filtering

Do not write:

WHERE DeliveredAt = NULL

Use:

WHERE DeliveredAt IS NULL

SQL uses three-valued logic because comparisons involving unknown values do not behave like ordinary true-or-false comparisons.

You do not need to become a mathematician to use SQL well, but you do need to respect the fact that null means unknown or absent.

DISTINCT

DISTINCT removes duplicate combinations from the final projection.

For example:

SELECT DISTINCT Region
FROM Customers;

asks for the different region values represented in the table.

That is legitimate.

But suppose a join unexpectedly produces repeated customers and a developer adds DISTINCT simply to hide them.

That is dangerous.

Unexpected duplicates often indicate that the result grain has not been understood.

Do not use DISTINCT as a cleaning spray for a query you do not understand.

ORDER BY and deterministic results

Without ORDER BY, SQL does not guarantee presentation order.

Rows may happen to appear in primary-key order during development. A later index change, parallel plan, or different data distribution can change that.

If order matters, request it explicitly.

For pagination, ordering must also be deterministic.

Suppose you order only by CreatedAt, but one hundred records share the same timestamp. Their relative order is unspecified.

Add a unique tie-breaker:

ORDER BY CreatedAt DESC, CustomerId DESC;

Now every row has a stable position.

Logical processing order

SQL is written in one order but logically evaluated in another.

A simplified mental model is:

1. FROM
2. WHERE
3. grouping
4. HAVING
5. SELECT
6. ORDER BY

This explains why an alias created in SELECT often cannot be referenced in WHERE. Logically, WHERE has already been evaluated before that selected alias exists.

Understanding logical processing prevents many confusing mistakes.

SARGability

This is your first glimpse into performance-aware query writing.

SARGable means roughly “search argument able.”

Suppose OrderedAt is indexed.

This predicate provides a direct range:

WHERE OrderedAt >= '2026-01-01'
  AND OrderedAt <  '2027-01-01'

SQL Server may be able to navigate directly to the relevant date range.

Now compare:

WHERE YEAR(OrderedAt) = 2026

Logically, the result may be the same.

Physically, you have wrapped the indexed column inside a function. Depending on the database and available indexes, the engine may need to calculate that function across many rows before deciding which ones qualify.

SARGability does not mean “an index will definitely be used.”

It means you have expressed the predicate in a form that gives the optimizer a useful direct search option.

The optimizer may still choose a scan if the query needs most of the table.

The important developer habit is:

Write predicates that communicate the search condition clearly.

---

Module 7 — Joining Tables and Working with Sets

Why joins exist

Normalization separates facts.

Customer information lives in one table.

Order information lives in another.

Product information lives somewhere else.

That separation protects integrity, but business questions frequently need those facts reconnected.

For example:

“Show every order together with the customer who placed it.”

That requires a join.

INNER JOIN

Suppose Customers.CustomerId is the primary key and Orders.CustomerId is the foreign key.

SELECT
    o.OrderId,
    o.OrderedAt,
    c.CustomerName
FROM Orders AS o
INNER JOIN Customers AS c
    ON c.CustomerId = o.CustomerId;

An inner join returns rows where the join relationship matches on both sides.

Here is the critical mentoring point.

If Customer 10 has five orders, Customer 10 appears five times in the result.

That is not automatically duplicate data.

The result grain has changed.

Before the join, one customer row represented one customer.

After joining customers to orders, one result row represents one order together with information about its customer.

This is why you must ask after every join:

What does one result row represent now?

LEFT JOIN

A left outer join preserves every row from the left-hand source even if no matching row exists on the right.

SELECT
    c.CustomerId,
    c.CustomerName,
    o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId;

Customers with orders produce matching rows.

Customers without orders still appear, but the order columns contain null.

This answers questions such as:

“Show every customer, including customers who have never ordered.”

ON versus WHERE in an outer join

This is a classic source of mistakes.

Suppose you want every customer and, where available, their paid orders.

You might write:

LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId
   AND o.Status = 'Paid'

The status condition controls which order rows qualify as matches, but customers remain preserved.

Now consider:

LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId
WHERE o.Status = 'Paid'

Customers without matching orders receive null order values from the outer join. The WHERE condition then rejects those null rows.

You have changed the meaning of the query.

The syntax looks similar. The business result is different.

This is why SQL expertise is about logical meaning, not memorising join templates.

CROSS JOIN

A cross join produces every combination.

If one table has three colours and another has four sizes, the result contains twelve combinations.

This can be useful when generating combinations deliberately.

It can also be catastrophic when produced accidentally on large tables.

Always understand the join predicate.

SELF JOIN

A self join connects a table to itself using different aliases.

An employee table containing ManagerId may join back to the employee table so each employee can be returned with their manager’s name.

The table has not duplicated itself physically. The aliases let the same relation play two logical roles.

EXISTS

Sometimes you do not need information from the related table. You only need to know whether a related row exists.

For example:

“Return customers who have at least one order.”

EXISTS expresses that intention clearly.

SELECT c.CustomerId, c.CustomerName
FROM Customers AS c
WHERE EXISTS
(
    SELECT 1
    FROM Orders AS o
    WHERE o.CustomerId = c.CustomerId
);

The business question is about existence, not about returning every matching child row.

This can also avoid accidental row multiplication.

NOT EXISTS

The opposite question is:

“Which customers have never placed an order?”

SELECT c.CustomerId, c.CustomerName
FROM Customers AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders AS o
    WHERE o.CustomerId = c.CustomerId
);

This is an anti-relationship query.

You will use this pattern constantly in real systems:

Products never ordered.

Invoices without payments.

Accounts without active users.

Records missing a required downstream process.

Set operations

Joins combine columns from related datasets.

Set operations combine compatible result rows.

UNION ALL appends one result to another and keeps duplicates.

UNION appends and then removes duplicate rows.

Because duplicate removal requires additional work, prefer UNION ALL when duplicates are valid or impossible.

INTERSECT finds rows appearing in both compatible results.

EXCEPT finds rows in the first result that do not appear in the second.

These are relational operations, not merely syntax conveniences.

The first glimpse of physical joins

At the logical level, you write:

JOIN

At the physical level, SQL Server may implement that join through different algorithms.

A Nested Loops join can be effective when one side is small and the other has an efficient lookup path.

A Merge Join can be attractive when both inputs are suitably ordered.

A Hash Join can be useful for larger equality joins where ordered input is not available.

Do not label one algorithm “good” and another “bad.”

The optimizer chooses based on estimated row counts, available indexes, ordering, and cost.

Later modules in this course go much deeper into these decisions.

For now, remember the separation:

You write the logical relationship.

The optimizer chooses the physical algorithm.

---

Conclusion — The Foundation You Should Carry Forward

Part 1 is not really about memorising seven groups of SQL features. It is about building a dependable way of thinking. Start with the business reality, define what one row means, place each fact at the correct grain, protect the model with keys and constraints, choose types that preserve meaning, evolve the schema carefully, and query the resulting data with precise predicates and joins.

If you keep returning to the questions “What does this row represent?”, “Where should this fact live?”, and “What exactly should this result mean?”, advanced SQL becomes much easier. Performance tuning, aggregation, transactions, and execution plans all depend on having this foundation right first.

Suggested Practice Project for Junior Developers

Build a small order-management database containing Customers, Products, Orders, and OrderItems.

Begin on paper.

Write one sentence explaining what one row in every table represents.

Draw the relationships.

Decide which relationships are mandatory.

Choose primary keys and foreign keys.

Identify at least one real business uniqueness rule.

Create the tables with correct data types and constraints.

Insert sample data including a customer with no orders and an order containing several products.

Then write queries that answer these questions:

• Which customers are active?
• Which customers have placed orders?
• Which customers have never ordered?
• Which products appear in a particular order?
• Which orders belong to a particular customer?
• Which customers should still appear when they have no orders?
• What happens to the grain when Orders is joined to OrderItems?
• Can you explain why DISTINCT is not a proper fix for accidental row multiplication?
• Can you write a date-range filter without applying a function to the date column?
• Can you explain every constraint in your schema in business language?

Do not treat this as a typing exercise.

For every table and query, explain the business meaning aloud.

That is how SQL stops being syntax and becomes engineering.

---

Sources and Further Reading

This mentoring guide is a synthesis of the relational foundations and querying material used throughout the course, particularly:

• Josephine Bush, *Learn SQL Database Programming* — relational database fundamentals, normalization, data types, database design, SELECT, filtering, joins, and set operations.
• *SQL for Data Analytics*, 4th Edition — relational modelling, constraints, SQL data types, data retrieval, joins, and analytical SQL foundations.
• Benjamin Nevarez, *SQL Server Query Tuning and Optimization* — used lightly in Part 1 to introduce the distinction between logical SQL and physical query execution; the performance material becomes central in Part 3.

The examples in this article are deliberately small. Production systems add concerns such as security, transaction isolation, indexing, observability, deployment strategy, and workload-specific performance tuning. Those topics belong to later parts of the course rather than being hidden inside the foundations.

---

Continue the SQL Mentoring Series

1. Part 1: Relational Foundations and Core Querying
2. Part 2: Advanced Querying, Analytics and Transactional SQL
3. Part 3: Performance Engineering and Modern Data Architecture