MASTERING DATA RELATIONS

MySQL
Essential Concepts and Practices
From foundational queries to relational thinking and
modern MySQL techniques.

– Where understanding grows and queries begin to think for you.

SELECT patterns FROM relations;

VO HOANG NHAT KHANG

Ph.D. Student in Natural Language Processing @MBZUAI

Version 0.1 • 2026

Preface

Learning SQL is often presented as a list of commands to memorize: SELECT, WHERE,

JOIN, and so on. We copy an example from the internet, change a column name or two,
and hope it works. Sometimes it does. Sometimes it does not, and the error message
feels cryptic and frustrating.
This book is written for readers who want to move beyond that stage.
My goal is to help you see SQL - in particular, the dialect used by modern MySQL
- not just as a programming language, but as a way of thinking about data. Instead of
treating queries as magic incantations, we will treat them as precise, logical statements
about sets, relationships, and constraints. When you finish a chapter, I want you not
only to know what to type, but also why it works and what the MySQL server is doing
on your behalf.
The journey begins with the basics: selecting rows, filtering with conditions, sort-
ing, and doing simple aggregations. From there, we gradually introduce relational
thinking: keys, joins, groupings, and the idea that tables are connected pieces of a
larger model. Later chapters cover data modification, constraints, and more advanced
techniques such as window functions and analytical queries, all illustrated using MySQL
syntax and behavior.
Although this book focuses on MySQL (and assumes a relatively recent version
such as MySQL 8.x), the underlying ideas apply to other relational systems as well.
Differences between SQL dialects matter in practice - and we will point out MySQL-
specific features where relevant - but they are secondary to the core relational concepts
you will see throughout these pages.
I wrote this material with a particular kind of reader in mind:

• Developers who already know how to code, but feel uncertain when they have
to design SQL queries on their own.

• Data analysts and students who want to build a solid foundation instead of rely-
ing on copy–paste snippets.

• Curious readers who may not work with databases every day, but want to under-
stand how structured data is managed and queried in modern MySQL and other
relational systems.

The chapters are intentionally structured from “first principles” toward more ad-
vanced topics. You can read them in order, or jump to specific parts when you need a
reference. Each section tries to balance formal accuracy with an honest, human expla-
nation of how things feel when you actually sit down to write a query in MySQL.
This book was developed while I was pursuing my Ph.D. studies. Many of the
examples are inspired by real problems: cleaning messy data, reconciling multiple data

Vo Hoang Nhat Khang i

sources, or trying to understand what is really stored in a production MySQL database.
Whenever possible, I have chosen examples that are small enough to fit on the page but
realistic enough to prepare you for what you will see in practice.
As you read, I encourage you to type the queries yourself, modify them, and break
them on purpose. Change conditions, remove clauses, or add new columns and see
how the result changes. MySQL (like other SQL systems) rewards this kind of exper-
imentation, and the fast feedback loop from the database is one of the best learning
tools you have.
If this book succeeds, you will finish it with two things:

1. A practical working knowledge of SQL as implemented by MySQL.

2. A mental model of relational data that will stay with you long after you forget
individual syntax details.

I was also fortunate to receive careful feedback during the writing and revision pro-
cess from friends working in the software industry. Their practical perspective helped
me improve clarity, fix imprecise wording, and keep the examples closer to what de-
velopers actually see in real systems. I would like to acknowledge Mr. Minh Quang Le,
Mr. Duc Tri Phan Nguyen, Mr. Quang Duy Chau Vu, Mr. Huy Cam La, Mr. Thuan
Tan Banh, Mr. Khang Manh Nguyen, Mr. Minh Cong Nguyen, Mr. An Truong Nguyen,
and Mr. Phu Kien Trac for their thoughtful comments and suggestions. Finally, I want
to thank my family. Their patience, encouragement, and quiet support made it possible
for me to spend long hours writing, revising, and testing examples. This book carries
their support in every chapter.
Thank you for choosing to spend your time with these pages. I hope they help
you build confidence, curiosity, and a deeper understanding of how your data actually
works.

VO HOANG NHAT KHANG

Ph.D. Student in Natural Language Processing @MBZUAI, Abu Dhabi

Vo Hoang Nhat Khang ii

 Contents

Contents

Preface i

I SQL Foundations 1

1 Getting Started with SQL 3

1.1 SQL Intro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.2 SQL Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

2 Basic Querying 7
2.1 SQL Select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.2 SQL Select Distinct . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
2.3 SQL Where . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2.4 SQL Order By . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2.5 SQL And . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.6 SQL Or . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.7 SQL Not . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

3 Modifying Data 37
3.1 SQL Insert Into . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.2 SQL Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
3.3 SQL Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.4 SQL Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

4 Functions and Calculations 57

4.1 SQL Select Top . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
4.2 SQL Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
4.2.1 SQL Min and Max . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
4.2.2 SQL Count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.2.3 SQL Sum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
4.2.4 SQL Avg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.3 SQL Like . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
4.4 SQL Wildcards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
4.5 SQL In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
4.6 SQL Between . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
4.7 SQL Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Vo Hoang Nhat Khang i

Contents

II Intermediate SQL 97
5 Mastering Joins 99
5.1 Overview of Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
5.1.1 Relational Thinking with Joins . . . . . . . . . . . . . . . . . . . . 100
5.1.2 Join Conditions and Keys . . . . . . . . . . . . . . . . . . . . . . . 101
5.1.3 Cross Join vs. Explicit Joins . . . . . . . . . . . . . . . . . . . . . . 102
5.2 SQL Inner Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
5.2.1 Definition and Use Cases . . . . . . . . . . . . . . . . . . . . . . . 104
5.2.2 Filtering with Inner Joins . . . . . . . . . . . . . . . . . . . . . . . 105
5.2.3 Common Pitfalls (Duplicate Rows, Missing Matches) . . . . . . . 107
5.3 SQL Left Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
5.3.1 Preserving Unmatched Rows . . . . . . . . . . . . . . . . . . . . . 109
5.3.2 Handling NULLs in Left Joins . . . . . . . . . . . . . . . . . . . . . 111
5.3.3 Left Joins with Aggregates . . . . . . . . . . . . . . . . . . . . . . . 113
5.3.4 Left Join in Relation to Inner and Right Joins . . . . . . . . . . . . 113
5.4 SQL Right Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
5.4.1 Basic Form and Symmetry with Left Join . . . . . . . . . . . . . . 114
5.4.2 Handling NULLs in Right Joins . . . . . . . . . . . . . . . . . . . . . 115
5.4.3 When (and When Not) to Use RIGHT JOIN . . . . . . . . . . . . 117
5.4.4 Converting a RIGHT JOIN to a LEFT JOIN . . . . . . . . . . . . . 118
5.4.5 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
5.5 SQL Full Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
5.5.1 Combining Left and Right Perspectives . . . . . . . . . . . . . . . 119
5.5.2 Emulating Full Joins in Systems Without Support . . . . . . . . . 120
5.6 SQL Self Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
5.6.1 Hierarchies and Parent–Child Relationships . . . . . . . . . . . . 122
5.6.2 Self Joins for Comparisons Within a Table . . . . . . . . . . . . . . 124

6 Combining and Analyzing Data 127

6.1 SQL Union and Union All . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
6.1.1 SQL UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
6.1.2 SQL UNION ALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
6.1.3 Set Semantics and Duplicates . . . . . . . . . . . . . . . . . . . . . 130
6.1.4 Compatibility of Column Lists . . . . . . . . . . . . . . . . . . . . 131
6.2 SQL Group By . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
6.2.1 Grouping Semantics . . . . . . . . . . . . . . . . . . . . . . . . . . 133
6.2.2 Aggregates with Group By . . . . . . . . . . . . . . . . . . . . . . 135
6.2.3 Common Errors (Non-Grouped Columns) . . . . . . . . . . . . . 136
6.3 SQL Having . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
6.3.1 Filtering Groups vs. Filtering Rows . . . . . . . . . . . . . . . . . 138
6.3.2 Using Aggregates in Having . . . . . . . . . . . . . . . . . . . . . 140
6.4 SQL Exists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
6.4.1 Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . 142
6.4.2 Exists vs. In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
6.4.3 Anti-Semijoin Patterns with Not Exists . . . . . . . . . . . . . . . 145
6.5 SQL Any and All . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
6.5.1 Quantified Comparisons . . . . . . . . . . . . . . . . . . . . . . . . 147

Vo Hoang Nhat Khang ii

 Contents

6.5.2 Any vs. Some vs. All . . . . . . . . . . . . . . . . . . . . . . . . . . 148

6.5.3 Practical Query Patterns . . . . . . . . . . . . . . . . . . . . . . . . 150

III Building & Managing

Databases 153
7 Database Management 155
7.1 SQL Create DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
7.2 SQL Drop DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
7.3 SQL Backup DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
7.4 Restoring Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159

8 Tables, Schemas, and Data Types 161

8.1 SQL Create Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
8.1.1 Defining Columns and Data Types . . . . . . . . . . . . . . . . . . 161
8.1.2 Choosing Appropriate Types . . . . . . . . . . . . . . . . . . . . . 163
8.2 SQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
8.2.1 Numeric Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
8.2.2 Character and Text Types . . . . . . . . . . . . . . . . . . . . . . . 167
8.2.3 Date and Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . 168
8.2.4 Boolean and Other Types . . . . . . . . . . . . . . . . . . . . . . . 169
8.3 SQL Alter Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
8.3.1 Adding, Modifying, and Dropping Columns . . . . . . . . . . . . 170
8.3.2 Evolving Schemas Safely . . . . . . . . . . . . . . . . . . . . . . . . 173
8.4 SQL Drop Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
8.4.1 Dropping Tables and Dependencies . . . . . . . . . . . . . . . . . 176
8.4.2 Archiving Before Drop . . . . . . . . . . . . . . . . . . . . . . . . . 178

9 Data Constraints and Relationships 179

9.1 SQL Not Null . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
9.2 SQL Unique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
9.3 SQL Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
9.4 SQL Foreign Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
9.5 SQL Check . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
9.6 SQL Default . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186

10 Reading Execution Plans in MySQL 189

10.1 What an Execution Plan Is . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
10.2 A Practical EXPLAIN Workflow . . . . . . . . . . . . . . . . . . . . . . . . . 190
10.3 Using EXPLAIN in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
10.3.1 Filter-then-join plan . . . . . . . . . . . . . . . . . . . . . . . . . . 191
10.3.2 Read EXPLAIN output . . . . . . . . . . . . . . . . . . . . . . . . . . 192

11 MySQL Reference Guide 197

11.1 Core SQL Keywords in MySQL . . . . . . . . . . . . . . . . . . . . . . . . 197
11.2 MySQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
11.2.1 Numeric Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
11.2.2 String and Text Types . . . . . . . . . . . . . . . . . . . . . . . . . . 200

Vo Hoang Nhat Khang iii

Contents

11.2.3 Date and Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . 201

11.2.4 JSON and Other Special Types . . . . . . . . . . . . . . . . . . . . 202
11.3 MySQL Built-in Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
11.3.1 String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
11.3.2 Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
11.3.3 Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . 208
11.3.4 Aggregation and Window Functions . . . . . . . . . . . . . . . . . 209
11.3.5 Control Flow Functions (IF, CASE, etc.) . . . . . . . . . . . . . . . 209

Further Reading and References 211

Afterword 213

Vo Hoang Nhat Khang iv

Part I SQL Foundations

Vo Hoang Nhat Khang 1

Chapter 1 Getting Started with SQL

A gentle introduction to SQL, database systems, core concepts, and the philosophy behind rela-
tional querying.

1.1 SQL Intro

Structured Query Language, commonly known as SQL, has become one of the most
enduring and influential languages in the history of computing. Originally developed
in the early 1970s alongside the relational model proposed by E. F. Codd, SQL was
designed to give humans a clear and expressive way to interact with data stored in
relational databases. Over the decades, it has remained remarkably stable, even as
database systems have evolved, scaled globally, and adapted to modern workloads.

Figure 1.1: Edgar Frank “Ted” Codd (1923 - 2003). Image taken from Wikipedia (https:
//[Link]/wiki/Edgar_F._Codd)

SQL is more than a command language; it is a declarative framework for describing

what data we want, rather than prescribing how a system must retrieve it. This char-
acteristic stands in contrast to traditional procedural programming. When we write a
SQL query, we specify logical intentthe conditions, relationships, and constraints that
characterize the desired result set. The database engine then determines the optimal
execution plan, applying decades of research in query optimization, indexing, concur-
rency control, and storage architecture.
Because of its declarative nature, SQL encourages a way of thinking that aligns
closely with mathematical logic. Relations, tuples, predicates, and set operations form

Vo Hoang Nhat Khang 3

1 Getting Started with SQL

the conceptual foundation upon which all SQL queries rest. Understanding these prin-
ciples is not merely academic; it directly impacts how effectively and efficiently one can
write queries for real-world systems.
In this opening section, we will examine what SQL fundamentally is, why it be-
came the universal language for relational data, and how its core ideas shape how we
reason about information. Before we explore syntax or write our first statements, it is
important to build a conceptual groundingone that helps the reader see SQL not just
as a tool, but as a formal system for expressing knowledge about data.

1.2 SQL Syntax

Having introduced the conceptual grounding of SQL, we now shift toward the practical
structure of the language itself. SQL is built around a collection of statements—formal
expressions that describe an intended action on a relational database. Although SQL is
declarative, its syntax follows a consistent and expressive pattern that allows complex
logic to be communicated with clarity.

SQL Statements
Most operations performed on a database—whether retrieving information, modifying
data, or managing schema—are expressed through SQL statements. A statement usu-
ally begins with a keyword, followed by one or more clauses that specify the context
or constraints of the operation.
For instance, consider a table named Employees. The following SQL statement re-
trieves all columns for every employee stored in the table:
SELECT * FROM Employees;

A more selective query might retrieve only two attributes:

SELECT Name, Department
FROM Employees;

Database Tables
A relational database organizes information into tables, each representing a set of logi-
cally related entities. Tables contain:
• Rows, which represent individual records, and
• Columns, which represent attributes of those records.
Below is a subset of the Products table.
ProductID ProductName UnitPrice Category
101 Arabica Coffee Beans 12.50 Beverages
102 Dark Chocolate Bar 3.75 Confectionery
103 Jasmine Green Tea 9.20 Beverages
104 Organic Olive Oil 18.95 Pantry

This subset illustrates both the structure and richness of relational data: each row
represents an entity, and each column corresponds to a well-defined attribute.

Vo Hoang Nhat Khang 4

 1.2 SQL Syntax

Case Sensitivity and Conventions

SQL keywords are not case sensitive. The following statements are equivalent:

select Name from Employees;

SELECT Name FROM Employees;
SeLeCt NaMe FrOm EmPlOyEeS;

In this book, all SQL keywords will appear in uppercase for clarity.

Statement Termination
Many database systems use a semicolon (;) to mark the end of a statement. While
some environments do not require it, using semicolons eliminates ambiguity and is
considered best practice. All SQL in this book will follow this convention.

Additional Examples of SQL Syntax

Filtering records based on a condition. The following query returns the names and
salaries of all employees whose salary exceeds 60, 000. Filtering is performed using the
WHERE clause, which restricts the result set to rows that satisfy a specified predicate.

SELECT Name, Salary

FROM Employees
WHERE Salary > 60000;

Inserting a new record into a table. This statement adds a new row to the Products
table. The INSERT INTO clause lists the target columns, and VALUES provides the corre-
sponding data for each attribute.

INSERT INTO Products (ProductName, UnitPrice, Category)

VALUES ('Maple Syrup', 7.80, 'Pantry');

Updating one or more attributes of an existing row. The query below modifies the
department assignment of an employee. The SET clause specifies which columns to
update, while the WHERE clause ensures that only the intended record is affected.

UPDATE Employees
SET Department = 'Research'
WHERE EmployeeID = 42;

Deleting records that meet a specific criterion. This statement removes all products
classified under the Confectionery category. As with updates, deletions should al-
ways be combined with an appropriate WHERE clause to avoid removing unintended
data.

DELETE FROM Products

WHERE Category = 'Confectionery';

Vo Hoang Nhat Khang 5

1 Getting Started with SQL

A Preview of Core SQL Commands

• SELECT – retrieves data from one or more tables

• INSERT INTO – adds new records to a table

• UPDATE – modifies existing records

• DELETE – removes records

• CREATE TABLE – defines a new table

• ALTER TABLE – updates table structure

• DROP TABLE – removes a table

• CREATE INDEX – improves search performance

• DROP INDEX – deletes an index

Vo Hoang Nhat Khang 6

Chapter 2 Basic Querying

Write your first queries: selecting rows, filtering, sorting, and using logical operators.

2.1 SQL Select

The SELECT statement is the central construct of SQL and the primary tool for retrieving
data from a relational database. Conceptually, it corresponds to the idea of “asking a
question” about the data: which attributes are of interest, from which tables, and under
what conditions.
From the perspective of relational theory, a SELECT query can be seen as a combi-
nation of two core operations:

• Projection – choosing which columns (attributes) to include in the result.

• Selection – choosing which rows (tuples) satisfy a given predicate.

The power of the SELECT statement arises from the way these operations, together
with joins and aggregations, can be composed into highly expressive queries.

Basic Form of a SELECT Statement

In its simplest form, a SELECT statement specifies a list of columns and a source table:

SELECT column1, column2, ...

FROM TableName;

For example, suppose we have a table Employees with columns EmployeeID, Name,
Department, and Salary. The following query returns every row, but only the Name and
Department columns:

SELECT Name, Department

FROM Employees;

Here, the SELECT clause defines the projection (which attributes we want), and the
FROM clause identifies the relation (table) over which the query is evaluated.
Example: Comparing a narrow and a wide projection
Assume the table Employees also contains HireDate and ManagerID. We can com-
pare a “narrow” and a “wide” projection:
-- Narrow projection: only what we need right now
SELECT Name, Department

Vo Hoang Nhat Khang 7

2 Basic Querying

FROM Employees;

-- Wide projection: useful during exploration

SELECT *
FROM Employees;

In the first query, the result set is focused and stable: if the schema later gains new
columns, this query still returns only Name and Department. The second query
automatically exposes all columns, which is convenient when you are still learning
the schema but less predictable in a production context.

It is also possible to request all columns using the asterisk (*):

SELECT *
FROM Employees;

While SELECT * is convenient in exploratory work, it is often discouraged in pro-

duction systems, where explicitly naming columns improves clarity, stability, and per-
formance.
Caution: Using SELECT * in long-lived queries
Relying on SELECT * in application code or dashboards can lead to subtle bugs:
newly added columns may unexpectedly appear in result sets, break client-side
parsing, or increase network and memory usage. Prefer explicit column lists for
queries that are meant to live a long time.

Projection and Derived Columns

The SELECT clause is not limited to existing columns; it can also contain expressions,
computed values, and column aliases. For instance:
SELECT Name,
Salary,
Salary * 1.10 AS AdjustedSalary
FROM Employees;

In this example, AdjustedSalary is a derived column created by applying a sim-

ple arithmetic expression. The AS keyword assigns a human-readable alias to the com-
puted result. Such expressions can be arithmetic, string-based, or involve built-in func-
tions provided by the database system.
Example: Combining columns into a display name
It is often useful to compute a column that exists only for presentation:
SELECT CONCAT(LastName, ', ', FirstName) AS DisplayName,
Department,
Salary
FROM Employees
ORDER BY DisplayName;
Here the base columns FirstName and LastName remain unchanged in the table,

Vo Hoang Nhat Khang 8

 2.1 SQL Select

but the query exposes a derived DisplayName that better matches how people are
usually listed in reports.

Note: Column aliases and their visibility

Column aliases defined in the SELECT list (such as AS AdjustedSalary) are gener-
ally available in the ORDER BY clause but not in the WHERE clause. This is a direct
consequence of the logical order in which the query is evaluated: filtering occurs
before projection, whereas ordering occurs after it.

Restricting Rows with WHERE

To limit the rows included in the result, SQL uses the WHERE clause. This clause specifies
a logical predicate that each row must satisfy in order to appear in the output.

SELECT Name, Department, Salary

FROM Employees
WHERE Department = 'Engineering';

This query returns only those employees whose Department value matches ’Engineering’.
Multiple conditions can be combined using logical operators such as AND, OR, and NOT:

SELECT Name, Department, Salary

FROM Employees
WHERE Department = 'Engineering'
AND Salary > 70000;

From a relational perspective, the WHERE clause performs a selection operation, fil-
tering the relation to a subset of rows that satisfy the predicate.
Note: NULL and the WHERE clause
Predicates that involve NULL behave differently from ordinary values. A compari-
son such as Salary = NULL is never true; it evaluates to an unknown truth value
and therefore filters out every row. To test for missing values, use IS NULL or IS
NOT NULL:

SELECT Name, Salary

FROM Employees
WHERE Salary IS NULL;

Ordering the Result with ORDER BY

By default, most SQL implementations do not guarantee any specific ordering of rows
in the result set unless an ORDER BY clause is provided. To impose an explicit ordering,
we can write:

SELECT Name, Department, Salary

FROM Employees
WHERE Salary > 60000
ORDER BY Salary DESC;

Vo Hoang Nhat Khang 9

2 Basic Querying

The keyword DESC requests a descending order (from highest to lowest). The de-
fault is ASC (ascending), which can be omitted:

ORDER BY Department ASC, Name ASC;

Here, the result is first ordered by Department, and ties within each department are
broken by Name. Ordering is a presentation-level concern; it does not alter the under-
lying data, but it can be crucial for analysis and reporting.
Example: Stable ordering for paginated results
When paginating over results, it is important that the ordering be deterministic.
For example:

SELECT EmployeeID, Name, Salary

FROM Employees
WHERE Salary > 60000
ORDER BY Salary DESC, EmployeeID ASC
LIMIT 20 OFFSET 40;

Including a unique key such as EmployeeID as the final tie-breaker ensures that
rows always appear in a consistent order across pages, even when multiple em-
ployees share the same salary.

Logical Versus Syntactic Order

One subtle but important aspect of the SELECT statement is the distinction between the
order in which clauses are written and the logical order in which they are conceptually
applied.
A typical SELECT query is written in the following syntactic order:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...;

However, conceptually, the database engine processes the query in a sequence closer
to:

1. Identify the source tables in the FROM clause (including joins).

2. Filter rows according to the WHERE predicate.

3. Group rows (if a GROUP BY clause is present).

4. Filter groups using the HAVING clause.

5. Apply projections and expressions in the SELECT list.

6. Apply ordering specified by ORDER BY.

Vo Hoang Nhat Khang 10

 2.1 SQL Select

Note: Why aliases work in ORDER BY but not in WHERE

Because the SELECT list is applied after WHERE but before ORDER BY, most SQL en-
gines allow column aliases to be referenced in ORDER BY but not in WHERE. For in-
stance:

SELECT Salary * 1.10 AS AdjustedSalary

FROM Employees
WHERE AdjustedSalary > 80000 -- often invalid
ORDER BY AdjustedSalary DESC; -- usually valid

Conceptually, the WHERE clause cannot see AdjustedSalary because the alias does
not exist yet at that stage of evaluation; the ORDER BY clause, however, comes after
the projection step and can use it.

SELECT as a Tool for Exploration

In practice, the SELECT statement serves both as a formal query mechanism and as an
exploratory tool. Analysts, developers, and researchers routinely use SELECT queries
to inspect schemas, verify assumptions about the data, and construct more complex
analyses step by step.
For example, to gain an initial sense of the distinct categories present in the Products
table, one might write:
SELECT DISTINCT Category
FROM Products
ORDER BY Category;

This query performs a projection over Category, eliminates duplicates with DISTINCT,
and orders the resulting set. Even simple exploratory queries like this can yield impor-
tant insights into the structure and semantics of a dataset.
Example: Incrementally refining an exploratory query
A common workflow is to start with a broad query and refine it:

-- Step 1: look at raw data

SELECT *
FROM Products
LIMIT 20;

-- Step 2: focus on the columns that matter

SELECT ProductID, Category, Price
FROM Products;

-- Step 3: summarize by category

SELECT Category,
COUNT(*) AS ProductCount,
AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category
ORDER BY AvgPrice DESC;

Vo Hoang Nhat Khang 11

2 Basic Querying

Each query builds on the intuition gained from the previous one, gradually moving
from “What is in this table?” to more structured questions about distribution and
scale.

Caution: Staying safely in read-only mode for now

At this stage we focus only on SELECT statements, which are read-only: they ask
questions about the data but do not change it.
Most SQL systems also support commands that can modify data (for example,
changing or removing rows). You will meet those in later chapters. While you
are still exploring, it is a good habit to double-check that every statement you run
really starts with SELECT.
This simple habit makes it much less likely that you accidentally change data when
your goal is only to understand it.

2.2 SQL Select Distinct

In many practical datasets, repeated values are common. A single department may
employ hundreds of people, a single product category may contain dozens of items,
and the same city may appear across thousands of customer records. While this re-
dundancy is natural, there are many situations in which we are interested not in every
row, but in the set of unique values that appear in a column or combination of columns.
The SELECT DISTINCT construct provides a direct way to express this intent. It in-
structs the database engine to eliminate duplicate rows from the result, returning only
one representative for each distinct combination of values in the projected attributes.

Basic Use of DISTINCT

The general form of a query using DISTINCT is:
SELECT DISTINCT column1, column2, ...
FROM TableName;

To see this in context, consider again the Employees table with columns such as
EmployeeID, Name, Department, and Salary. Suppose many employees work in the
same department. If we wish to list each department only once, we can write:
SELECT DISTINCT Department
FROM Employees;

This query does not return employee-level detail; instead, it returns the set of de-
partment names that actually occur in the Employees table.
Example: Exploring the set of departments
A typical exploratory workflow might start with all rows and then move to distinct
values:

-- Step 1: see all department values as they appear in the data

SELECT Department

Vo Hoang Nhat Khang 12

 2.2 SQL Select Distinct

FROM Employees
LIMIT 20;

-- Step 2: collapse to the unique set of departments

SELECT DISTINCT Department
FROM Employees
ORDER BY Department;

The first query helps you notice spelling inconsistencies or unexpected categories,
while the second gives a clean list suitable for reports or user interfaces (such as a
drop-down menu of departments).

Example with a Products Table

Assume the following subset of a Products table:

ProductID ProductName UnitPrice Category

101 Arabica Coffee Beans 12.50 Beverages
102 Dark Chocolate Bar 3.75 Confectionery
103 Jasmine Green Tea 9.20 Beverages
104 Organic Olive Oil 18.95 Pantry
105 Espresso Roast Coffee 11.40 Beverages

A simple SELECT over the Category column would return five rows, because each
row is preserved:
SELECT Category
FROM Products;

Conceptually, the result would look like:

Category
Beverages
Confectionery
Beverages
Pantry
Beverages

If our goal is to understand which categories exist, not how many products belong
to each, this redundancy is unhelpful. Using DISTINCT, we can obtain the unique set
of categories:
SELECT DISTINCT Category
FROM Products;

The logical result is:

Category
Beverages
Confectionery
Pantry

Vo Hoang Nhat Khang 13

2 Basic Querying

Note: Distinct values vs. counts

SELECT DISTINCT Category tells you which categories exist, but not how many prod-
ucts belong to each. To answer “how many” questions you will later use aggrega-
tion (for example, counting rows per category), which we discuss in the chapter
on aggregate functions.

DISTINCT on Multiple Columns

The DISTINCT keyword applies to the entire list of columns in the SELECT clause, not to
each column independently. In other words, the database considers rows to be dupli-
cates only if all selected columns match.
For instance, suppose we want to know which combinations of department and
salary level appear in the Employees table:

SELECT DISTINCT Department, Salary

FROM Employees;

In this query, two rows are treated as identical only if both their Department and
Salary values are equal. This is often useful when exploring patterns such as pay bands
within departments or the distribution of attributes across groups.
Example: DISTINCT on combinations vs. single columns
Compare the following two queries:

-- Unique departments only

SELECT DISTINCT Department
FROM Employees;

-- Unique (department, salary) pairs

SELECT DISTINCT Department, Salary
FROM Employees;

The first query might return:

Department
----------------
Engineering
HR
Sales

while the second could produce many more rows, one for each salary actually ob-
served in each department. Both use DISTINCT, but they answer different questions:
the first about categories, the second about observed combinations.

NULL Values and DISTINCT

When NULL values appear in the selected columns, SQL treats NULL as an ordinary value
for the purposes of DISTINCT. Multiple rows in which the selected columns are all NULL
are considered duplicates of one another.

Vo Hoang Nhat Khang 14

 2.2 SQL Select Distinct

For example, if several products have an unknown category recorded as NULL, the
query

SELECT DISTINCT Category

FROM Products;

will include NULL at most once in the result set, regardless of how many underlying
rows have a NULL category.
Note: DISTINCT and partially NULL rows
When using DISTINCT on multiple columns, two rows are considered the same
only if all selected columns match, including any NULL values. For example, the
pair (Department = ’Sales’, Bonus = NULL) will be deduplicated against an-
other row with exactly the same combination of values, but it is still distinct from
(Department = ’Sales’, Bonus = 0).

DISTINCT, GROUP BY, and Performance Considerations

Conceptually, SELECT DISTINCT is closely related to GROUP BY. Many database engines
may internally implement a DISTINCT query by grouping on the selected columns and
discarding group-level aggregates. For example, the following two queries are often
equivalent in practice:

SELECT DISTINCT Category

FROM Products;

SELECT Category
FROM Products
GROUP BY Category;

Despite this similarity, the choice between DISTINCT and GROUP BY is largely a mat-
ter of intent and readability. DISTINCT emphasizes the idea of deduplicating results,
whereas GROUP BY is typically used when aggregates (such as counts or sums) are in-
volved.
Because DISTINCT requires the database to detect and remove duplicate rows, it can
be more expensive than a simple SELECT on large datasets, especially when applied to
many columns or complex expressions. Indexes covering the relevant columns can
significantly improve performance.
Caution: Using DISTINCT as a band-aid for bad joins
In real projects, DISTINCT is sometimes added to a query simply because “the re-
sult had duplicates”. While this may hide the symptoms, it does not explain why
duplicates appeared in the first place. My suggest is, before relying on DISTINCT to
“fix” a query, it is worth investigating whether the duplicates reflect a real property
of the data or a problem in the query logic.

When to Use SELECT DISTINCT

SELECT DISTINCT is appropriate when:

Vo Hoang Nhat Khang 15

2 Basic Querying

• The goal is to obtain a set of unique values rather than all underlying records.
• You are exploring a dataset and want to understand the domain of a particular
attribute.
• You are preparing intermediate result sets for further analysis or reporting.

However, it should be used with care. Overusing DISTINCT as a quick fix for unex-
pected duplicates can hide modeling issues or errors in joins. A well-informed practi-
tioner uses DISTINCT deliberately, with a clear understanding of why duplicates exist
and what it means to remove them.
In the next section, we will expand our discussion of row selection by introducing
the WHERE clause, which allows us to express precise logical conditions for filtering data.

2.3 SQL Where

The WHERE clause is the primary mechanism in SQL for restricting which rows are in-
cluded in a query result. While the SELECT clause determines what is projected (which
columns are returned), the WHERE clause determines which rows from the underlying
tables qualify to appear.
Formally, WHERE corresponds to a selection operation on a relation: given a predicate
(a logical condition), only those tuples that satisfy the predicate are preserved in the
result set.

Basic Form of the WHERE Clause

A typical query using WHERE has the following structure:

SELECT column1, column2, ...

FROM TableName
WHERE condition;

Here, condition is a logical expression that evaluates to either true or false for each
row. Only the rows for which the condition evaluates to true are included in the final
result; all others are discarded.
For example, consider again the Employees table with columns EmployeeID, Name,
Department, and Salary. The following query returns only employees in the Engineer-
ing department:

SELECT Name, Department, Salary

FROM Employees
WHERE Department = 'Engineering';

In this case, the condition Department = ’Engineering’ is evaluated for every row
in Employees. Rows that do not meet this criterion are excluded from the result.
Example: Filtering rows step by step
A useful habit is to start with a broad query and then add conditions incrementally:

-- Step 1: see everyone in the table

Vo Hoang Nhat Khang 16

 2.3 SQL Where

SELECT Name, Department, Salary

FROM Employees;

-- Step 2: focus on a single department

SELECT Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering';

-- Step 3: add a salary condition

SELECT Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering'
AND Salary >= 80000;

This kind of gradual refinement makes it easier to understand which condition is

responsible for which change in the result.

Comparison Operators
Conditions in a WHERE clause frequently involve comparisons between a column and a
constant value, a column and another column, or a column and a computed expression.
Common comparison operators include:

• = equal to

• <> or != not equal to (syntax varies by system)

• > greater than

• >= greater than or equal to

• < less than

• <= less than or equal to

For instance, to retrieve employees whose salary exceeds 70,000:

SELECT Name, Salary

FROM Employees
WHERE Salary > 70000;

Or, to find employees who are not in the Sales department:

SELECT Name, Department

FROM Employees
WHERE Department <> 'Sales';

Comparison operators can be applied to numeric, textual, and date/time columns,

provided the underlying database system supports the comparison for the given data
types.

Vo Hoang Nhat Khang 17

2 Basic Querying

Note: Comparing text values

Text comparisons such as Department = ’Sales’ are usually case-sensitive or case-
insensitive depending on the database collation settings. In some systems, ’Sales’
and ’SALES’ are treated as the same value; in others they are considered different.
When behavior matters, it is better to be explicit, for example by using functions
such as LOWER(Department) and a lower-case constant.

Combining Conditions
Real-world queries often involve more than one condition. SQL allows multiple pred-
icates in the WHERE clause to be combined using logical operators such as AND, OR, and
NOT. For example:

SELECT Name, Department, Salary

FROM Employees
WHERE Department = 'Engineering'
AND Salary >= 80000;

This query returns only those employees who work in Engineering and have a salary
of at least 80,000.
Example: Combining AND and OR with parentheses
Suppose we want all employees in Engineering with salary at least 80,000, or any
employee in the Research department (regardless of salary):

SELECT Name, Department, Salary

FROM Employees
WHERE (Department = 'Engineering' AND Salary >= 80000)
OR Department = 'Research';

Parentheses make the intended logic explicit. Without them, it can be hard to see
which parts of the condition are grouped together, especially as queries grow more
complex.

The precise behavior of these logical operators, along with their evaluation order
and interaction with parentheses, will be discussed in later sections dedicated to AND,
OR, and NOT. For now, it is sufficient to recognize that the WHERE clause can express
complex logical conditions by composing simpler predicates.

WHERE with Text, Numbers, and Dates

The WHERE clause is not restricted to numeric comparisons. It is equally applicable to
character and date types.

Filtering by text.

SELECT ProductName, Category

FROM Products
WHERE Category = 'Beverages';

Vo Hoang Nhat Khang 18

 2.3 SQL Where

Filtering by numeric range.

SELECT ProductName, UnitPrice

FROM Products
WHERE UnitPrice > 15.00;

Filtering by date. Assuming a table Orders with a column OrderDate:

SELECT OrderID, CustomerID, OrderDate

FROM Orders
WHERE OrderDate >= '2025-01-01';

The exact literal format for dates can vary between database systems, but the logical
role of the WHERE clause remains the same: it filters rows based on a specified condition.
Note: Being explicit with date ranges
When filtering by date ranges, it is helpful to think carefully about inclusiveness.
For example, a condition such as OrderDate >= ’2025-01-01’ AND OrderDate <
’2025-02-01’ clearly describes all orders in January 2025. This style of “half-open”
interval becomes especially useful when time-of-day information is involved.

WHERE and NULL Values

Special care is required when dealing with NULL values in conditions. A NULL represents
an unknown or missing value, and comparisons involving NULL do not behave like
ordinary comparisons. For example, the condition

WHERE Department = NULL

does not match rows in which Department is unknown. Instead, SQL provides the
predicates IS NULL and IS NOT NULL:

SELECT Name, Department

FROM Employees
WHERE Department IS NULL;

The treatment of NULL values, and their implications for logical reasoning in SQL,
will be explored more fully in the dedicated section on NULL.
Note: Three-valued logic
When a comparison involves NULL, the result is neither true nor false, but an “un-
known” third state. The WHERE clause only keeps rows where the condition is true,
so rows that evaluate to unknown are filtered out just like rows that evaluate to
false. This three-valued logic is one of the main reasons why NULL can be surpris-
ing in SQL.

Vo Hoang Nhat Khang 19

2 Basic Querying

WHERE Beyond SELECT

Although we currently use the WHERE clause only with SELECT queries, it plays a similar
role in other kinds of statements as well: it describes which rows a statement should
apply to.
Later in the book, when we learn how to modify data, we will see that the WHERE
clause is what prevents a change from affecting every row in a table. Developing a
precise understanding of WHERE now will make it much easier to use those statements
safely later.
Caution: Checking your WHERE clause before running a query
Even with read-only SELECT statements, it is helpful to pause and mentally check
what your WHERE clause is doing: Which rows should be included? Which should
be excluded? If the result looks surprising, the issue is often in the condition rather
than in the rest of the query.

2.4 SQL Order By

In relational theory, a table is a set (or multiset) of tuples without any inherent order-
ing. Rows are conceptually unordered, and the logical meaning of a query does not
depend on the order in which tuples are stored.
In practice, however, the order of results is often crucial for analysis, reporting, and
user interfaces. The ORDER BY clause provides a way to impose a deterministic order-
ing on the rows returned by a query. Without an ORDER BY clause, most SQL imple-
mentations do not guarantee any particular order, even if the result appears sorted by
accident.

Basic Use of ORDER BY

The general form of a query using ORDER BY is:
SELECT column1, column2, ...
FROM TableName
ORDER BY column1 [ASC | DESC],
column2 [ASC | DESC], ...;

Here, ASC (ascending) is the default direction and may be omitted; DESC specifies
descending order.
Consider a Products table with columns ProductName and UnitPrice. To list prod-
ucts from the least expensive to the most expensive, we can write:
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice ASC;

To reverse the ordering, we use DESC:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Vo Hoang Nhat Khang 20

 2.4 SQL Order By

Example: Ordering search results

Imagine a simple search over the Products table:

SELECT ProductName, UnitPrice

FROM Products
WHERE ProductName LIKE '%coffee%'
ORDER BY UnitPrice DESC;

Here, the WHERE clause limits the result to products whose names contain the word
coffee, and ORDER BY then shows the most expensive coffee-related products first.
The selection and ordering steps work together but play distinct roles.

Ordering by Multiple Columns

The ORDER BY clause can specify more than one column. In that case, ordering is ap-
plied lexicographically: the first column determines the primary order, and subsequent
columns are used to break ties.
For example, suppose we have the following subset of Employees:
EmployeeID Name Department Salary
7 Alice Thompson Engineering 90000
3 David Li Engineering 85000
12 Maria Gomez Marketing 75000
5 Jacob Smith Marketing 78000
If we want to see employees ordered first by department name (alphabetically) and
then by salary (descending) within each department, we can write:
SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC,
Salary DESC;
The result groups employees by department and orders the most highly paid em-
ployees first within each group.
Note: ORDER BY and stable tie-breaking
If you only order by Department, employees within the same department may ap-
pear in any order. Adding a second key such as Salary DESC (or even a unique
identifier like EmployeeID) gives a stable and predictable ordering within each de-
partment.

Ordering by Expressions and Column Positions

The ORDER BY clause is not limited to simple column references. Many database sys-
tems allow ordering by expressions, such as arithmetic or function calls. For example:
SELECT Name,
Salary,
Salary * 1.10 AS AdjustedSalary
FROM Employees
ORDER BY AdjustedSalary DESC;

Vo Hoang Nhat Khang 21

2 Basic Querying

Here, the result is ordered according to the derived column AdjustedSalary. Some
systems also permit using the ordinal position of a column in the SELECT list:

SELECT Name,
Department,
Salary
FROM Employees
ORDER BY 3 DESC; -- order by the third column in the SELECT list

While positional ordering can be concise, it is generally less readable and more
fragile than naming columns explicitly. For long-lived or shared queries, referring to
columns or aliases by name is preferable.
Note: Using column aliases in ORDER BY
Because ordering happens after the SELECT list is evaluated, most SQL systems al-
low you to use column aliases in the ORDER BY clause:

SELECT Name,
Salary * 1.10 AS AdjustedSalary
FROM Employees
ORDER BY AdjustedSalary DESC;

This can make queries easier to read than repeating the full expression.

ORDER BY and NULL Values

The placement of NULL values in a sorted result is not fully standardized and may differ
between database systems. Some implementations treat NULL as a very small value
(appearing first in ascending order), while others treat it as very large (appearing last).
Several systems provide explicit control using non-standard extensions such as:

ORDER BY UnitPrice ASC NULLS LAST;

ORDER BY UnitPrice DESC NULLS FIRST;

When working with NULL values in ordered results, it is important to consult the
documentation for the specific database system or to use explicit expressions (e.g.,
COALESCE) to model the desired behavior.
Example: Pushing NULL values to the end manually
Even if your database does not support NULLS FIRST/LAST, you can encode the
intention using expressions:

SELECT ProductName, UnitPrice

FROM Products
ORDER BY (UnitPrice IS NULL) ASC,
UnitPrice ASC;

The condition UnitPrice IS NULL evaluates to 0 for non-NULL values and 1 for NULL
values, so non-NULL prices appear first, sorted in ascending order.

Vo Hoang Nhat Khang 22

 2.5 SQL And

Logical Versus Physical Order

Adding an ORDER BY clause affects only the presentation of the result; it does not change
how data is stored in the underlying table. This distinction is important: even if a table
appears to be stored in a particular order on disk, the SQL language does not guarantee
that a query without ORDER BY will return rows in that order.
For example, the following two queries are logically different:

SELECT Name, Salary

FROM Employees;

SELECT Name, Salary

FROM Employees
ORDER BY Salary DESC;

The first query makes no promises about row order; the second explicitly requests
that rows be ordered by salary in descending order. Any reasoning about the sequence
of results should rely only on the second form.
Caution: Never rely on accidental ordering
It is common to see result sets that “look sorted” even though the query has no
ORDER BY clause. This often happens because of how the database happens to read
rows from disk. However, this behavior is not guaranteed and may change after an
index is added, data is reloaded, or the database is upgraded. If the order matters,
always specify it explicitly.

Performance Considerations
Ordering can be computationally expensive, especially on large result sets. To sort
rows, the database engine may need to allocate memory and perform sorting opera-
tions that scale with the size of the intermediate result. Indexes on the columns used
in ORDER BY can reduce this cost.
For example, if there is an index on UnitPrice, a query such as:

SELECT ProductName, UnitPrice

FROM Products
ORDER BY UnitPrice;

may be able to use that index to produce sorted results more efficiently. However, the
exact behavior depends on the optimizer and the presence of other clauses (such as
WHERE or joins), which we will explore in later chapters.

2.5 SQL And

The logical operator AND is one of the fundamental building blocks for expressing com-
plex conditions in SQL. While a simple WHERE clause may filter rows based on a single
predicate, realistic queries often require multiple criteria to be satisfied simultaneously.
The AND operator allows these criteria to be combined into a single, more expressive
condition.

Vo Hoang Nhat Khang 23

2 Basic Querying

Formally, AND corresponds to the logical conjunction of two (or more) predicates: a
row is included in the result only if all predicates connected by AND evaluate to true for
that row.

Basic Use of AND

The general pattern for using AND in a WHERE clause is:

SELECT column1, column2, ...

FROM TableName
WHERE condition1
AND condition2
AND condition3
... ;

Each condition is evaluated independently, and the row qualifies only when every
condition is satisfied. Consider the Employees table with columns Name, Department,
and Salary. The following query retrieves only those employees who work in the En-
gineering department and earn at least 80,000:

SELECT Name, Department, Salary

FROM Employees
WHERE Department = 'Engineering'
AND Salary >= 80000;

In this example, an employee in Engineering with a salary of 75,000 does not appear
in the result, nor does an employee in another department with a salary of 90,000. Both
conditions must hold simultaneously.

Example: Refining a query with an extra condition

A common workflow is to start with a simple filter and then strengthen it using
AND:

-- Step 1: all employees in Engineering

SELECT Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering';

-- Step 2: only highly paid engineers

SELECT Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering'
AND Salary >= 80000;

The first query answers “Who works in Engineering?” The second answers the
more specific question “Who are the higher-paid engineers?” The difference is
entirely in the additional AND condition.

Vo Hoang Nhat Khang 24

 2.5 SQL And

Combining Multiple Criteria

The AND operator becomes especially useful when queries involve several dimensions
of filtering. For instance, suppose we want to find products that belong to a particular
category and also satisfy price constraints:
SELECT ProductName, Category, UnitPrice
FROM Products
WHERE Category = 'Beverages'
AND UnitPrice BETWEEN 10.00 AND 20.00;

Here, a product is included in the result only if it is in the ’Beverages’ category

and its price lies within the specified range.
Note: Order of conditions in AND
From a logical point of view, the order of conditions connected by AND does not
matter: A AND B is equivalent to B AND A. The database engine is free to evaluate
them in any order. You should choose an order that makes the intention of the
query clearest to human readers.

Logical Conjunction and Truth Conditions

At a logical level, AND corresponds to conjunction: the combined predicate is true ex-
actly when each of its component predicates is true. Ignoring NULL for the moment, the
truth table for two boolean expressions A and B combined with A AND B is:
A B A AND B
TRUE TRUE TRUE
TRUE FALSE FALSE
FALSE TRUE FALSE
FALSE FALSE FALSE

In the context of SQL, each condition in the WHERE clause evaluates to either true,
false, or unknown (when NULL is involved). Only rows for which the overall expression
evaluates to true are preserved.

Interaction with NULL (Three-Valued Logic)

When NULL values are present, SQL uses a three-valued logic: predicates can evaluate
to TRUE, FALSE, or UNKNOWN. The behavior of AND in this setting is slightly more subtle.
For example, if A is true but B is unknown, then A AND B is unknown, not false.
Intuitively:
• If any predicate connected by AND is definitely false, the whole conjunction is false.
• If all predicates are definitely true, the conjunction is true.
• Otherwise, the result may be unknown.
Rows for which the overall condition is unknown do not satisfy the WHERE clause and
are therefore excluded from the result. This is particularly important when conditions
involve columns that may contain NULL values.

Vo Hoang Nhat Khang 25

2 Basic Querying

Note: AND with conditions that can be NULL

Suppose some employees do not yet have a recorded bonus:

SELECT Name, Department, Bonus

FROM Employees
WHERE Department = 'Sales'
AND Bonus > 1000;

If Bonus is NULL for a row, the predicate Bonus > 1000 is neither true nor false but
unknown, so the whole AND expression is not satisfied and that row is filtered out. If
you want to include employees with missing bonus information, you might instead
write:

WHERE Department = 'Sales'

AND (Bonus > 1000 OR Bonus IS NULL);

AND in Practice
Filtering on department, salary, and location.
SELECT Name, Department, Salary, City
FROM Employees
WHERE Department = 'Engineering'
AND Salary >= 85000
AND City = 'Berlin';

This query selects engineers in Berlin whose salary is at least 85,000. An employee
satisfying only two of the three conditions will not appear in the result.

Applying AND to dates and status flags. Assume an Orders table with columns
OrderDate, Status, and TotalAmount. To find all completed orders in the current year
with a total amount above a threshold:
SELECT OrderID, OrderDate, Status, TotalAmount
FROM Orders
WHERE Status = 'Completed'
AND OrderDate >= '2025-01-01'
AND TotalAmount > 100.00;

The conjunction ensures that only orders satisfying all three conditions are returned.
Example: Checking that all conditions are really needed
Sometimes a long chain of AND conditions hides redundancy. For instance:

SELECT OrderID, CustomerID, TotalAmount

FROM Orders
WHERE Status = 'Completed'
AND TotalAmount > 100.00
AND TotalAmount >= 0.00;

The final condition TotalAmount >= 0.00 may be unnecessary if the data model

Vo Hoang Nhat Khang 26

 2.5 SQL And

already guarantees non-negative totals. Removing redundant conditions can make

queries easier to read without changing their meaning.

Readability and Parentheses

Although AND has a higher precedence than OR in SQLs operator hierarchy, it is good
practice to use parentheses when combining multiple logical operators. This not only
avoids subtle precedence-related bugs, but also makes the intent of the query more
transparent to human readers.
Consider the following two predicates:

WHERE Department = 'Engineering'

AND Salary >= 80000
OR Department = 'Research';

Depending on the database system and operator precedence, this may not behave
as intended. A clearer and safer formulation is:

WHERE (Department = 'Engineering' AND Salary >= 80000)

OR Department = 'Research';

Here, parentheses make it explicit that high-earning engineers and all researchers
should be included.
Caution: Ambiguity when combining AND and OR
Without parentheses, many readers will hesitate before deciding how a complex
condition should be interpreted. Even if you know the precedence rules, the next
person reading the query may not. When in doubt, add parentheses to show ex-
actly which rows you intend to include.

AND Beyond WHERE

The AND operator is not restricted to WHERE clauses. It also appears in JOIN conditions
and in CHECK constraints, among other contexts. For example, a join that relates two
tables on multiple matching columns might look like:

SELECT [Link], [Link], [Link]

FROM Orders AS o
JOIN Customers AS c
ON [Link] = [Link]
AND [Link] = [Link];

Here, both equality conditions in the ON clause must be satisfied for a pair of rows
to be joined. If this syntax feels unfamiliar, you can treat it as a preview: later chapters
will return to joins in much greater detail.

Vo Hoang Nhat Khang 27

2 Basic Querying

2.6 SQL Or
While the AND operator requires that multiple conditions all be satisfied simultaneously,
the OR operator relaxes this requirement. With OR, a row is included in the result if at
least one of the specified predicates evaluates to true.
In logical terms, OR represents disjunction. It is essential for expressing queries
that select rows matching one criterion or another, often corresponding to either/or
situations in informal reasoning.

Basic Use of OR
The general pattern for using OR in a WHERE clause is:

SELECT column1, column2, ...

FROM TableName
WHERE condition1
OR condition2
OR condition3
... ;

Each condition is evaluated independently. A row qualifies for inclusion if at least

one of these conditions is true.
Consider the Employees table with columns Name, Department, and City. To retrieve
employees who work either in Engineering or in Research, we might write:

SELECT Name, Department, City

FROM Employees
WHERE Department = 'Engineering'
OR Department = 'Research';

Any employee whose department is either ’Engineering’ or ’Research’ appears

in the result set.
Example: Relaxing a filter with OR
Suppose we begin with a strict filter on a single department:

SELECT Name, Department

FROM Employees
WHERE Department = 'Engineering';

If we later decide to include Research as well, we can relax the condition:

SELECT Name, Department

FROM Employees
WHERE Department = 'Engineering'
OR Department = 'Research';

The first query answers Who works in Engineering?, while the second answers
Who works in Engineering or Research? The only change is the use of OR.

Vo Hoang Nhat Khang 28

 2.6 SQL Or

Logical Disjunction and Truth Conditions

Ignoring NULL for the moment, the behavior of OR can be summarized by the classical
truth table for two boolean expressions A and B combined as A OR B:

A B A OR B
TRUE TRUE TRUE
TRUE FALSE TRUE
FALSE TRUE TRUE
FALSE FALSE FALSE

In SQL, when multiple predicates are connected with OR, the overall expression is
true for a row as soon as any individual predicate is true.

Combining OR with Ranges and Categories

The OR operator is especially useful when filtering by several alternative values or con-
ditions. For example, suppose we want to list products that are either beverages or
confectionery:

SELECT ProductName, Category, UnitPrice

FROM Products
WHERE Category = 'Beverages'
OR Category = 'Confectionery';

Similarly, we can combine numeric and categorical conditions:

SELECT Name, Department, Salary

FROM Employees
WHERE Salary > 90000
OR Department = 'Executive';

This query returns all employees whose salary is above 90,000, along with all em-
ployees in the Executive department, even if their salary is lower.
Note: OR as a union of sets
It is often helpful to imagine that each condition in a WHERE clause selects a set of
rows. Using OR corresponds to taking the union of those sets: any row that belongs
to at least one of the sets is kept in the result.

OR and IN: A More Compact Alternative

When multiple OR conditions compare the same column to different constant values,
many database systems support the more concise IN syntax. For example, the earlier
department query can be rewritten as:

SELECT Name, Department, City

FROM Employees
WHERE Department IN ('Engineering', 'Research');

Semantically, this expression is equivalent to:

Vo Hoang Nhat Khang 29

2 Basic Querying

WHERE Department = 'Engineering'

OR Department = 'Research';

Although IN is not a direct replacement for all uses of OR, it can improve readability
and reduce repetition when the pattern matches.
Example: Replacing a long OR chain with IN
Compare the following predicates:

WHERE City = 'Berlin'

OR City = 'Paris'
OR City = 'Rome';

WHERE City IN ('Berlin', 'Paris', 'Rome');

Both describe the same set of cities, but the second version is shorter and often
easier to maintain, especially when the list grows longer.

Interaction with NULL (Three-Valued Logic)

When NULL values are involved, SQL again uses three-valued logic (TRUE, FALSE, UNKNOWN).
For the OR operator, the intuitive rules are:

• If any predicate is definitely true, the entire disjunction is true.

• If all predicates are definitely false, the disjunction is false.
• Otherwise, the result may be unknown.

For example, if A is unknown and B is false, then A OR B is unknown, not false.

Rows for which the overall condition is unknown do not satisfy the WHERE clause and
are thus excluded from the result.
This subtle interaction becomes important when conditions reference columns that
may contain NULL. For instance:

SELECT Name, City

FROM Employees
WHERE City = 'Berlin'
OR City = 'Munich';

Rows in which City is NULL do not satisfy either comparison; the result of each
comparison is unknown, and so the row does not pass the WHERE filter.
Note: Including rows with NULL using OR
If you want to include rows where a value is missing, you can extend the predicate
explicitly:

SELECT Name, City

FROM Employees
WHERE City = 'Berlin'
OR City = 'Munich'

Vo Hoang Nhat Khang 30

 2.6 SQL Or

OR City IS NULL;

Here, employees with no recorded city are included alongside those in Berlin or
Munich.

Combining OR with AND: The Role of Parentheses

Queries frequently mix AND and OR in the same WHERE clause. Because AND has higher
precedence than OR in SQL, expressions can behave differently from what a casual read-
ing might suggest.
Consider the following predicate:

WHERE Department = 'Engineering'

OR Department = 'Research'
AND Salary >= 80000;

Due to operator precedence, this is interpreted as:

WHERE Department = 'Engineering'

OR (Department = 'Research' AND Salary >= 80000);

This means that all engineers are included regardless of salary, but only researchers
earning at least 80,000 are included. If the intent was to include only high-earning
engineers and researchers, the predicate should be written with explicit parentheses:

WHERE (Department IN ('Engineering', 'Research'))

AND Salary >= 80000;

The disciplined use of parentheses is essential for expressing compound logical con-
ditions clearly and correctly.
Caution: Subtle bugs from missing parentheses
Many real-world query bugs stem from missing or misplaced parentheses in ex-
pressions that mix AND and OR. A useful habit is to read the condition out loud in
plain language and check whether the SQL structure matches that sentence. If it
does not, adjust the parentheses until it does.

OR in Practice: Examples
Filtering by multiple cities.

SELECT Name, City

FROM Customers
WHERE City = 'Berlin'
OR City = 'Paris'
OR City = 'Rome';

This query returns customers located in any of the specified cities.

Vo Hoang Nhat Khang 31

2 Basic Querying

Mixing category and price filters.

SELECT ProductName, Category, UnitPrice
FROM Products
WHERE Category = 'Beverages'
OR UnitPrice < 5.00;

Here, low-priced products are included regardless of category, and all beverages
are included regardless of price.

OR Beyond WHERE
Like AND, the OR operator is not limited to WHERE clauses. It can appear in CHECK con-
straints, computed columns, and other logical expressions. However, it is less com-
monly used in join conditions, where equality-based predicates combined with AND
are more typical. Later chapters on constraints and joins will revisit these contexts in
more detail.

2.7 SQL Not

The NOT operator is the primary mechanism for expressing logical negation in SQL.
While AND and OR allow us to combine conditions, NOT allows us to invert a condition,
selecting rows for which a given predicate does not hold. Negation is essential for
expressing exclusion criteria, such as all customers who are not from a certain country
or all orders that are not yet completed.
In logical terms, NOT takes a boolean expression and reverses its truth value, subject
to the nuances of SQLs three-valued logic.

Basic Use of NOT

The general form of a query using NOT is:

SELECT column1, column2, ...

FROM TableName
WHERE NOT condition;

For example, to retrieve all employees who are not in the Sales department, we can
write:

SELECT Name, Department, Salary

FROM Employees
WHERE NOT (Department = 'Sales');

This is logically equivalent to:

WHERE Department <> 'Sales';

However, the explicit use of NOT becomes more expressive when combined with
more complex predicates.

Vo Hoang Nhat Khang 32

 2.7 SQL Not

Example: Two ways to say not Sales

Consider the following two predicates:

WHERE Department <> 'Sales';

WHERE NOT (Department = 'Sales');

They select the same set of rows (ignoring NULL for the moment). The first uses a
dedicated not equal operator, while the second uses NOT to negate a simpler equal-
ity. When conditions grow more complex, the NOT form can make the structure of
the logic more visible.

Negating Comparisons and Patterns

NOT can be applied to various types of predicates, including comparisons, membership
tests, and pattern matches. Some common patterns include:

• NOT = or <> (not equal)

• NOT IN (value not in a set)

• NOT LIKE (string does not match a pattern)

• NOT BETWEEN (value outside a range)

For example, to find all products whose price is not less than 5.00:

SELECT ProductName, UnitPrice

FROM Products
WHERE NOT (UnitPrice < 5.00);

To retrieve customers who are not located in a small set of cities:

SELECT Name, City, Country

FROM Customers
WHERE City NOT IN ('Berlin', 'Paris', 'Rome');

To exclude product names that match a particular pattern:

SELECT ProductName
FROM Products
WHERE ProductName NOT LIKE 'Organic%';

Note: Choosing between NOT and a positive condition

There is often more than one way to express the same logic. For instance,

WHERE NOT (UnitPrice < 5.00)

is equivalent to:

WHERE UnitPrice >= 5.00

Vo Hoang Nhat Khang 33

2 Basic Querying

The second form is usually easier to read because it states the acceptable range
directly rather than describing what is excluded.

Logical Negation and Truth Conditions

Ignoring NULL for the moment, the behavior of NOT can be summarized using a simple
truth table:

Expression NOT Expression

TRUE FALSE
FALSE TRUE

In SQL, the situation becomes more subtle when NULL is involved, because predi-
cates can evaluate to UNKNOWN in addition to TRUE and FALSE.

NOT and NULL (Three-Valued Logic)

In the presence of NULL, SQL uses three-valued logic. A predicate such as Salary >
70000 may be:

• TRUE, if Salary is known and greater than 70,000;

• FALSE, if Salary is known and less than or equal to 70,000;

• UNKNOWN, if Salary is NULL.

For NOT, the corresponding truth table is:

Expression NOT Expression

TRUE FALSE
FALSE TRUE
UNKNOWN UNKNOWN

Thus, negating an UNKNOWN does not produce TRUE; it remains UNKNOWN. As a conse-
quence, the following WHERE clause:

WHERE NOT (Department = 'Sales');

does not include rows where Department is NULL, because the predicate Department =
’Sales’ evaluates to UNKNOWN, and its negation is still UNKNOWN, not TRUE. Rows with
UNKNOWN conditions do not satisfy the WHERE filter.
If the intent is to include both non-Sales departments and rows with unknown de-
partments, a more explicit predicate is needed, such as:

WHERE Department <> 'Sales'

OR Department IS NULL;

Vo Hoang Nhat Khang 34

 2.7 SQL Not

Note: NOT and missing information

It is tempting to assume that not equal to ’Sales’ automatically includes rows where
the department is missing. In SQLs three-valued logic, this is not the case: missing
information (NULL) is neither equal to ’Sales’ nor not equal to it. When missing
values should be included, they generally need to be handled explicitly with IS
NULL.

De Morgans Laws in SQL

Negation often interacts with AND and OR through identities known as De Morgans
laws. These identities are useful both for reasoning about queries and for refactoring
conditions:

NOT (A AND B) ≡ (NOT A) OR (NOT B)

NOT (A OR B) ≡ (NOT A) AND (NOT B)

For example, suppose we want to find employees who are not in Engineering and
not in Research. We could write this directly as:

WHERE Department <> 'Engineering'

AND Department <> 'Research';

Using NOT with a grouped condition, the same logic can be expressed as:

WHERE NOT (Department = 'Engineering'

OR Department = 'Research');

Both forms are equivalent (subject to the usual caveats about NULL). Choosing be-
tween them is largely a matter of readability and stylistic preference.
Example: Rewriting a negative condition
Suppose we start with a negated condition:

WHERE NOT (Category = 'Beverages'

OR Category = 'Confectionery');

Using De Morgans law, we can rewrite this as:

WHERE Category <> 'Beverages'

AND Category <> 'Confectionery';

Both queries exclude the same categories, but some readers may find one form
clearer than the other. Being comfortable moving between these forms is helpful
when reading and refactoring existing SQL.

NOT with IN, BETWEEN, and EXISTS

Many SQL constructs have direct negated forms that incorporate NOT:

Vo Hoang Nhat Khang 35

2 Basic Querying

• NOT IN to exclude a set of values:

WHERE Country NOT IN ('Germany', 'France');

• NOT BETWEEN to select values outside an inclusive range:

WHERE UnitPrice NOT BETWEEN 10.00 AND 20.00;

• NOT EXISTS to select rows for which a related set of rows does not exist (typically
using a subquery).
At this stage we will treat NOT EXISTS as a preview: later chapters on subqueries
and EXISTS will show how it can be used to express questions such as customers with
no orders or rows without a matching record in another table.

Readability and Intent

Negation can make predicates harder to read, especially when nested or combined with
other logical operators. Overuse of NOT can lead to double negatives and convoluted
expressions. When possible, it is often clearer to reformulate conditions in a positive
style.
Compare the following two predicates:
WHERE NOT (Status = 'Cancelled' OR Status = 'Returned');
and:
WHERE Status IN ('Pending', 'Shipped', 'Completed');
Both may describe the same set of acceptable statuses, but the latter often com-
municates intent more directly. As a general guideline, prefer forms that are easy to
understand at a glance, even if they require a few more characters.
Caution: Double negatives in WHERE clauses
Conditions like NOT (Status <> ’Active’) or NOT (Department NOT IN (...))
are technically valid but difficult to parse quickly. If you find yourself reading a
condition twice to understand it, consider rewriting it in a simpler, more positive
form.

NOT in Constraints and Data Quality Rules

Beyond WHERE clauses, NOT is frequently used in CHECK constraints and other integrity
rules. For example, to ensure that a numeric column Discount never takes on negative
values, one might define:
CHECK (NOT (Discount < 0));
or more simply:
CHECK (Discount >= 0);
Later chapters on constraints and schema design will return to such uses in more
detail. For now, it is enough to see that NOT is a key tool for articulating what is not
allowed or what should be explicitly excluded, both in queries and in the structure of
the database itself.

Vo Hoang Nhat Khang 36

Chapter 3 Modifying Data

3.1 SQL Insert Into

Up to this point, our focus has been on reading and interpreting existing data using
the SELECT statement. In any realistic system, however, data is not static: new employ-
ees are hired, new products are added, and new orders are placed. The INSERT INTO
statement is the primary mechanism in SQL for creating new rows in a table.
From a relational point of view, an INSERT operation extends a relation by adding
one or more new tuples that conform to the tables schema and integrity constraints.

Basic Form of INSERT

The general pattern of an INSERT statement that adds a single row is:

INSERT INTO TableName (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

The column list specifies which attributes are being assigned, and the VALUES clause
provides the corresponding data in the same order. For example, suppose we have a
Products table with the columns ProductName, UnitPrice, and Category:

INSERT INTO Products (ProductName, UnitPrice, Category)

VALUES ('Hazelnut Coffee', 13.50, 'Beverages');

This statement adds a new product to the Products table. If the table also has an
auto-incrementing primary key column (such as ProductID), that column may be omit-
ted from the INSERT list so that the database system can generate the key automatically.

Example: Letting the database generate a key

Assume Products has an auto-increment ProductID column:

INSERT INTO Products (ProductName, UnitPrice, Category)

VALUES ('Ethiopian Single Origin', 14.20, 'Beverages');

The database will allocate a new ProductID (for example, 106), without you having
to specify it explicitly. This reduces the risk of collisions and keeps key generation
consistent across all inserts.

Vo Hoang Nhat Khang 37

3 Modifying Data

Inserting with and without a Column List

Some database systems allow the column list to be omitted, provided that values are
supplied for all columns in the exact order defined by the table schema:

INSERT INTO Products

VALUES ('Hazelnut Coffee', 13.50, 'Beverages');

Although this can be concise for quick experiments, omitting the column list is gen-
erally discouraged in long-lived applications, for several reasons:

• The order of columns in the table may not be obvious to future readers.

• Schema changes (such as adding a new column) can silently break existing INSERT
statements.

• Explicit column lists serve as documentation, clarifying which attributes are be-
ing set.

For these reasons, this book will consistently use INSERT INTO with an explicit col-
umn list.
Caution: Omitting the column list
An INSERT without a column list may work today but fail tomorrow after a schema
change. If a new column is added in the middle of the table definition, all value
positions shift, and your INSERT may suddenly put data into the wrong columns
or raise errors. Using an explicit column list makes such changes much safer.

Inserting Multiple Rows

Many SQL implementations support inserting multiple rows in a single statement by
providing multiple VALUES tuples:

INSERT INTO Products (ProductName, UnitPrice, Category)

VALUES ('Jasmine Green Tea', 9.20, 'Beverages'),
('Sea Salt Caramel', 4.10, 'Confectionery'),
('Wholegrain Crackers', 3.30, 'Pantry');

This form can be more efficient than issuing separate INSERT statements, especially
when populating a table with initial data.
Example: Seeding a lookup table
Suppose you have a small OrderStatus lookup table:
INSERT INTO OrderStatus (StatusCode, Description)
VALUES ('PENDING', 'Waiting for processing'),
('SHIPPED', 'Shipped to customer'),
('COMPLETED', 'Delivered and completed'),
('CANCELLED', 'Cancelled by customer or system');

Using a single multi-row INSERT keeps the initial data for this reference table in
one place and makes it easy to see all allowed status codes at a glance.

Vo Hoang Nhat Khang 38

 3.1 SQL Insert Into

Defaults, NULL, and Omitted Columns

Not every column must be explicitly assigned in every INSERT operation. The treatment
of omitted columns depends on how the table is defined:

• If a column has a DEFAULT value, that value is used.

• If a column allows NULL and no default is specified, the value becomes NULL.

• If a column is defined as NOT NULL and no default exists, omitting it will typically
cause an error.

Consider a simplified Employees table:

• EmployeeID (auto-increment primary key)

• Name (NOT NULL)

• Department (nullable, no default)

• HireDate (default: current date)

An INSERT statement might look like:

INSERT INTO Employees (Name, Department)

VALUES ('Alice Thompson', 'Engineering');

In this case, the database engine might:

• Generate EmployeeID automatically.

• Store ’Alice Thompson’ in Name.

• Store ’Engineering’ in Department.

• Use the default value (e.g., todays date) for HireDate.

If we explicitly want to insert a row with an unknown department, we can use NULL:

INSERT INTO Employees (Name, Department)

VALUES ('Jonas Weber', NULL);

The semantics of NULL - which represents an unknown or missing value - will be

discussed in more depth in the section on NULL values, but it already plays a role when
adding new data.
Note: Checking the table definition before inserting
Before writing INSERT statements, it is often helpful to inspect the table definition
to see which columns are NOT NULL, which have defaults, and which allow NULL. In
practice, this might involve commands like DESCRIBE Employees; or using a graph-
ical tool, but the principle is the same: design your INSERT to match the schema
rather than guessing.

Vo Hoang Nhat Khang 39

3 Modifying Data

Constraint Checking and Errors

Every INSERT operation is subject to the integrity constraints defined on the table.
These may include:

• NOT NULL constraints,

• UNIQUE constraints,

• PRIMARY KEY and FOREIGN KEY constraints,

• CHECK constraints.

If an INSERT attempts to violate one of these constraints, the database system will
typically reject the operation and raise an error. For example, if Email is defined as
UNIQUE, inserting a second row with the same email address may fail:

INSERT INTO Users (UserName, Email)

VALUES ('khang', 'khang@[Link]');

-- later
INSERT INTO Users (UserName, Email)
VALUES ('another_user', 'khang@[Link]'); -- likely error

Similarly, if a foreign key constraint requires that every CustomerID in the Orders
table correspond to an existing row in the Customers table, inserting an order with a
non-existent CustomerID will be rejected.
Caution: Learning from INSERT errors
Constraint errors during INSERT are not just failures; they are feedback from the
data model. A NOT NULL error may indicate missing information, while a UNIQUE
violation might reveal duplicated data or a misunderstanding of which attributes
are supposed to be unique. Reading the error message carefully is often the fastest
way to understand what the schema expects.

INSERT and Transactions

In many database systems, INSERT statements participate in transactions. This means
that a group of operations - including multiple INSERT statements and other modifica-
tions - can be treated as a single atomic unit. Either all changes are committed, or none
are.
Although a full discussion of transactions is beyond the scope of this section, it is
worth noting that inserting data is rarely an isolated act in practice. Instead, INSERT op-
erations often form part of a larger sequence of changes linked to a particular business
event.
Note: Thinking in terms of events
One helpful mental model is to imagine a business event (such as a new order is
placed) and then ask which rows in which tables need to be inserted as a conse-
quence. Transactions allow those related changes to succeed or fail together, keep-

Vo Hoang Nhat Khang 40

 3.2 SQL Null Values

ing the database in a consistent state.

INSERT INTO . . . SELECT

In addition to inserting literal values, SQL also allows inserting the results of a query
into a table. This takes the following general form:

INSERT INTO TargetTable (column1, column2, ...)

SELECT expression1, expression2, ...
FROM SourceTable
WHERE ...;

This pattern, sometimes used for archiving, transformation, or data migration, will
be explored in detail in the later section on INSERT INTO SELECT. For now, it is enough
to recognize that INSERT can draw its input either from explicit values or from existing
tables.
Example: Copying selected rows into another table
Imagine copying high-value products into a separate FeaturedProducts table:

INSERT INTO FeaturedProducts (ProductName, UnitPrice, Category)

SELECT ProductName, UnitPrice, Category
FROM Products
WHERE UnitPrice > 20.00;

This single statement both selects rows from Products and inserts them into
FeaturedProducts. Later chapters will explore this pattern in much more detail.

3.2 SQL Null Values

In relational databases, the special marker NULL represents the absence of a value. It
does not mean zero, an empty string, or a default value; rather, it indicates that the value
is unknown, not applicable, or missing. Understanding how NULL behaves is essential,
because it affects comparisons, logical conditions, aggregates, and constraints in ways
that often surprise new practitioners.
From a logical perspective, NULL introduces a third truth value — UNKNOWN — in ad-
dition to TRUE and FALSE. SQL therefore uses a three-valued logic for evaluating pred-
icates.

NULL as “Unknown” or “Missing”

Consider an Employees table with a column PhoneNumber. For some employees, this
value may not be recorded yet. Instead of inventing a placeholder such as ’N/A’ or an
empty string, the database can store NULL, indicating that the phone number is currently
unknown.

Vo Hoang Nhat Khang 41

3 Modifying Data

EmployeeID Name PhoneNumber

1 Alice Thompson ’+49-555-123’
2 David Li NULL
3 Maria Gomez ’+44-20-555’

Here, NULL for David Li does not imply “no phone”; it only indicates that the database
does not currently store a value.
Example: Comparing NULL to placeholder values
Some systems represent “unknown” information with special strings such as ’N/A’
or ’Unknown’ instead of NULL. In that case, a simple equality check PhoneNumber =
’N/A’ will work.
In contrast, when the missing value is represented by NULL, normal comparison
operators behave differently, and we must use IS NULL instead. This is one reason
why it is important to know how missing information is encoded in each table.

Why = NULL Does Not Work

A common mistake is to test for missing values using the equality operator:

-- This does NOT behave as intended

SELECT Name, PhoneNumber
FROM Employees
WHERE PhoneNumber = NULL;

In SQL, any comparison with NULL yields UNKNOWN, not TRUE or FALSE. As a result,
the predicate PhoneNumber = NULL never evaluates to true for any row, and the query
returns no results.
Instead, SQL provides two special predicates:

• IS NULL – tests whether a value is NULL,

• IS NOT NULL – tests whether a value is not NULL.

The correct way to find rows with missing phone numbers is:

SELECT Name, PhoneNumber

FROM Employees
WHERE PhoneNumber IS NULL;

To find rows where a phone number is available:

SELECT Name, PhoneNumber

FROM Employees
WHERE PhoneNumber IS NOT NULL;

Note: A quick mental rule

Whenever you find yourself writing column = NULL or column <> NULL, replace
it with column IS NULL or column IS NOT NULL instead. Equality and inequality
operators are for comparing actual values; IS and IS NOT are for testing presence

Vo Hoang Nhat Khang 42

 3.2 SQL Null Values

or absence of values.

Three-Valued Logic and Predicate Evaluation

Because of NULL, predicates in SQL can evaluate to three possible truth values:

• TRUE,

• FALSE,

• UNKNOWN (typically arising from comparisons involving NULL).

The presence of UNKNOWN affects logical operators such as AND, OR, and NOT. For ex-
ample:

• TRUE AND UNKNOWN is UNKNOWN,

• FALSE AND UNKNOWN is FALSE,

• TRUE OR UNKNOWN is TRUE,

• FALSE OR UNKNOWN is UNKNOWN,

• NOT UNKNOWN is UNKNOWN.

When evaluating a WHERE clause, only rows for which the predicate is TRUE are in-
cluded in the result. Rows for which the predicate evaluates to FALSE or UNKNOWN are
excluded. This is why, for instance, the predicate Salary > 70000 excludes rows with
Salary = NULL.
Note: UNKNOWN behaves like not included in WHERE
Although SQL distinguishes between FALSE and UNKNOWN, WHERE does not: both
lead to the row being omitted from the result. When debugging a query, it can be
helpful to remember that unknown conditions are filtered out just like explicitly false
ones.

NULL in Comparisons and Expressions

Comparisons and expressions involving NULL behave differently from those involving
ordinary values:

• 5 = NULL yields UNKNOWN, not FALSE.

• ’Alice’ <> NULL yields UNKNOWN.

• NULL + 1 typically yields NULL.

This means that arithmetic or string expressions that include NULL will often prop-
agate NULL. For example:

Vo Hoang Nhat Khang 43

3 Modifying Data

SELECT Name,
BaseSalary,
Bonus,
BaseSalary + Bonus AS TotalCompensation
FROM Employees;

If Bonus is NULL for some employees, the computed TotalCompensation will also be
NULL for those rows, unless we explicitly replace NULL with a suitable default.
Example: Propagating NULL through expressions
Consider an Invoices table with Amount and TaxAmount. If TaxAmount is sometimes
unknown:

SELECT InvoiceID,
Amount,
TaxAmount,
Amount + TaxAmount AS TotalWithTax
FROM Invoices;

For rows where TaxAmount is NULL, the expression Amount + TaxAmount yields
NULL. If your report needs a numeric total in every row, you will need to handle
NULL explicitly.

Handling NULL with COALESCE and Similar Functions

Most SQL dialects provide functions to substitute default values when encountering
NULL. A widely supported function is COALESCE, which returns the first non-NULL value
in its argument list:

SELECT Name,
COALESCE(PhoneNumber, 'Unknown') AS DisplayPhone
FROM Employees;

If PhoneNumber is NULL, the query returns the string ’Unknown’ instead. This is often
useful in reports or user interfaces that should avoid displaying NULL directly.
Some systems also support vendor-specific functions such as IFNULL or ISNULL. The
general idea is the same: provide a way to replace missing values with a more infor-
mative placeholder.
Example: Treating missing bonuses as zero
If a missing Bonus should be treated as zero for reporting purposes, we can write:

SELECT Name,
BaseSalary,
COALESCE(Bonus, 0) AS BonusOrZero,
BaseSalary + COALESCE(Bonus, 0) AS TotalCompensation
FROM Employees;

This does not change the stored data, but it provides a clear rule for how missing
bonuses should be interpreted in this particular query.

Vo Hoang Nhat Khang 44

 3.2 SQL Null Values

NULL and Aggregate Functions

Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX typically ignore NULL values
in their input. For example, consider the following subset:

EmployeeID Bonus
1 5000
2 NULL
3 3000
4 NULL

The following query:

SELECT COUNT(Bonus) AS CountBonus,

SUM(Bonus) AS TotalBonus,
AVG(Bonus) AS AvgBonus
FROM Employees;

treats only the non-NULL values (5000 and 3000) as contributing to the aggregates. In
contrast:

SELECT COUNT(*) AS RowCount

FROM Employees;

counts all rows, regardless of whether Bonus is NULL.

Note: COUNT(*) vs COUNT(column)
COUNT(*) counts rows, while COUNT(column) counts only the rows where that col-
umn is not NULL. When investigating missing data, comparing these two counts is
a quick way to see how many values are absent for a given column.

Sorting and Grouping with NULL

When using ORDER BY, rows with NULL values may appear either at the beginning or
at the end of the result, depending on the database system and sort direction. Some
systems provide explicit control via extensions such as:

ORDER BY HireDate ASC NULLS LAST;

When grouping, NULL values are treated as belonging to a single group. For exam-
ple:

SELECT Department, COUNT(*) AS CountEmployees

FROM Employees
GROUP BY Department;

will produce one group for each known department and, if present, an additional
group where Department is NULL.

Vo Hoang Nhat Khang 45

3 Modifying Data

Example: Grouping with a NULL category

Suppose Products has some rows where Category is NULL. The query:

SELECT Category, COUNT(*) AS ProductCount

FROM Products
GROUP BY Category;

might produce an output where one of the groups has Category = NULL. This
group collects all products whose category is currently unknown.

Constraints and NULL

Constraints interact with NULL in specific ways:

• A column declared NOT NULL cannot contain NULL values. Any attempt to insert
or update a row with NULL in such a column will fail.

• UNIQUE constraints typically allow multiple NULL values, because NULL is consid-
ered unknown and not equal to itself. However, details may vary by system.

• Foreign key columns are often allowed to be NULL, indicating that no related row
exists or has been chosen yet.

Note: UNIQUE and multiple NULLs

It may seem surprising that a column with a UNIQUE constraint can still contain
more than one NULL. The usual interpretation is that each NULL represents an un-
known value, and the database cannot assume that two unknowns are equal. If
you truly need at most one missing value, the constraint will need to be modeled
more explicitly.

Modeling Considerations
While NULL is a powerful mechanism for representing missing data, it should be used
deliberately. Excessive or uncontrolled use of NULL can make queries harder to reason
about and may complicate integrity constraints. In some designs, it may be prefer-
able to use separate tables to represent optional attributes or to choose explicit sentinel
values when appropriate and meaningful.

Caution: When too many NULLs are a design smell

If most columns in a table are allowed to be NULL, or if many rows have NULL in
most columns, it may be a sign that the schema is trying to represent several differ-
ent kinds of entities in a single table. In such cases, reorganizing the schema (for
example, by splitting the table into more focused tables) can sometimes make both
the data and the queries much simpler.

Vo Hoang Nhat Khang 46

 3.3 SQL Update

3.3 SQL Update

While INSERT INTO adds new rows to a table, the UPDATE statement modifies the values
of existing rows. In most operational databases, data changes over time: employees
move between departments, product prices are adjusted, and order statuses are up-
dated. The UPDATE command is the primary tool in SQL for reflecting such changes in
place.
From a relational perspective, an UPDATE operation takes an existing relation and
produces a new version in which one or more attributes of certain tuples have been
altered, subject to the tables integrity constraints.

Basic Form of UPDATE

The general pattern of an UPDATE statement is:

UPDATE TableName
SET column1 = value1,
column2 = value2,
...
WHERE condition;

The SET clause specifies which columns should be modified and what their new
values should be. The WHERE clause determines which rows are affected.
For example, suppose we have an Employees table with columns Name, Department,
and Salary. To give all employees in the Engineering department a salary increase of
5%, we might write:

UPDATE Employees
SET Salary = Salary * 1.05
WHERE Department = 'Engineering';

This statement updates only those rows whose Department is ’Engineering’. All
other rows remain unchanged.
Example: Checking affected rows before updating
A safe habit is to first run the WHERE condition with a SELECT:

-- Step 1: inspect which rows will be affected

SELECT Name, Department, Salary
FROM Employees
WHERE Department = 'Engineering';

-- Step 2: apply the update when you are satisfied

UPDATE Employees
SET Salary = Salary * 1.05
WHERE Department = 'Engineering';

This simple two-step pattern greatly reduces the risk of updating the wrong set of
rows, especially when you are still exploring a new dataset.

Vo Hoang Nhat Khang 47

3 Modifying Data

Updating Multiple Columns

The SET clause can assign new values to multiple columns in the same statement. Con-
sider an Orders table with columns OrderID, Status, and ShippedDate. To mark a
particular order as shipped and record the date:

UPDATE Orders
SET Status = 'Shipped',
ShippedDate = '2025-03-15'
WHERE OrderID = 1024;

Each assignment in the SET clause is evaluated for every row that satisfies the WHERE
condition. The order of assignments within SET is generally not significant, though
some systems allow later expressions to reference values assigned earlier.
Note: Using keys to target specific rows
When updating a single logical entity (such as one employee or one order), it is
usually best to filter by a key such as EmployeeID or OrderID, rather than by non-
unique attributes like Name or Status. This reduces the chance of accidentally mod-
ifying multiple rows.

UPDATE Without a WHERE Clause

If the WHERE clause is omitted, the UPDATE statement applies to all rows in the table:

UPDATE Products
SET UnitPrice = UnitPrice * 1.10;

This statement increases the price of every product by 10%. While such global up-
dates are sometimes intentional (for example, applying a site-wide price adjustment),
omitting the WHERE clause by accident can cause significant damage.
As a defensive programming practice, it is common to:

• First run a SELECT query with the same WHERE condition to verify which rows will
be affected.

• Use transactions so that unintended changes can be rolled back.

Caution: Accidentally updating every row

One of the most common mistakes with UPDATE is forgetting the WHERE clause:

UPDATE Employees
SET Salary = 0; -- Oops: applies to ALL employees

In a production database, this can be very costly to repair. When writing updates,
it is worth pausing to confirm that the WHERE clause is present and reflects exactly
the set of rows you intend to change.

Vo Hoang Nhat Khang 48

 3.3 SQL Update

Expressions, Functions, and Column References

The right-hand side of an assignment in the SET clause need not be a simple literal. It
can be an expression involving:
• arithmetic (e.g., Salary * 1.05),
• string manipulation (e.g., UPPER(Name)),
• date and time functions,
• or references to other columns in the same row.
For example, to normalize product names to a consistent format:
UPDATE Products
SET ProductName = TRIM(ProductName);

Or to move all employees from an old department name to a new one:

UPDATE Employees
SET Department = 'Data Science'
WHERE Department = 'Analytics';

Expressions are evaluated per row, using the current values of the columns before
the update is applied.
Example: Incrementing values and logging the change
Suppose we want to increase all bonuses in the Sales department by 500:

UPDATE Employees
SET Bonus = Bonus + 500
WHERE Department = 'Sales';

If Bonus is NULL for some employees, this expression may yield NULL as well. In that
case, an explicit COALESCE can be used:

UPDATE Employees
SET Bonus = COALESCE(Bonus, 0) + 500
WHERE Department = 'Sales';

This treats missing bonuses as zero before applying the increase.

Setting Values to NULL

The UPDATE statement can also assign NULL to columns that allow it, indicating that a
value is now unknown or not applicable. For instance, if a product is discontinued and
its future delivery date is no longer meaningful:
UPDATE Products
SET NextDeliveryDate = NULL
WHERE ProductID = 205;

Whether NULL is allowed depends on the columns definition. Columns declared

NOT NULL cannot be set to NULL; attempting to do so will result in an error.

Vo Hoang Nhat Khang 49

3 Modifying Data

Interaction with Constraints

As with INSERT, every UPDATE operation must respect the integrity constraints defined
on the table:

• NOT NULL constraints may prevent setting certain columns to NULL.

• UNIQUE constraints may prevent assigning values that already exist in other
rows.

• CHECK constraints may reject updates that violate specified conditions.

• FOREIGN KEY constraints may disallow changes that would break referential
integrity.

For example, if DepartmentID in an Employees table references a Departments table,

an attempt to change DepartmentID to a value not present in Departments will fail,
unless the foreign key is nullable and the assignment is to NULL.
Note: Constraint errors during UPDATE
When an UPDATE fails because of a constraint, the error message is a clue about
the data model. A UNIQUE violation tells you that the new value is already used
elsewhere; a foreign key error suggests that the new reference does not exist. Treat
these errors as information about the rules of the schema rather than just obstacles
to get around.

UPDATE with Joins (Dialect-Dependent, Preview)

Many SQL dialects support updating rows based on information from another table
using a join-like syntax. Although not part of the original SQL standard, this pattern
is common in practice. For example, to copy the City and Country information from a
Customers table into a denormalized Orders table:

UPDATE Orders AS o
SET City = [Link],
Country = [Link]
FROM Customers AS c
WHERE [Link] = [Link];

The exact syntax varies between systems (e.g., some use a different placement of
FROM), but the underlying idea is the same: use relationships between tables to deter-
mine both WHERE and SET expressions.
At this stage, you can think of this as a preview of how UPDATE can interact with
joins. Later chapters on joins and data migration will revisit this pattern in more detail.

UPDATE and Transactions

UPDATE statements often appear within transactions that group multiple changes into
a single logical unit of work. For example, updating an orders status might be accom-
panied by an inventory adjustment and a log entry. In such cases:

Vo Hoang Nhat Khang 50

 3.4 SQL Delete

• Either all updates are committed together, or

• All of them are undone if an error occurs.

Although a full discussion of transactional semantics (ACID properties, isolation

levels) is deferred to a later chapter, it is important to recognize that UPDATE operations
rarely occur in isolation in production systems.
Caution: Practice updating in a safe environment
When you are learning or designing new UPDATE statements, it is best to experiment
in a test database or within a transaction that you can roll back. This lets you build
confidence in your conditions and expressions without risking permanent changes
to important data.

3.4 SQL Delete

Where INSERT INTO adds new rows and UPDATE modifies existing ones, the DELETE
statement removes rows from a table. Deletion is a fundamental operation in data
management: orders are cancelled, products are discontinued, and temporary records
are cleared. Because deletions are often irreversible, understanding DELETE and using
it carefully is essential.
From a relational perspective, a DELETE operation produces a new version of a re-
lation in which certain tuples have been removed, subject to referential and other in-
tegrity constraints.

Basic Form of DELETE

The general pattern of a DELETE statement is:
DELETE FROM TableName
WHERE condition;

The WHERE clause specifies which rows should be removed. For example, to delete
all employees in a temporary test department:
DELETE FROM Employees
WHERE Department = 'Test';

Only rows for which the condition Department = ’Test’ evaluates to true are deleted.
All other rows remain unchanged.
Example: Previewing rows before deleting
A safe pattern is to run a corresponding SELECT before executing DELETE:
-- Step 1: preview affected rows
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department = 'Test';

-- Step 2: perform the deletion

Vo Hoang Nhat Khang 51

3 Modifying Data

DELETE FROM Employees

WHERE Department = 'Test';

This makes the deletion less mysterious: you can see exactly which rows will dis-
appear before you execute the destructive statement.

DELETE Without a WHERE Clause

If the WHERE clause is omitted, DELETE removes all rows from the table:

DELETE FROM Employees;

After this statement, the table Employees still exists, but it is empty. This behavior
is sometimes used intentionally (for example, to clear a staging table), but forgetting
the WHERE clause can be disastrous in production environments.
As with UPDATE, a common safety practice is:

• First run a SELECT query with the same WHERE condition to check which rows
would be affected.

• Use transactions so that a mistaken DELETE can be rolled back.

Caution: The missing WHERE clause problem
The following two statements are only one line apart:

DELETE FROM Orders

WHERE Status = 'Test';

DELETE FROM Orders; -- no WHERE: removes all rows

A misplaced cursor or a copy–paste mistake can easily lead to the second statement
running instead of the first. Using transactions and preview queries is your best
defense against this kind of error.

Examples of Conditional Deletion

Deleting based on numeric conditions. Suppose we have a Products table and we
want to remove discontinued items with very low prices:

DELETE FROM Products

WHERE Discontinued = 1
AND UnitPrice < 2.00;

Deleting based on dates. Given an Orders table, we might periodically remove test
orders created long ago:

DELETE FROM Orders

WHERE IsTestOrder = 1
AND OrderDate < '2023-01-01';

Vo Hoang Nhat Khang 52

 3.4 SQL Delete

Deleting rows with NULL in key columns. If, due to data import issues, some rows
have missing customer identifiers:

DELETE FROM Orders

WHERE CustomerID IS NULL;

In each case, the WHERE clause acts as a precise filter controlling which rows are
removed.
Example: Combining several criteria for cleanup
Consider a periodic cleanup of a log table:

DELETE FROM EventLog

WHERE EventDate < '2024-01-01'
AND Severity = 'DEBUG'
AND UserID IS NULL;

Here, we remove only old, low-severity events that are not associated with a known
user. All other events are retained, even if they match some but not all of the con-
ditions.

Interaction with Constraints and Foreign Keys

DELETE must respect all constraints defined on the table, particularly foreign key con-
straints. If other tables reference the row being deleted, the database must decide what
to do with those dependent rows. Depending on how the foreign key is defined, several
behaviors are possible:

• RESTRICT / NO ACTION: prevent deletion if dependent rows exist.

• ON DELETE CASCADE: automatically delete dependent rows.

• ON DELETE SET NULL: set the foreign key in dependent rows to NULL.

For example, if [Link] references [Link] and the con-

straint is defined with ON DELETE RESTRICT, then:

DELETE FROM Customers

WHERE CustomerID = 42;

will fail if there are any orders associated with customer 42. By contrast, with ON
DELETE CASCADE, deleting the customer would also delete all of their orders.
These behaviors are specified at schema definition time and have profound impli-
cations for how deletions propagate through the database.
Note: Understanding delete behavior before relying on it
Before performing deletions that might affect related tables, it is important to know
how the foreign keys are defined. In some schemas, deleting a row in a parent table
will automatically remove child rows; in others, the deletion will be blocked until
the child rows are removed explicitly.

Vo Hoang Nhat Khang 53

3 Modifying Data

DELETE Versus TRUNCATE

Many database systems provide a separate command such as TRUNCATE TABLE to quickly
remove all rows from a table:

TRUNCATE TABLE Employees;

Unlike DELETE without a WHERE clause, TRUNCATE:

• Often bypasses row-by-row logging for performance.

• May reset identity or auto-increment counters.
• Typically cannot include a WHERE clause.

However, TRUNCATE is more restricted (for example, it may be disallowed if foreign

keys reference the table). In conceptual discussions focused on SQL semantics, DELETE
remains the primary tool.

DELETE and Transactions

Because deletions can be destructive, they are frequently performed within transac-
tions. A typical workflow might be:

BEGIN TRANSACTION;

DELETE FROM Orders

WHERE OrderDate < '2020-01-01'
AND Status = 'Test';

-- Inspect the number of affected rows or run diagnostic queries.

COMMIT; -- or ROLLBACK; if the result is not as expected

Using transactions, especially in development and staging environments, provides

a safety net for complex or large-scale deletions.
Example: Dry-run pattern with transactions
In some systems you can explicitly start a transaction, run a DELETE statement, and
then query the table to inspect the effect:
BEGIN TRANSACTION;

DELETE FROM Logs

WHERE EventDate < '2023-01-01';

-- Check the impact

SELECT COUNT(*) FROM Logs;

-- Decide whether to keep or undo

ROLLBACK; -- or COMMIT;

This pattern is a practical way to build confidence in your deletion logic before

Vo Hoang Nhat Khang 54

 3.4 SQL Delete

making changes permanent.

Soft Deletes: An Alternative Pattern

In some applications, permanent deletion is undesirable because historical data must
be preserved for auditing, analytics, or regulatory reasons. A common alternative is
the soft delete pattern: rather than removing rows, a flag is updated to mark them as
inactive.
For example, an IsDeleted (or Active) column might be used:

UPDATE Customers
SET IsDeleted = 1
WHERE CustomerID = 42;

Queries that should ignore deleted customers include a condition such as:

WHERE IsDeleted = 0;

Soft deletes are not a replacement for DELETE, but they illustrate how logical deletion
can be separated from physical removal when long-term retention is important.
Caution: Trade-offs of soft deletes
Soft deletes preserve history, but they also add complexity: every query that should
ignore deleted rows must remember to filter on the delete flag. Over time, this can
lead to inconsistent behavior if some queries forget the filter. Whether to use soft
deletes, hard deletes, or a combination of both is ultimately a design choice driven
by the requirements of the application.

Vo Hoang Nhat Khang 55

3 Modifying Data

Vo Hoang Nhat Khang 56

Chapter 4 Functions and Calculations

4.1 SQL Select Top

In many situations, we are not interested in the entire result of a query, but only in a
limited number of rows: the first few records for inspection, the highest-scoring items,
or a sample of data for exploratory analysis. SQL supports this pattern through con-
structs that restrict the number of rows returned.
In several database systems, notably SQL Server and MS Access, this behavior is
expressed using the TOP keyword in the SELECT clause. Other systems (such as MySQL
and PostgreSQL) use different syntax (e.g., LIMIT or FETCH FIRST), but the conceptual
idea is the same: limit the size of the result set.

Basic Use of SELECT TOP

In SQL Server–style syntax, the basic form is:

SELECT TOP n column1, column2, ...

FROM TableName;

where n is the maximum number of rows to return. For example, to retrieve the first
five rows from a Products table:

SELECT TOP 5 ProductID, ProductName, UnitPrice

FROM Products;

Without an ORDER BY clause, the choice of which rows count as the top n is not well-
defined from a logical standpoint. The database may return rows in whatever internal
order it finds convenient. As a result, meaningful use of TOP almost always goes hand
in hand with ORDER BY.
Note: Top is undefined without ORDER BY
If no ORDER BY is specified, neither the SQL language nor most database systems
guarantee which rows you will see when using TOP or LIMIT. Even if the result looks
sorted in practice, that behavior can change after an index or query plan change.
For reproducible results, always pair TOP/LIMIT with an explicit ORDER BY.

TOP with ORDER BY

To obtain a deterministic and semantically meaningful subset, TOP should be combined
with ORDER BY. For example, to retrieve the three most expensive products:

Vo Hoang Nhat Khang 57

4 Functions and Calculations

SELECT TOP 3 ProductName, UnitPrice

FROM Products
ORDER BY UnitPrice DESC;

Here, the ordering expresses a clear intention: list products in descending order of
price, then return only the first three rows from that ordered result. Conceptually, the
database:

1. Evaluates the FROM and WHERE clauses to produce an intermediate set of rows,

2. Sorts that set according to ORDER BY,

3. Truncates the ordered result to the first n rows as requested by TOP.

The same pattern can be used for smallest values, earliest dates, or scores according
to a custom expression:

SELECT TOP 10 CustomerID, TotalSpent

FROM CustomerSummary
ORDER BY TotalSpent DESC;

Example: Earliest orders in a given year

To inspect the first five orders placed in 2025:

SELECT TOP 5 OrderID, CustomerID, OrderDate

FROM Orders
WHERE OrderDate >= '2025-01-01'
ORDER BY OrderDate ASC, OrderID ASC;

The ORDER BY clause makes the notion of first explicit (earliest date, and then lowest
OrderID for ties).

TOP PERCENT and WITH TIES (Dialect-Specific)

Some systems extend TOP with additional options. Two common ones in SQL Server
are:

• TOP (n) PERCENT – returns approximately n percent of the rows,

• WITH TIES – includes additional rows that tie with the last row according to ORDER
BY.

For example, to retrieve the top 5% most expensive products:

SELECT TOP (5) PERCENT ProductName, UnitPrice

FROM Products
ORDER BY UnitPrice DESC;

To retrieve the three highest-priced products, including any additional products

whose price matches the third highest:

Vo Hoang Nhat Khang 58

 4.1 SQL Select Top

SELECT TOP (3) WITH TIES ProductName, UnitPrice

FROM Products
ORDER BY UnitPrice DESC;
If the third most expensive product has a price of 25.00 and there are multiple prod-
ucts with that same price, WITH TIES ensures that all of them are included, even if this
results in more than three rows.
Note: Using TOP (n) PERCENT for top slices
TOP (n) PERCENT is often used when you want a proportion rather than a fixed
count: top 1% of customers or top 10% of products by sales. Under the hood, the
database must first estimate how many rows correspond to that percentage, and
then apply the usual TOP logic on the ordered result.

Equivalent Constructs in Other SQL Dialects

Although TOP is widely used, it is not part of the original SQL standard, and many
database systems implement alternative syntax. Conceptually, however, these con-
structs all perform similar roles.

MySQL and PostgreSQL: LIMIT (and OFFSET). In MySQL and PostgreSQL, the
following:
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
LIMIT 3;
is analogous to:
SELECT TOP 3 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
These systems also support pagination using OFFSET, for example:
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
LIMIT 10 OFFSET 20;
which returns 10 rows starting from the 21st row in the ordered result.

Standard-style FETCH FIRST. More recent versions of the SQL standard, and some
engines (such as DB2 and newer PostgreSQL releases), support:
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
FETCH FIRST 3 ROWS ONLY;
From a conceptual standpoint, all of these mechanisms provide a way to restrict the
cardinality of the result set after ordering.

Vo Hoang Nhat Khang 59

4 Functions and Calculations

Example: Pagination with LIMIT and OFFSET (MySQL/PostgreSQL style)

To fetch the second page of results, with 20 rows per page:

SELECT ProductName, UnitPrice

FROM Products
ORDER BY ProductName ASC
LIMIT 20 OFFSET 20;

Here, the first 20 rows (offset 0) form page 1; the next 20 rows (offset 20) form page
2, and so on.

Using TOP for Sampling and Exploration

During exploratory analysis, it is common to inspect only a handful of rows to under-
stand the shape of the data. For example:

SELECT TOP 10 *
FROM Orders;

returns a small sample from the Orders table. Although this is useful in practice, one
should remember that without ORDER BY, the rows returned may not be representa-
tive of any particular logical ordering. For more controlled sampling, some systems
provide randomization functions or specialized sampling clauses.
Note: TOP for peeking at tables
A pattern you will use frequently when learning a new schema is:

SELECT TOP 10 *
FROM SomeTable;

Even though the rows are not ordered, this quick peek can be enough to see col-
umn names, typical values, and rough data types before you design more precise
queries.

TOP in Combination with Aggregates

The TOP construct is frequently used together with aggregate functions and grouping.
For instance, to find the five customers with the highest total spending:

SELECT TOP 5 CustomerID,

SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
ORDER BY TotalSpent DESC;

Here, grouping and aggregation are performed first to compute TotalSpent per
customer, then ORDER BY sorts customers by this derived measure, and finally TOP 5
restricts the result to the top five.

Vo Hoang Nhat Khang 60

 4.2 SQL Aggregate Functions

Example: Top categories by number of products

To retrieve the three categories with the largest number of products:

SELECT TOP 3 Category,

COUNT(*) AS ProductCount
FROM Products
GROUP BY Category
ORDER BY ProductCount DESC;

This query combines grouping, aggregation, ordering, and TOP to answer a very
natural analytical question: Which categories are largest by product count?

Modeling Intent
From a modeling perspective, SELECT TOP (and its equivalents) is best understood not
as a fundamental relational operation, but as a pragmatic extension that reflects how
query results are consumed: in pages, top-k lists, dashboards, and user interfaces that
display limited subsets at a time.
It is therefore helpful to separate two questions when designing queries:

• What is the logical result set you want (defined by FROM, WHERE, GROUP BY, and
ORDER BY)?

• How many rows from that ordered result do you actually need to see or process
right now?

TOP, LIMIT, and related constructs answer the second question while leaving the
first one to the core of the query.

4.2 SQL Aggregate Functions

So far, our queries have primarily operated at the level of individual rows: each row is
either included or excluded, and selected columns are returned as-is or with simple ex-
pressions. In many analytical tasks, however, we are interested not in individual rows,
but in summaries of sets of rows: totals, averages, counts, minimums, and maximums.
SQL provides a family of aggregate functions for this purpose. An aggregate function
takes a collection of values (typically derived from a column over many rows) and
returns a single, summarized value. Common examples include:

• MIN() – smallest value in a set,

• MAX() – largest value in a set,

• COUNT() – number of values (or rows),

• SUM() – total of numeric values,

• AVG() – arithmetic mean of numeric values.

Vo Hoang Nhat Khang 61

4 Functions and Calculations

Aggregate functions are often used together with GROUP BY, which partitions rows
into groups and applies the aggregate to each group separately. They also appear in
scalar contexts (for example, to compute the maximum salary in the entire table).
Unless noted otherwise, standard aggregate functions ignore NULL values in their
input: a missing value does not contribute to the computation, though it still affects
COUNT(*). This behavior is important when working with incomplete data.
Note: Aggregates summarize sets of rows
A useful way to think about aggregates is: SELECT normally returns one result per
row; aggregates return one result per set of rows. With GROUP BY, each group becomes
a separate mini-table, and the aggregate summarizes that mini-table.

4.2.1 SQL Min and Max

The functions MIN() and MAX() return, respectively, the smallest and largest value from
a set of values. They can be applied to numeric, date/time, and even character data
types, depending on the database system and the underlying ordering rules.
At a conceptual level, given a multiset of values:

{v1 , v2 , . . . , vn },

MIN() returns the least value according to the type’s ordering, and MAX() returns the
greatest, ignoring any NULL values.

Basic Use of MIN and MAX

The simplest use of MIN() and MAX() is to compute extremal values over an entire col-
umn. For example, consider a Products table:

ProductID ProductName UnitPrice Category

101 Arabica Coffee Beans 12.50 Beverages
102 Dark Chocolate Bar 3.75 Confectionery
103 Jasmine Green Tea 9.20 Beverages
104 Organic Olive Oil 18.95 Pantry
105 Espresso Roast Coffee 11.40 Beverages

To find the minimum and maximum price across all products:

SELECT MIN(UnitPrice) AS MinPrice,

MAX(UnitPrice) AS MaxPrice
FROM Products;

The result might be:

MinPrice MaxPrice
3.75 18.95

Here, MIN(UnitPrice) returns the smallest price, and MAX(UnitPrice) returns the
largest.

Vo Hoang Nhat Khang 62

 4.2 SQL Aggregate Functions

Example: Checking bounds for data sanity

A quick way to sanity check data loads is to look at extremal values:

SELECT MIN(OrderDate) AS EarliestOrder,

MAX(OrderDate) AS LatestOrder
FROM Orders;

If the earliest order date is accidentally in the year 1900, or the latest order date is
far in the future, that may signal issues with default values or parsing errors during
import.

Filtering Before Aggregation

Aggregate functions are applied after the WHERE clause filters rows. This means we can
compute minimums and maximums over subsets of the data by adding conditions:

SELECT MIN(UnitPrice) AS MinBeveragePrice,

MAX(UnitPrice) AS MaxBeveragePrice
FROM Products
WHERE Category = 'Beverages';

Only rows where Category = ’Beverages’ are considered when computing the
aggregates.
Similarly, to find the maximum salary among employees in Engineering:

SELECT MAX(Salary) AS MaxEngineeringSalary

FROM Employees
WHERE Department = 'Engineering';

MIN and MAX with GROUP BY

When used with GROUP BY, MIN() and MAX() compute extremal values within each group
rather than across the entire table. For example, to find the minimum and maximum
price per category in the Products table:

SELECT Category,
MIN(UnitPrice) AS MinPrice,
MAX(UnitPrice) AS MaxPrice
FROM Products
GROUP BY Category;

Conceptually, the database engine:

1. Partitions rows into groups based on Category,

2. Within each group, computes MIN(UnitPrice) and MAX(UnitPrice),

3. Returns one row per group with the aggregated values.

The result might look like:

Vo Hoang Nhat Khang 63

4 Functions and Calculations

Category MinPrice MaxPrice

Beverages 9.20 12.50
Confectionery 3.75 3.75
Pantry 18.95 18.95

This pattern is common when summarizing data by category, region, time period,
or other dimensions.

MIN and MAX on Non-Numeric Types

MIN() and MAX() are not limited to numeric columns. They can also be applied to:

• Date and time columns, where MIN() yields the earliest date and MAX() the latest,

• Character columns, where ordering is typically lexicographic according to the

databases collation rules.

For example, to find the earliest and latest order dates:

SELECT MIN(OrderDate) AS FirstOrderDate,

MAX(OrderDate) AS LastOrderDate
FROM Orders;

Or to find the alphabetically first and last customer names:

SELECT MIN(CustomerName) AS FirstName,

MAX(CustomerName) AS LastName
FROM Customers;

The precise ordering for character data depends on collation settings (case sensi-
tivity, accent handling, locale), which may differ between systems.

Interaction with NULL Values

As with other aggregate functions, MIN() and MAX() ignore NULL values in their input.
If a column contains a mixture of numeric values and NULLs, only the non-NULL values
are considered when computing the minimum or maximum.
For example, suppose we extend the Products table with some unknown prices:

ProductID ProductName UnitPrice

201 Sample Product A 5.00
202 Sample Product B NULL
203 Sample Product C 9.50

Then:

SELECT MIN(UnitPrice) AS MinPrice,

MAX(UnitPrice) AS MaxPrice
FROM Products;

ignores the NULL in computing MinPrice and MaxPrice. If all values are NULL, the result
of MIN() and MAX() is typically NULL as well.

Vo Hoang Nhat Khang 64

 4.2 SQL Aggregate Functions

Note: MIN/MAX answer what value?, not which row?

MIN() and MAX() return values, not rows. If you need to know which product has
the maximum price (including its name or ID), you will often combine ordering
with TOP/LIMIT rather than relying on the aggregate alone.

MIN/MAX and ORDER BY

A common pattern in practice is to use ORDER BY together with TOP (or LIMIT) instead
of MIN() and MAX(). For example, to retrieve the product with the highest price:

SELECT TOP 1 ProductID, ProductName, UnitPrice

FROM Products
ORDER BY UnitPrice DESC;

This differs from using MAX(UnitPrice) in that it returns the entire row (or rows)
associated with the maximum value, not just the maximum itself. Both patterns are
useful, but they serve slightly different goals:

• MAX(UnitPrice) answers the question What is the largest price?,

• TOP 1 ... ORDER BY UnitPrice DESC answers Which product has the largest
price?.

4.2.2 SQL Count

While MIN() and MAX() identify extremal values in a set, the COUNT() function answers
a more basic but equally important question: How many? In practice, COUNT() is one of
the most frequently used aggregate functions in SQL, appearing in queries that com-
pute record counts, frequencies, and group sizes.
At a high level, COUNT() returns the number of items in its input set, subject to two
important variations:

• COUNT(*) counts rows.

• COUNT(expression) counts non-NULL values of the expression.

Understanding this distinction - especially in the presence of NULL - is crucial for

interpreting results correctly.

COUNT(*) Versus COUNT(column)

The simplest form, COUNT(*), returns the number of rows in the result after any WHERE
filtering has been applied:

SELECT COUNT(*) AS TotalProducts

FROM Products;

Here, COUNT(*) includes every row, regardless of whether any particular column is
NULL.
By contrast, COUNT(column) counts only those rows for which the specified column
is not NULL:

Vo Hoang Nhat Khang 65

4 Functions and Calculations

SELECT COUNT(UnitPrice) AS PricedProducts

FROM Products;

To see the difference, consider the following illustrative subset:

ProductID ProductName UnitPrice

1 Sample Product A 5.00
2 Sample Product B NULL
3 Sample Product C 9.50

Then:

SELECT COUNT(*) AS RowCount,

COUNT(UnitPrice) AS CountUnitPrice
FROM Products;

would produce:

RowCount CountUnitPrice
3 2

COUNT(*) reports three rows, while COUNT(UnitPrice) counts only the two non-NULL
prices.
Example: Estimating missing values with COUNT
A common pattern to measure missingness is:

SELECT COUNT(*) - COUNT(UnitPrice) AS MissingPriceCount

FROM Products;

This subtracts the number of known prices from the total number of rows, yielding
exactly how many products currently lack a price.

Counting with Conditions

As with other aggregates, COUNT() is computed after the WHERE clause has filtered rows.
To count only products in a particular category, we can write:

SELECT COUNT(*) AS BeverageCount

FROM Products
WHERE Category = 'Beverages';

Here, the WHERE clause restricts the input set to beverage products, and COUNT(*)
counts how many remain.
Similarly, to count employees with a recorded phone number:

SELECT COUNT(PhoneNumber) AS EmployeesWithPhone

FROM Employees;

Since COUNT(PhoneNumber) ignores NULL values, only employees with a non-NULL

PhoneNumber contribute to the count.

Vo Hoang Nhat Khang 66

 4.2 SQL Aggregate Functions

COUNT(DISTINCT ...)
The COUNT() function can also be combined with DISTINCT to count the number of
unique non-NULL values in a column. This is often used to measure cardinality, such as
the number of distinct customers or categories:

SELECT COUNT(DISTINCT Category) AS DistinctCategories

FROM Products;

This query returns the number of distinct Category values. Any NULL categories
are ignored when counting distinct values.
Another common use case is counting distinct customers who have placed an order:

SELECT COUNT(DISTINCT CustomerID) AS CustomersWithOrders

FROM Orders;

This does not count orders directly; it counts how many different customers appear
in the Orders table.

COUNT with GROUP BY

When used with GROUP BY, COUNT() computes the size of each group. For example, to
count how many products exist in each category:

SELECT Category,
COUNT(*) AS ProductCount
FROM Products
GROUP BY Category;

Conceptually, the database engine:

1. Partitions rows into groups based on Category,

2. Counts the number of rows in each group,

3. Returns one row per category with the group size.

An example result might be:

Category ProductCount
Beverages 3
Confectionery 1
Pantry 1

Similarly, to count how many orders each customer has placed:

SELECT CustomerID,
COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID;

Vo Hoang Nhat Khang 67

4 Functions and Calculations

COUNT and NULL Revisited

Because COUNT(*) and COUNT(column) treat NULL differently, choosing the appropriate
form depends on the question being asked:

• Use COUNT(*) when you want to know how many rows satisfy a condition, re-
gardless of whether specific columns are missing.

• Use COUNT(column) when you want to know how many rows have a known value
in that column.

For example, in an Employees table:

SELECT COUNT(*) AS TotalEmployees,

COUNT(PhoneNumber) AS EmployeesWithPhone
FROM Employees;

gives a quick summary of data completeness for the PhoneNumber attribute.

Note: COUNT is evaluated after WHERE

The question count of what? is answered by the combination of WHERE (which se-
lects rows) and the form of COUNT() (which decides how those rows are counted).
Changing the WHERE clause can dramatically change the meaning of the same
COUNT() expression.

COUNT in Practice

Because COUNT() is so widely used, small differences in its interpretation can have sig-
nificant practical consequences. Common patterns include:

• Validating expectations (e.g., How many rows were imported?).

• Checking referential coverage (e.g., How many orders have no associated cus-
tomer? using COUNT(*) over a LEFT JOIN).

• Driving pagination and user interfaces (e.g., total result counts).

• Supporting data quality checks (e.g., counting missing values via COUNT(*) -
COUNT(column)).

For instance, the number of rows where a particular column is NULL can be com-
puted as:

SELECT COUNT(*) - COUNT(PhoneNumber) AS MissingPhoneCount

FROM Employees;

Vo Hoang Nhat Khang 68

 4.2 SQL Aggregate Functions

4.2.3 SQL Sum

While COUNT() tells us how many rows or values satisfy a condition, the SUM() function
answers a complementary question: how much in total. It computes the arithmetic sum
of a set of numeric values, typically taken from a single column over many rows.
At a high level, given a multiset of numeric values
{v1 , v2 , . . . , vn },
SUM() returns the value
v1 + v2 + · · · + vn ,
ignoring any NULL values. If all values are NULL, the result is usually NULL rather than
zero, reflecting the absence of known data.

Basic Use of SUM

The simplest use of SUM() is to aggregate a numeric column over all rows in a table or
over rows that meet a particular condition. For example, consider an Orders table with
a column TotalAmount:
SELECT SUM(TotalAmount) AS TotalRevenue
FROM Orders;

This query returns the total revenue represented by all orders in the table.
We can restrict the input set using a WHERE clause. For instance, to compute revenue
for a specific year:
SELECT SUM(TotalAmount) AS Revenue2025
FROM Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01';

Here, SUM() is applied only to rows whose OrderDate falls within the year 2025.

Ignoring NULL Values

As with most aggregate functions, SUM() ignores NULL values in its input. Suppose we
have a subset of an Employees table:
EmployeeID Name Bonus
1 Alice Thompson 5000
2 David Li NULL
3 Maria Gomez 3000
Then:
SELECT SUM(Bonus) AS TotalBonus
FROM Employees;

returns 8000, not NULL and not 5000. The NULL bonus is simply omitted from the sum.
If all values are NULL, SUM() typically returns NULL. If a literal default (such as zero)
is desired in that case, a function like COALESCE can be used:
SELECT COALESCE(SUM(Bonus), 0) AS TotalBonus
FROM Employees;

Vo Hoang Nhat Khang 69

4 Functions and Calculations

Note: SUM answers how much over filtered rows

Because SUM() is applied after WHERE, the meaning of total amount always depends
on the filter. A small change in the WHERE clause can turn total revenue this year into
total revenue for one region, even if the SUM() expression itself does not change.

SUM with GROUP BY

SUM() becomes especially powerful when used with GROUP BY to compute totals per
group. For example, in an Orders table:
SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;
Conceptually, the database engine:
1. Partitions rows by CustomerID,
2. Computes the sum of TotalAmount within each partition,
3. Returns one row per customer with the aggregated total.
This pattern is common in reporting and analytics, where totals by customer, region,
category, or time period are essential.
Another example, using the Products table, is to compute total inventory value per
category:
SELECT Category,
SUM(UnitPrice * UnitsInStock) AS InventoryValue
FROM Products
GROUP BY Category;
Here, SUM() operates on an expression (UnitPrice * UnitsInStock), not just a col-
umn reference, illustrating that aggregates can summarize derived quantities as well.

Combining SUM with COUNT and AVG

SUM() is often used alongside COUNT() and AVG() to provide a more complete view of
a numeric attribute. For example, to analyze bonuses:
SELECT COUNT(*) AS EmployeeCount,
COUNT(Bonus) AS WithBonusCount,
SUM(Bonus) AS TotalBonus,
AVG(Bonus) As AvgBonus
FROM Employees;
This query summarizes:
• how many employees there are,
• how many of them have a recorded bonus,
• the total amount spent on bonuses,
• and the average bonus among those with non-NULL values.
Such combinations are central to descriptive statistics in SQL.

Vo Hoang Nhat Khang 70

 4.2 SQL Aggregate Functions

Data Types and Overflow Considerations

The behavior of SUM() depends on the underlying data type of the expression being
aggregated. Summing many large values may lead to overflow if the chosen data type
cannot represent the result. For example, summing millions of large integers into a
small integer type can exceed its range.
Different database systems handle such situations differently:

• Some promote the result to a wider type (e.g., from INT to BIGINT).

• Others may raise an error when overflow occurs.

When designing schemas for financial or scientific applications, it is important to

choose appropriate numeric types and, where necessary, to consider numeric precision
and scale (e.g., using DECIMAL or NUMERIC types).

SUM in Practice
Typical uses of SUM() include:

• Calculating revenue, costs, or profits over a period.

• Summing quantities such as units sold, inventory levels, or hours worked.

• Aggregating scores, ratings, or other metrics.

For example, to compute monthly revenue:

SELECT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS MonthStart,

SUM(TotalAmount) AS MonthlyRevenue
FROM Orders
GROUP BY DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)
ORDER BY MonthStart;

(The exact date-handling functions vary by system, but the pattern is widely applicable.)

4.2.4 SQL Avg

The AVG() function computes the arithmetic mean of a set of numeric values. Con-
ceptually, it combines the ideas of SUM() and COUNT() by adding all contributing val-
ues and dividing by their number. Averages are ubiquitous in analysis: average order
value, average salary, average rating, and so on.
Given a multiset of numeric values

{v1 , v2 , . . . , vn },

the average is defined as

v1 + v2 + · · · + vn
,
n
with the important caveat that AVG() ignores NULL values in its input. Only non-NULL
values contribute to both the numerator and denominator.

Vo Hoang Nhat Khang 71

4 Functions and Calculations

Basic Use of AVG

The simplest form of AVG() aggregates a numeric column over all rows in a table (or
over rows that satisfy a given condition). For example, consider an Employees table
with a Salary column:

SELECT AVG(Salary) AS AvgSalary

FROM Employees;

This query returns the average salary across all employees whose Salary is not
NULL.
As with other aggregates, we can restrict the input using a WHERE clause. For in-
stance, to compute the average salary in the Engineering department:

SELECT AVG(Salary) AS AvgEngineeringSalary

FROM Employees
WHERE Department = 'Engineering';

Ignoring NULL Values

AVG() ignores NULL values, just as SUM() and COUNT(column) do. Consider the following
subset:
EmployeeID Name Bonus
1 Alice Thompson 5000
2 David Li NULL
3 Maria Gomez 3000

The query:

SELECT AVG(Bonus) AS AvgBonus

FROM Employees;

computes (5000 + 3000)/2 = 4000. The NULL value is excluded from both the sum and
the count. If all values of Bonus were NULL, the result of AVG(Bonus) would itself be
NULL.
If a literal default is desired when no non-NULL values exist, we can write:

SELECT COALESCE(AVG(Bonus), 0) AS AvgBonus

FROM Employees;

AVG with GROUP BY

Like other aggregate functions, AVG() is often used with GROUP BY to compute per-
group averages. For example, to compute the average salary by department:

SELECT Department,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

Conceptually, the database engine:

Vo Hoang Nhat Khang 72

 4.2 SQL Aggregate Functions

1. Partitions rows into groups according to Department,

2. For each group, sums the non-NULL salaries and divides by the number of non-NULL
salaries,
3. Returns one row per department with the resulting average.
Similarly, in an Orders table with a TotalAmount column, we can compute the av-
erage order value per customer:
SELECT CustomerID,
AVG(TotalAmount) AS AvgOrderValue
FROM Orders
GROUP BY CustomerID;

Data Types and Precision

The type and precision of the result of AVG() depend on the database system and the
input type:
• When averaging integer values, some systems return an integer (potentially trun-
cating fractional parts), while others promote the result to a higher-precision
type.
• When averaging DECIMAL or NUMERIC values, the result is typically of a related
decimal type, with precision and scale chosen according to system rules.
For financial or scientific applications where rounding and precision matter, it is
common to explicitly cast inputs or outputs, for example:
SELECT CAST(AVG(CAST(Salary AS DECIMAL(18,2))) AS DECIMAL(18,2)) AS AvgSalary
FROM Employees;

This ensures that the average is computed and presented with the desired numeric
characteristics.

Combining AVG with Other Aggregates

AVG() is often used together with COUNT() and SUM() to provide a fuller summary of a
distribution. For example:
SELECT Department,
COUNT(*) AS EmployeeCount,
SUM(Salary) AS TotalSalary,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

This query reports, for each department:

• how many employees there are,
• the total salary expenditure,
• and the average salary.
Such combined summaries are essential in reporting and dashboard queries.

Vo Hoang Nhat Khang 73

4 Functions and Calculations

Interpreting Averages Carefully

Although averages are intuitive, they can be misleading when distributions are skewed
or contain outliers. A very high or very low value can significantly affect AVG(), es-
pecially in small samples. In such cases, additional measures such as medians, per-
centiles, or trimmed means may be more informative, although these are not always
available as built-in SQL aggregates.
When possible, it is often helpful to inspect both AVG() and supporting statistics
(such as minimum, maximum, and standard deviation) to contextualize the average.
Note: AVG summarizes, but does not show the distribution
Two different departments can have the same average salary while having very dif-
ferent internal distributions (for example, one with many juniors and a few seniors,
the other with all mid-level employees). AVG() is a useful summary, but it does not
replace a closer look at the underlying values.

4.3 SQL Like

So far, our conditions in WHERE clauses have relied primarily on exact comparisons:
equality, inequality, and numeric ranges. In many practical scenarios, however, we
are interested in patterns rather than precise matches. For example, we may want all
customers whose name begins with a certain prefix, all email addresses ending with a
particular domain, or all products containing a keyword in their description.
The LIKE operator provides a simple pattern-matching mechanism for character
data in SQL. It allows us to express conditions using wildcard characters instead of
specifying entire strings exactly.
Note: LIKE is for text patterns
The LIKE operator is designed for character data (CHAR, VARCHAR, TEXT, etc.). Many
systems will implicitly convert non-text types to strings if used with LIKE, but it is
clearer and often safer to apply LIKE only to text columns.

Basic Use of LIKE

The general form of a predicate using LIKE is:

column [NOT] LIKE pattern

where pattern is a string that may contain special wildcard characters. In standard
SQL, the two most important wildcards are:

• % (percent) – matches zero or more arbitrary characters,

• _ (underscore) – matches exactly one arbitrary character.

For example, suppose we have a Customers table with a CustomerName column. To

find customers whose names start with the prefix ’Al’:

Vo Hoang Nhat Khang 74

 4.3 SQL Like

SELECT CustomerID, CustomerName

FROM Customers
WHERE CustomerName LIKE 'Al%';

Here, ’Al%’ matches any string that begins with ’Al’ followed by zero or more
characters.
Example: Exploring data with LIKE
When you first connect to a new database and only remember part of a name, LIKE
can help you find relevant rows:

SELECT CustomerID, CustomerName

FROM Customers
WHERE CustomerName LIKE '%smith%';

This returns any customer whose name contains ’smith’ somewhere, such as
’Anna Smith’ or ’Smithson Trading Co.’.

Using % for Prefix, Suffix, and Substring Matches

The percent sign % is the most frequently used wildcard, and its position in the pattern
determines which type of match we perform.

Prefix match.

-- Names starting with 'Mar'

WHERE CustomerName LIKE 'Mar%';

Suffix match.

-- Email addresses ending with '@[Link]'

WHERE Email LIKE '%@[Link]';

Substring match.

-- Product names that contain the word 'coffee' anywhere

WHERE ProductName LIKE '%coffee%';

In the last case, the pattern ’%coffee%’ matches any string that has ’coffee’ as a
contiguous substring, regardless of what precedes or follows it.
Note: Prefix vs. substring patterns
There is an important difference between ’word%’ and ’%word%’. The first searches
for values starting with ’word’; the second allows ’word’ anywhere in the string.
In many database engines, prefix patterns (like ’word%’) can use indexes more
efficiently than full substring patterns (like ’%word%’).

Vo Hoang Nhat Khang 75

4 Functions and Calculations

Using _ for Single-Character Matches

The underscore _ wildcard matches exactly one arbitrary character. It is useful when
we want to constrain the overall length of a string or the position of a varying character.
For example, to find product codes that follow the pattern AB, followed by any
single character, followed by 7:

WHERE ProductCode LIKE 'AB_7';

This matches codes such as ’AB17’, ’ABX7’, or ’ABa7’, but not ’AB7’, ’AB127’, or
’ABXY7’.
We can combine underscores to specify multiple unknown characters:

-- Exactly five-character codes beginning with 'P'

WHERE ProductCode LIKE 'P____';

Here, the pattern has one literal ’P’ followed by four underscores, for a total length
of five characters.
Example: Fixed-length codes
In systems that use fixed-length identifiers (such as ticket codes or voucher codes),
single-character wildcards are useful:

-- Codes of the form 'A' + 3 digits

WHERE Code LIKE 'A___';

This will match codes like ’A123’ or ’A045’, but not ’AB12’ or ’A1234’.

Case Sensitivity and Collation

Whether LIKE is case sensitive depends on the database system and the collation set-
tings of the column or database. For example:

• In some systems and collations, CustomerName LIKE ’al%’ may match ’Alfred’
and ’ALBERT’.

• In others, the same pattern may be strictly case sensitive, matching only names
that begin with a lowercase ’al’.

When case sensitivity matters, common strategies include:

• Using functions such as LOWER() or UPPER() on both the column and the pattern:

WHERE LOWER(CustomerName) LIKE 'al%';

• Choosing collations that enforce or ignore case according to requirements.

Because function calls may affect index usage, there is often a trade-off between
portability, readability, and performance.

Vo Hoang Nhat Khang 76

 4.3 SQL Like

Note: Case-insensitive search pattern

A portable idiom for case-insensitive matching is:

WHERE LOWER(ProductName) LIKE LOWER('%coffee%');

This makes the intent explicit: normalize both sides to lowercase, then apply the
pattern.

Escaping Wildcard Characters

Occasionally, we need to search for the literal characters % or _ themselves, rather than
treating them as wildcards. SQL supports this via an ESCAPE clause, which designates
a special escape character used within the pattern.
For example, to find product names that literally contain the string ’100%’:

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%100!%%' ESCAPE '!';

In this pattern:

• % at the beginning and end still act as wildcards.

• ’!%’ is interpreted as a literal percent sign, because ’!’ is declared as the escape
character.

Different systems offer slight variations on escaping behavior, but the core idea is
the same: an escape character cancels the special meaning of the wildcard in the pat-
tern.

NOT LIKE for Exclusion

As with other predicates, LIKE can be negated using NOT. This is useful when we want
to exclude strings that match a particular pattern.
For example, to find products whose names do not contain the word ’organic’:

SELECT ProductID, ProductName

FROM Products
WHERE ProductName NOT LIKE '%organic%';

Or to retrieve customers whose email is not from a specific domain:

SELECT CustomerID, Email

FROM Customers
WHERE Email NOT LIKE '%@[Link]';

As always, when combining NOT with other logical operators, parentheses may be
necessary for clarity.

Vo Hoang Nhat Khang 77

4 Functions and Calculations

Caution: Be careful with wide NOT LIKE filters

A predicate such as

WHERE ProductName NOT LIKE '%coffee%'

may match most rows in a large table. This is sometimes what you want, but it can
also produce unexpectedly large result sets and heavy scans. It is often useful to
add additional conditions to make the intent more precise.

LIKE Versus Regular Expressions

It is important to recognize that LIKE provides a relatively simple pattern language, not
a full regular-expression engine. Patterns based on % and _ are sufficient for many tasks
(prefix, suffix, substring, simple position constraints), but more complex conditions
- such as character classes, repetition bounds, or alternation - often require vendor-
specific regular expression functions.
For example, some systems support predicates such as:

WHERE ProductCode REGEXP '^[A-Z]{2}[0-9]{4}$';

These are beyond the scope of standard LIKE, but they complement it in databases
that provide them.
Note: Rule of thumb
Use LIKE when your pattern can be expressed with simple wildcards (starts with,
ends with, contains, fixed length with a few flexible characters). When patterns
become more complicated or you need precise control over character classes and
repetition, it is usually time to reach for regular expressions or more specialized
text-search features.

4.4 SQL Wildcards

The LIKE operator, introduced in the previous section, relies on wildcards to express
flexible pattern-matching conditions over character data. Wildcards act as placeholders
for one or more unspecified characters, allowing queries to search for prefixes, suffixes,
substrings, or more structured patterns rather than exact strings.
Although details vary across database systems, two wildcard characters are com-
mon to virtually all SQL dialects:

• % (percent) – matches zero or more arbitrary characters,

• _ (underscore) – matches exactly one arbitrary character.

Some systems, such as SQL Server, also support bracket-based patterns (e.g., [A-
Z]), which we will discuss briefly as dialect-specific extensions.

Vo Hoang Nhat Khang 78

 4.4 SQL Wildcards

Note: Wildcards and LIKE

Wildcard characters such as % and _ are only interpreted as wildcards in the context
of LIKE (or related operators such as NOT LIKE). In ordinary string comparisons
using =, ’A%’ is treated as a literal percent sign, not as a pattern.

Core Wildcards: % and _

The two core wildcards are summarized in the following table.

Wildcard Meaning
% zero or more arbitrary characters
_ exactly one arbitrary character

These wildcards are used within string literals in LIKE predicates:

column [NOT] LIKE 'pattern-with-wildcards'

The position and combination of wildcards determine which strings are matched.

Using % for Flexible-Length Matching

The percent sign % matches any sequence of characters, including the empty sequence.
Its position in the pattern determines whether we are matching prefixes, suffixes, or
arbitrary substrings.

Prefix match.
-- Customer names starting with 'Al'
WHERE CustomerName LIKE 'Al%';

This matches ’Alfred’, ’Alice’, and ’Al’ itself (because % can represent zero char-
acters).

Suffix match.
-- Email addresses ending with '@[Link]'
WHERE Email LIKE '%@[Link]';

Substring match.
-- Product names that contain 'coffee' anywhere
WHERE ProductName LIKE '%coffee%';

Here, the pattern ’%coffee%’ matches strings such as ’Iced coffee latte’, ’coffee
beans’, and ’Decaf coffee blend’.
Example: Searching by file extension
Suppose a table Files has a column FileName. To find all rows representing PDF
files, one simple approach is:
SELECT FileName

Vo Hoang Nhat Khang 79

4 Functions and Calculations

FROM Files
WHERE FileName LIKE '%.pdf';

The pattern ’%.pdf’ matches any file name whose last four characters are ’.pdf’.

Using _ for Single-Character Matching

The underscore _ wildcard matches exactly one character. It is useful when the overall
length of the string is constrained or when only specific positions are allowed to vary.

Fixed-length codes.

-- Product codes of length 5 starting with 'P'

WHERE ProductCode LIKE 'P____';

This pattern consists of ’P’ followed by four underscores, matching any five-character
string whose first character is ’P’.

Single-character substitution.

-- Codes of the form 'AB?7' where ? is any one character

WHERE ProductCode LIKE 'AB_7';

This matches ’AB17’, ’ABX7’, and ’ABa7’, but not ’AB7’ (too short) or ’AB127’ (too
long).
Underscores can be combined with percent signs in the same pattern. For example:

-- Names whose second character is 'a'

WHERE CustomerName LIKE '_a%';

Example: Patterned identifiers

If user IDs follow the format “U” + three digits (e.g., U101, U007), we can find all
such IDs using:

SELECT UserID
FROM Users
WHERE UserID LIKE 'U___';

Here, each underscore matches exactly one character, enforcing a total length of
four.

Dialect-Specific Bracket Patterns

Some SQL dialects, notably SQL Server and MS Access, extend LIKE with bracket-based
patterns:

• [abc] – matches any one of the listed characters (’a’, ’b’, or ’c’),

• [a-z] – matches any one character in the specified range,

Vo Hoang Nhat Khang 80

 4.4 SQL Wildcards

• [∧ aeiou] – matches any one character not in the specified set (in some dialects).

For example, in SQL Server:

-- Codes starting with a letter from A to F

WHERE ProductCode LIKE '[A-F]%';

-- Names starting with a consonant (dialect-dependent)

WHERE CustomerName LIKE '[^AEIOU]%';

These bracket patterns are not part of the core SQL standard and may not be avail-
able or may behave differently in other systems (such as PostgreSQL or MySQL). When
writing portable SQL, it is safer to rely on % and _ or to use regular expression functions
provided by specific systems.

Escaping Wildcards
Because % and _ have special meaning inside LIKE patterns, we need a way to match
them literally when they appear as actual characters in data. SQL provides an ESCAPE
clause that defines an escape character:

WHERE ColumnName LIKE '%100!%%' ESCAPE '!';

In this pattern:

• The leading and trailing % act as wildcards.

• The sequence ’!%’ is interpreted as a literal percent sign (because ’!’ is the
escape character).

Other escape characters (such as backslash) can be used instead of ’!’, depending
on conventions and database support. The key idea is that the escape character signals
that the following character should be treated literally.
Example: Literal underscore in usernames
Suppose usernames may contain underscores, and you want to find all users whose
name ends with ’_dev’:

SELECT UserName
FROM Users
WHERE UserName LIKE '%\_dev' ESCAPE '\';

Here, ’_’ is treated as a literal underscore because backslash is declared as the

escape character.

Combining Wildcards with NOT, AND, and OR

Wildcards operate within predicates that can be combined using logical operators. For
example:

Vo Hoang Nhat Khang 81

4 Functions and Calculations

-- Products whose name contains 'coffee' but not 'decaf'

WHERE ProductName LIKE '%coffee%'
AND ProductName NOT LIKE '%decaf%';

or:

-- Customers whose name starts with 'Al' or 'El'

WHERE CustomerName LIKE 'Al%'
OR CustomerName LIKE 'El%';

Parentheses should be used as needed to clarify precedence when mixing AND, OR,
and NOT.

Null Values and Wildcard Matching

Wildcards apply only to character values; if the column being tested is NULL, the entire
LIKE predicate evaluates to UNKNOWN. For example:

WHERE ProductName LIKE '%coffee%';

does not match rows where ProductName is NULL. To include such rows, an explicit
condition is needed:

WHERE ProductName LIKE '%coffee%'

OR ProductName IS NULL;

Caution: Performance and leading wildcards

Patterns that begin with a wildcard, such as ’%coffee%’ or ’%@[Link]’, of-
ten prevent the database from using ordinary indexes efficiently and may require
scanning many rows. In contrast, prefix patterns like ’coffee%’ are more index-
friendly. When large tables are involved, it is worth considering how wildcard
placement affects query performance.

4.5 SQL In
Many filtering conditions in SQL involve checking whether a value belongs to a small,
explicitly known set: customers from a few specific countries, orders in certain statuses,
or departments from a predefined subset. Writing such conditions using a series of OR
comparisons is both repetitive and error-prone.
The IN operator provides a concise and readable way to test membership in a list of
values (or in the result of a subquery). It is one of the most common tools for expressing
one of these values in a WHERE clause.
Note: IN is syntactic sugar for OR
Logically,
Country IN ('Germany', 'France', 'Sweden')

is equivalent to

Vo Hoang Nhat Khang 82

 4.5 SQL In

Country = 'Germany'
OR Country = 'France'
OR Country = 'Sweden'

The IN form is easier to read and less error-prone, especially as the list of values
grows.

Basic Use of IN with Literal Lists

The general form of a predicate using IN is:

expression [NOT] IN (value1, value2, value3, ...)

For example, consider a Customers table with a Country column. To select cus-
tomers from a small set of countries:

SELECT CustomerID, CustomerName, Country

FROM Customers
WHERE Country IN ('Germany', 'France', 'Sweden');

This is logically equivalent to:

WHERE Country = 'Germany'

OR Country = 'France'
OR Country = 'Sweden';

However, the IN form is shorter, easier to maintain, and more directly expresses the
idea of membership in a finite set.

NOT IN for Exclusion

The NOT IN form expresses the opposite intent: select rows whose value is not among
the listed values. For example, to find customers whose country is neither Germany,
France, nor Sweden:

SELECT CustomerID, CustomerName, Country

FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'Sweden');

As with other negations, NOT IN must be used carefully in the presence of NULL
values, a topic we discuss below.

IN with Numeric Values and Codes

The IN operator is not limited to character data; it can be used with numeric identifiers,
status codes, and other scalar types. For example, given an Orders table:

SELECT OrderID, CustomerID, Status

FROM Orders
WHERE Status IN (1, 2, 5); -- e.g., Pending, Shipped, Delivered

Vo Hoang Nhat Khang 83

4 Functions and Calculations

Or to select products whose IDs belong to a small, known subset:

SELECT ProductID, ProductName, UnitPrice

FROM Products
WHERE ProductID IN (101, 105, 110);

These patterns are very common when a business rule references an enumerated
set of codes.
Example: Whitelists and blacklists
-- Whitelist: only these payment methods are allowed
WHERE PaymentMethod IN ('CreditCard', 'PayPal', 'BankTransfer');

-- Blacklist: exclude these test users

WHERE UserName NOT IN ('test', 'demo', 'sample');

Using IN makes such allow/deny list rules visible at a glance.

IN with Subqueries
One of the most powerful uses of IN arises when the list of values is not fixed, but
computed dynamically by a subquery. In this context, IN expresses membership in the
result of another query.
For example, to find customers who have placed at least one order, we can write:

SELECT CustomerID, CustomerName

FROM Customers
WHERE CustomerID IN (
SELECT DISTINCT CustomerID
FROM Orders
);

Here, the subquery returns a set of CustomerID values from the Orders table, and
the outer query selects customers whose CustomerID appears in that set.
Similarly, to find products that have ever been ordered:

SELECT ProductID, ProductName

FROM Products
WHERE ProductID IN (
SELECT DISTINCT ProductID
FROM OrderDetails
);

Conceptually, the subquery defines a dynamic value set, and IN tests whether each
row in the outer query belongs to it.
Note: Do you really need DISTINCT?
In predicates like
WHERE CustomerID IN (
SELECT CustomerID

Vo Hoang Nhat Khang 84

 4.5 SQL In

FROM Orders
);

adding DISTINCT inside the subquery usually does not change the logic: IN only
cares whether a value appears at least once. Some optimizers remove redundant
DISTINCT automatically, but it is still useful to understand that duplicates are irrel-
evant for the semantics of IN.

IN Versus EXISTS (Conceptual Comparison)

Queries that use IN with a subquery often have an equivalent formulation using EXISTS.
For instance, the customers with orders query above can also be written as:
SELECT [Link], [Link]
FROM Customers AS c
WHERE EXISTS (
SELECT 1
FROM Orders AS o
WHERE [Link] = [Link]
);

Both forms express the same logical intent: select customers for whom at least one
matching order exists. Depending on the database system and indexing, one form
may be more efficient than the other, but modern optimizers frequently recognize and
transform between them.
From a modeling perspective:
• IN (subquery) emphasizes membership in a set of values.
• EXISTS (subquery) emphasizes the existence of related rows.
Choosing between them is often a matter of readability and performance consider-
ations.

NULL and the Subtlety of NOT IN

The interaction between NOT IN and NULL values is a frequent source of confusion. Con-
sider the predicate:
WHERE Country NOT IN ('Germany', 'France');

If the Country column itself is NULL, the expression Country NOT IN (...) evalu-
ates to UNKNOWN, and the row is excluded from the result. This behavior is consistent
with SQLs three-valued logic.
More subtle is the case where the list in the IN predicate contains NULL. For example:
WHERE Country NOT IN ('Germany', 'France', NULL);

In this situation, the entire predicate can become UNKNOWN for all rows, because SQL
must conceptually test whether Country is equal to any of the listed values, including
NULL. Since comparisons with NULL are UNKNOWN, the result of the combined test can be
UNKNOWN, causing no rows to match.

Vo Hoang Nhat Khang 85

4 Functions and Calculations

Caution: NOT IN and NULL

Avoid using NOT IN with lists or subqueries that may contain NULL. The safest op-
tions are:

• filter out NULL values in the subquery:

WHERE Country NOT IN (
SELECT Country
FROM SomeTable
WHERE Country IS NOT NULL
);

• or use NOT EXISTS with an explicit join condition.

This avoids surprising no rows returned situations caused by three-valued logic.

For this reason, using NOT IN with subqueries that may produce NULL values can be
dangerous. A safer pattern is to filter out NULLs explicitly in the subquery, or to use NOT
EXISTS instead. For example, instead of:

WHERE CustomerID NOT IN (

SELECT CustomerID
FROM Orders
);

it is often preferable to write:

WHERE NOT EXISTS (

SELECT 1
FROM Orders AS o
WHERE [Link] = [Link]
);

This formulation avoids the pitfalls of NULL values in the subquery result.

IN, Performance, and Readability

From a performance perspective, IN with a short list of literal values is usually straight-
forward for the optimizer to handle and can be more efficient than a long chain of OR
conditions. For larger dynamic sets, the efficiency of IN (subquery) depends on in-
dexing and the optimizers ability to transform the query.
From a readability perspective, IN often reveals intent more clearly than equivalent
OR expressions, particularly when:

• Testing a single column against many candidate values,

• Expressing membership in a whitelist or blacklist of categories or codes,

• Using subqueries to define logical sets of related keys.

As queries grow more complex, clarity becomes as important as raw performance.

Choosing IN when appropriate can make business rules easier to verify and maintain.

Vo Hoang Nhat Khang 86

 4.6 SQL Between

4.6 SQL Between

Many filtering conditions in SQL involve selecting values that fall within a continuous
range: prices between two limits, dates within a specific period, or numeric identifiers
between lower and upper bounds. The BETWEEN operator provides a compact way to
express such range predicates.
At a logical level, a condition of the form

expression BETWEEN a AND b

is equivalent to
expression >= a AND expression <= b,
with the important detail that the bounds a and b are inclusive.
Note: How to read BETWEEN
A useful mental model is to read
value BETWEEN 10 AND 20

as value is at least 10 and at most 20. Both boundaries are included, and the lower
bound should be less than or equal to the upper bound.

Basic Use of BETWEEN

The general form of a BETWEEN predicate is:

expression [NOT] BETWEEN lower_bound AND upper_bound

For example, consider a Products table with a UnitPrice column. To find products
priced between 5 and 15 (inclusive):

SELECT ProductID, ProductName, UnitPrice

FROM Products
WHERE UnitPrice BETWEEN 5.00 AND 15.00;

This is semantically equivalent to:

WHERE UnitPrice >= 5.00

AND UnitPrice <= 15.00;

Both boundary values are included: a product priced exactly at 5.00 or 15.00 matches
the predicate.
Example: Filtering scores in a band
Suppose exam scores are stored in ExamResults(StudentID, Score). To find stu-
dents who scored in the B band between 70 and 84 inclusive:

SELECT StudentID, Score

FROM ExamResults
WHERE Score BETWEEN 70 AND 84;

Vo Hoang Nhat Khang 87

4 Functions and Calculations

This pattern is much easier to read than writing two separate comparisons joined
by AND.

BETWEEN with Dates

BETWEEN is commonly used with date and time values to select records from a particular
interval. For example, given an Orders table:

SELECT OrderID, CustomerID, OrderDate, TotalAmount

FROM Orders
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

This query returns all orders whose OrderDate falls on or between 1 January 2025
and 31 January 2025, inclusive.
When working with date/time types that include time-of-day components, it is im-
portant to ensure that the upper bound accurately captures the intended end of the
range. In some designs, it is safer to use a half-open interval, such as:

WHERE OrderDate >= '2025-01-01'

AND OrderDate < '2025-02-01';

rather than BETWEEN on a date-time column that includes hours, minutes, and sec-
onds.
Caution: Beware of time components in BETWEEN
If OrderDate includes a time-of-day, then
WHERE OrderDate BETWEEN '2025-01-01'
AND '2025-01-31'

will typically include orders from midnight at the start of 2025-01-31, but exclude
orders placed later that same day (e.g., 2025-01-31 10:15). Using a half-open range
with < ’2025-02-01’ avoids this pitfall.

NOT BETWEEN for Excluding Ranges

The negated form, NOT BETWEEN, selects values that lie outside the specified range. For
example, to find premium products whose price is either below 20 or above 100:

SELECT ProductID, ProductName, UnitPrice

FROM Products
WHERE UnitPrice NOT BETWEEN 20.00 AND 100.00;

This predicate is equivalent to:

WHERE UnitPrice < 20.00

OR UnitPrice > 100.00;

As with other uses of NOT, careful attention to parentheses is required when NOT
BETWEEN is combined with additional conditions.

Vo Hoang Nhat Khang 88

 4.6 SQL Between

BETWEEN and Text Values

Although BETWEEN is most intuitive for numeric and date types, it can also be applied
to character data, using the databases collation order. For example, to select customer
names that fall alphabetically between ’A’ and ’M’:
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName BETWEEN 'A' AND 'M';
The exact set of names included depends on collation rules (case sensitivity, accents,
locale). Because character ordering can be non-obvious, text-based uses of BETWEEN are
less common in practice than numeric or date ranges, and explicit pattern matching
(LIKE) or conditions on derived prefixes are often preferred.

Inclusive Bounds and Boundary Cases

A key property of BETWEEN is that both bounds are inclusive:
• UnitPrice BETWEEN 5 AND 15 includes values 5 and 15.
• OrderDate BETWEEN ’2025-01-01’ AND ’2025-01-31’ includes orders on both
the start and end dates.
In most SQL dialects, if the lower bound is greater than the upper bound, the pred-
icate evaluates to false for all non-NULL values. For example:
WHERE UnitPrice BETWEEN 15.00 AND 5.00;
typically matches no rows. For clarity, it is good practice to ensure that the lower bound
and upper bound are written in ascending order.
Note: Symmetry and BETWEEN
Standard BETWEEN is not symmetric: swapping the bounds will usually change
the result (often to no rows). Some systems provide a non-standard BETWEEN
SYMMETRIC operator, but it is not widely supported. In portable SQL, always write
the lower bound first.

Interaction with NULL Values

As with other predicates, BETWEEN is affected by NULL. If the expression being tested is
NULL, the result of the BETWEEN comparison is UNKNOWN, and the row does not satisfy the
WHERE clause.
For example:
WHERE UnitPrice BETWEEN 5.00 AND 15.00;
excludes rows where UnitPrice is NULL. Similarly, if either bound is NULL, the result is
UNKNOWN for all rows:
WHERE UnitPrice BETWEEN 5.00 AND NULL; -- always UNKNOWN
Therefore, bounds in BETWEEN predicates should generally be constants or expres-
sions known not to be NULL. If NULL values require special handling, explicit predicates
such as IS NULL or IS NOT NULL should be added.

Vo Hoang Nhat Khang 89

4 Functions and Calculations

BETWEEN and Index Usage

From a performance perspective, BETWEEN is often index-friendly when applied to a
single column with well-defined bounds. For instance:

WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

can typically be supported efficiently by an index on OrderDate, allowing the database

to seek directly into the relevant range instead of scanning the entire table.
More complex expressions, such as BETWEEN on a computed function of a column,
may prevent index usage unless the database supports functional indexes on the ex-
pression in question.

BETWEEN Versus Explicit Comparisons

Although BETWEEN is mostly syntactic sugar for a pair of comparisons joined by AND, it
improves readability in many common cases:

• Date intervals:
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

• Score bands:
WHERE Score BETWEEN 80 AND 100;

• Identifier ranges:
WHERE EmployeeID BETWEEN 1000 AND 1999;

Using BETWEEN emphasizes the range nature of the condition and reduces duplica-
tion compared to writing the equivalent >= and <= predicates manually.

4.7 SQL Aliases

As queries grow more complex, the names of tables and columns can become long,
repetitive, or ambiguous. For example, joins often involve multiple tables with simi-
larly named columns, and derived expressions in the SELECT list may not have mean-
ingful default names.
Aliases provide a way to assign temporary, query-local names to columns and ta-
bles. They improve readability, reduce typing, and clarify intent, especially in queries
involving joins, aggregates, and nested subqueries.
In SQL, aliases are defined only for the duration of a single statement; they do not
change the underlying schema.
Note: Why aliases matter
A good alias is like a good variable name in programming: short but meaningful.
In longer queries, well-chosen aliases can be the difference between a query you
can understand in five seconds and one you have to re-parse from scratch every
time you read it.

Vo Hoang Nhat Khang 90

 4.7 SQL Aliases

Column Aliases
A column alias assigns a more convenient or descriptive name to an expression in the
SELECT list. The general forms are:

expression AS alias
expression alias

The AS keyword is optional in many dialects, but its use often improves clarity.
For example, suppose we have an Employees table with columns FirstName, LastName,
and Salary. We can present a combined full name and a renamed salary column:

SELECT FirstName + ' ' + LastName AS FullName,

Salary AS MonthlySalary
FROM Employees;

Here:

• FirstName + ’ ’ + LastName is an expression,

• FullName is the column alias that will appear in the result.

The alias is visible in the query output and can be referenced in some clauses (such
as ORDER BY) of the same statement.
Example: Labeling derived metrics
Suppose sales data are stored in OrderLines(OrderID, ProductID, Quantity,
UnitPrice). To compute a readable line total for each row:

SELECT OrderID,
ProductID,
Quantity,
UnitPrice,
Quantity * UnitPrice AS LineTotal
FROM OrderLines;

Without the LineTotal alias, the result would show an unnamed expression col-
umn, making downstream analysis and reporting less clear.

Using Aliases in ORDER BY and GROUP BY

Most SQL implementations allow column aliases to be referenced in ORDER BY and
sometimes in GROUP BY clauses. For example:

SELECT Department,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
ORDER BY AvgSalary DESC;

Vo Hoang Nhat Khang 91

4 Functions and Calculations

In this query, AvgSalary is used in the ORDER BY clause instead of repeating the
AVG(Salary) expression, improving both readability and maintainability.
By contrast, aliases generally cannot be used in the WHERE clause, because WHERE is
evaluated logically before the SELECT list is processed. If a filter must use the same
expression as an alias, the expression is typically repeated or factored into a subquery
or common table expression.
Caution: Alias visibility and WHERE
A common mistake is to write:
SELECT Salary * 1.10 AS AdjustedSalary
FROM Employees
WHERE AdjustedSalary > 80000; -- usually invalid

Here, AdjustedSalary is not yet defined when WHERE is evaluated. A portable fix
is to repeat the expression:
SELECT Salary * 1.10 AS AdjustedSalary
FROM Employees
WHERE Salary * 1.10 > 80000;

or to move the expression into a subquery and filter in the outer query.

Quoting Aliases
Aliases can often contain spaces or special characters if enclosed in appropriate quotes,
depending on the database system:

SELECT COUNT(*) AS "Number of Employees"

FROM Employees;

The exact quoting syntax (double quotes, square brackets, backticks) is dialect-
dependent. While such aliases may be useful for human-facing reports, using simple,
identifier-style aliases is often preferable when the results will be consumed by other
queries or programs.

Table Aliases
Table aliases provide short, local names for tables within a query. They are especially
useful in joins, where they reduce repetition and disambiguate references to columns
with the same name.
The general form is:

FROM TableName AS alias

FROM TableName alias

For example, consider an Orders table and a Customers table:

SELECT [Link],
[Link],
[Link]

Vo Hoang Nhat Khang 92

 4.7 SQL Aliases

FROM Orders AS o
JOIN Customers AS c
ON [Link] = [Link];

Here, o and c are table aliases for Orders and Customers. Column references are
qualified with these aliases (e.g., [Link]) to make clear which table each column
comes from.
Without aliases, the same query would be more verbose:
SELECT [Link],
[Link],
[Link]
FROM Orders
JOIN Customers
ON [Link] = [Link];

As queries grow in complexity, short aliases greatly improve readability.

Example: Self-join with table aliases
Suppose the Employees table includes a ManagerID column that references
EmployeeID in the same table. To list employees together with their managers:

SELECT [Link] AS EmployeeName,

[Link] AS ManagerName
FROM Employees AS e
JOIN Employees AS m
ON [Link] = [Link];

The aliases e and m distinguish between the employee side and the manager side
of the same underlying table.

Disambiguating Column Names

When multiple tables share the same column name (such as ID or Name), table aliases
are necessary to resolve ambiguity. For example:
SELECT [Link] AS EmployeeName,
[Link] AS ManagerName
FROM Employees AS e
JOIN Employees AS m
ON [Link] = [Link];

In this self-join, two aliases (e and m) refer to the same underlying table Employees,
allowing us to distinguish between the employee and their manager.

Aliases for Derived Tables and Subqueries

Aliases are mandatory for derived tablessubqueries used in the FROM clause. The sub-
query itself produces a temporary table, and the alias names that table within the outer
query.
For example, suppose we want to compute the total spending per customer and
then filter on that summary:

Vo Hoang Nhat Khang 93

4 Functions and Calculations

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
JOIN (
SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
) AS s
ON [Link] = [Link]
WHERE [Link] > 1000;

In this example:

• The inner SELECT produces a derived table with columns CustomerID and TotalSpent.

• The alias s names this derived table.

• The outer query can then refer to [Link] and [Link].

Without the alias s, most database systems will reject the query because every de-
rived table must have a name.
Note: Choosing good alias names
Single-letter aliases (like t or x) are convenient in short, ad-hoc queries. In longer
or shared queries, slightly longer aliases such as cust, ord, or emp can make the
query much easier to follow without adding much typing overhead.

Aliasing Expressions for Clarity

Aliases are also useful for clarifying the meaning of computed or transformed columns.
For example, when normalizing units or applying business rules:

SELECT ProductName,
UnitPrice * 1.10 AS PriceWithTax,
UnitsInStock AS Stock
FROM Products;

Here, PriceWithTax communicates the intent of the expression UnitPrice * 1.10

much more clearly than leaving the column unnamed or using a generic default.
Similarly, when extracting parts of a date:

SELECT OrderID,
OrderDate,
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth
FROM Orders;

The aliases make the result immediately interpretable to both humans and down-
stream tools.

Vo Hoang Nhat Khang 94

 4.7 SQL Aliases

Scope and Lifetime of Aliases

It is important to remember that aliases:

• Exist only within the query in which they are defined.

• Do not alter the underlying table schema or column names.

• Are not visible outside their scope (for example, an alias in an inner subquery is
not available in the outer query unless it is projected as a column).

Caution: Aliases are not schema changes

Defining
SELECT Salary AS AnnualSalary
FROM Employees;

does not create a new column called AnnualSalary in the Employees table. The alias
exists only for the duration of that querys result set. To persist a new column, you
must alter the table schema explicitly.

Vo Hoang Nhat Khang 95

4 Functions and Calculations

Vo Hoang Nhat Khang 96

Part II Intermediate SQL

Vo Hoang Nhat Khang 97

Chapter 5 Mastering Joins

5.1 Overview of Joins

Up to this point, most queries have operated on a single table at a time. In realistic
databases, however, information is deliberately decomposed across multiple tables:
customers are stored separately from orders, products separately from categories, and
so on. This decomposition reduces redundancy and improves consistency, but it also
means that meaningful questions often require combining data from several tables.
Joins are the relational mechanism for doing exactly this. A join takes two (or more)
tables and produces a new, virtual table whose rows relate data across them according
to some condition. Informally, we can think of joins as answering questions of the form:

For each row in table A, find the matching row(s) in table B, and bring their attributes
together.

From a relational-theoretic perspective, joins can be expressed as a combination of

three operations:

• Cartesian product (cross product) – form all possible pairs of rows between two
tables.

• Selection – keep only those pairs that satisfy a given condition (the join condition).

• Projection – select which columns (attributes) to include in the final result.

SQL hides these low-level steps behind a higher-level syntax such as:

SELECT ...
FROM A
JOIN B
ON [Link] = [Link];

In this chapter, we will gradually refine our understanding of joins, starting with
the relational intuition, then examining join conditions and keys, and finally exploring
specific join types such as inner, left, right, full, and self joins.
Note: Joins as “relationships made explicit”
A useful mental model is that tables store facts about kinds of things (customers,
orders, products), and joins let you tell stories that connect those facts: which cus-
tomer placed which order, which product belongs to which category, and so on.

Vo Hoang Nhat Khang 99

5 Mastering Joins

5.1.1 Relational Thinking with Joins

Relational databases are built on the idea that tables represent relations between at-
tributes. When we normalize a schema, we typically split one large conceptual table
into several smaller ones, each capturing a specific type of fact. For example:

Customers Orders
CustomerID Name OrderID
City CustomerID
Country OrderDate

Conceptually:

• The Customers table tells us facts about each customer.

• The Orders table tells us facts about each order, including which customer placed
it.

A join between Customers and Orders reconstructs the relationship: “which orders
belong to which customers”. Relationally, this is often described as:

Customers ./[Link]=[Link] Orders

The join produces a new relation whose rows contain attributes from both tables.
Each row represents a combined fact such as:

Customer Alice (in Berlin) placed Order 102 on 2025–01–15.

Thinking in these terms is helpful for two reasons:

1. It emphasizes that joins are about relationships between entities, not about “look-
ing up” values in some procedural sense.

2. It clarifies that joins are purely declarative: we describe which rows correspond;
the database decides how to compute the result.

As our queries grow more complex, maintaining this relational perspective helps
prevent mistakes such as accidental duplication, missing relationships, or logically in-
correct join conditions.
Example: Joining customers and orders
Suppose we store:

Customers
CustomerID Name City Country
1 Alice Müller Berlin Germany
2 David Tran Hanoi Vietnam
Orders
OrderID CustomerID OrderDate TotalAmount
101 1 2025-01-15 120.00
102 1 2025-01-20 45.00
103 2 2025-02-01 80.00

Vo Hoang Nhat Khang 100

 5.1 Overview of Joins

To see each order together with the customer name:

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM Customers AS c
JOIN Orders AS o
ON [Link] = [Link];

Each result row combines one Customers row with one matching Orders row, using
the shared key CustomerID.

5.1.2 Join Conditions and Keys

A join is defined not just by the tables it combines, but by the condition that specifies
which rows from each table should be related. In SQL, this appears in the ON clause:

SELECT ...
FROM Customers AS c
JOIN Orders AS o
ON [Link] = [Link];

Here, the join condition equates [Link] and [Link]. Several impor-
tant points follow from this:

• Joins often use key relationships: a primary key in one table (e.g., [Link])
and a foreign key in another (e.g., [Link]).

• The condition need not be a simple equality; it can involve multiple columns or
more complex predicates, though equality joins are by far the most common.

• A join condition defines the logical relationship; it does not itself specify whether
unmatched rows are kept or discarded. That behavior is determined by the type
of join (inner, left, right, full).

Multi-column keys are common when no single attribute uniquely identifies a row.
For example, suppose we have a table of daily product prices:

ProductPrices
ProductID PriceDate Currency UnitPrice

The natural key might be the combination (ProductID, PriceDate, Currency). A

join involving this table could use a compound condition:

ON [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]

Vo Hoang Nhat Khang 101

5 Mastering Joins

The correctness of a join depends critically on the correctness of its condition. A join
with too weak a condition (for example, missing part of a composite key) can create
spurious matches and duplicated rows; a join with too strong or incorrect conditions
can eliminate valid matches.
For this reason, understanding the declared keys (primary and foreign) and func-
tional dependencies in a schema is a prerequisite for writing reliable join conditions.
Caution: The “accidental duplication” trap
A very common bug in real SQL code is a join condition that is almost, but not
quite, correct. For example, joining on CustomerID but forgetting to also join on
OrderDate when the true key is (CustomerID, OrderDate).
The query may still run and produce results, but some rows will be duplicated or
combined incorrectly. Whenever you see mysterious duplicates in a query result,
the join conditions are one of the first places to investigate.

5.1.3 Cross Join vs. Explicit Joins

Before introducing specific join types, it is useful to revisit the foundational idea of the
Cartesian product of two tables.

Cross Join (Cartesian Product)

A CROSS JOIN in SQL produces the Cartesian product of two tables: every row from
the first combined with every row from the second.

SELECT *
FROM A
CROSS JOIN B;

If A has 3 rows and B has 4 rows, the cross join has 3 × 4 = 12 rows. This operation
is rarely what we want by itself, but it is conceptually important: most other joins can
be seen as a cross join followed by a filtering step.
Historically, SQL also allowed implicit cross joins using a comma:

SELECT *
FROM A, B;

Modern style strongly encourages using JOIN syntax explicitly, as discussed next.

Explicit Joins with ON

In everyday SQL, we almost always combine tables using an explicit join with a join
condition:

SELECT ...
FROM A
JOIN B
ON [Link] = [Link];

Compared to commas in the FROM clause, explicit join syntax has several advantages:

Vo Hoang Nhat Khang 102

 5.1 Overview of Joins

• Clarity: the join condition is visually tied to the join itself.

• Extensibility: different join types (inner, left, right, full) are expressed clearly.

• Safety: it is harder to accidentally omit a join condition (which would otherwise

produce an unintended Cartesian product).

To see the relationship between a cross join and an inner join, consider:

-- Conceptual two-step view

SELECT *
FROM A
CROSS JOIN B
WHERE [Link] = [Link];

-- Typical explicit join

SELECT *
FROM A
JOIN B
ON [Link] = [Link];

Both queries are logically equivalent: start with all pairs, then retain only those
where the keys match. In practice, the database optimizer does not literally compute
the Cartesian product; it uses indexes and join algorithms to compute the same result
more efficiently.

When to Use CROSS JOIN Intentionally

Although unintended Cartesian products are usually a mistake, there are cases where
a cross join is the correct tool:

• Generating all combinations of a small set of categories and time periods.

• Creating test data with all pairwise combinations of attributes.

• Building a grid or matrix of values for reporting.

For example, to generate all combinations of sizes and colors:

SELECT [Link], [Link]

FROM Sizes AS s
CROSS JOIN Colors AS c;

In such cases, using the explicit keyword CROSS JOIN makes the intention unmis-
takable: the combinatorial explosion is deliberate, not an accident.

Vo Hoang Nhat Khang 103

5 Mastering Joins

5.2 SQL Inner Join

An INNER JOIN is the most common and conceptually simplest form of join in SQL.
It returns only those rows where the join condition between two tables is satisfied. In
other words, an inner join keeps the intersection of two tables with respect to a specified
relationship.
From a relational point of view, an inner join corresponds to taking the Cartesian
product of two relations, then selecting only the pairs of rows that satisfy the join pred-
icate, and finally projecting the desired columns. Rows from either table that do not
have at least one matching partner in the other table are excluded from the result.
Note: Inner join as “keep only the matches”
A quick way to remember inner joins is:

inner join result = rows that match on both sides.

Anything that does not find a partner in the other table simply disappears from the
result.

5.2.1 Definition and Use Cases

The typical syntax of an inner join is:
SELECT column_list
FROM TableA AS a
INNER JOIN TableB AS b
ON [Link] = [Link];

The keyword INNER is optional in many dialects; JOIN by itself usually denotes an
inner join:
SELECT column_list
FROM TableA AS a
JOIN TableB AS b
ON [Link] = [Link];

Example: Customers and orders

Suppose we have two tables:

Customers Orders
CustomerID CustomerName OrderID
City CustomerID
Country OrderDate

To list all orders together with the names of the customers who placed them:

SELECT [Link],
[Link],
[Link],
[Link]
FROM Orders AS o

Vo Hoang Nhat Khang 104

 5.2 SQL Inner Join

INNER JOIN Customers AS c

ON [Link] = [Link];

This query returns one row for each order that has a matching customer. Orders
with an invalid or missing CustomerID, and customers who have never placed an
order, do not appear in the result.

Typical use cases. Inner joins are appropriate when:

• You are interested only in records that have valid relationships on both sides (e.g.,
orders that belong to an existing customer).

• You are combining fact tables with their dimensions (orders with customers, line
items with products).

• You are enforcing implicit integrity in queries, even if the schema does not declare
foreign keys.

In many reporting and analytical queries, the inner join is the default choice: we
want to see only entities that are fully “connected” according to the model.
Example: Chaining multiple inner joins
It is common to join more than two tables at once. For example, to see orders to-
gether with customer and product information for each line item:

SELECT [Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Orders AS o
JOIN Customers AS c ON [Link] = [Link]
JOIN OrderDetails AS od ON [Link] = [Link]
JOIN Products AS p ON [Link] = [Link];

Each row in the result now represents a customer–order–product triple. If any link
in this chain is missing (for example, a line item with a ProductID that does not
exist), that combination is excluded by the inner join semantics.

5.2.2 Filtering with Inner Joins

An inner join both combines and filters data. The join condition in the ON clause acts
as a filter across tables: only pairs of rows that satisfy this condition survive into the
intermediate join result.
Once tables are joined, additional conditions in the WHERE clause can further restrict
the result. For inner joins, conditions in ON and WHERE are logically similar, although
their placement matters for readability and becomes important for outer joins.

Vo Hoang Nhat Khang 105

5 Mastering Joins

Join condition as a filter. Consider a join between Employees and Departments:

SELECT [Link],
[Link],
[Link]
FROM Employees AS e
JOIN Departments AS d
ON [Link] = [Link];

Here, only employees whose DepartmentID matches a row in Departments will ap-
pear. If an employee is assigned to a non-existent department (or has a NULL DepartmentID),
that employee is dropped from the result.

Adding row-level filters. We can then apply additional criteria with WHERE. For ex-
ample, to show only employees in Engineering hired after a certain date:
SELECT [Link],
[Link],
[Link],
[Link]
FROM Employees AS e
JOIN Departments AS d
ON [Link] = [Link]
WHERE [Link] = 'Engineering'
AND [Link] >= '2022-01-01';

Conceptually:
1. The join pairs employees with their departments where the IDs match.
2. The WHERE clause then filters those pairs to Engineering employees hired on or
after 2022–01–01.
For pure inner joins, moving predicates that refer only to one table between ON and
WHERE does not change the final set of rows, although it may influence query plans. For
example, the following two queries are logically equivalent:
-- Predicate in ON
SELECT [Link], [Link], [Link]
FROM Employees AS e
JOIN Departments AS d
ON [Link] = [Link]
AND [Link] = 'Engineering';

-- Predicate in WHERE
SELECT [Link], [Link], [Link]
FROM Employees AS e
JOIN Departments AS d
ON [Link] = [Link]
WHERE [Link] = 'Engineering';

This equivalence does not hold for outer joins, which is why being explicit and con-
sistent about predicate placement is good practice.

Vo Hoang Nhat Khang 106

 5.2 SQL Inner Join

Note: Heuristic: ON for relationships, WHERE for filters

A common style guideline is:

• Put conditions that express how tables relate in the ON clause.

• Put conditions that express which rows you want to keep in the WHERE clause.

For inner joins this is mostly a stylistic choice, but following it makes it easier to
reason about more complex joins later.

5.2.3 Common Pitfalls (Duplicate Rows, Missing Matches)

Although inner joins are conceptually straightforward, they are a frequent source of
subtle bugs. Two recurring issues are unexpected duplicate rows and unintended loss
of data (missing matches).

Duplicate rows from one-to-many and many-to-many relationships

Whenever one row in one table matches multiple rows in the other, the join will natu-
rally produce multiple rows for that entity. This is not an error: it is how the relational
model represents one-to-many and many-to-many relationships.

One-to-many example. Suppose a customer can place multiple orders. Joining Customers
to Orders:

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
JOIN Orders AS o
ON [Link] = [Link];

If a given customer has 5 orders, that customer will appear in 5 rows, once per order.
If we simply count rows in this result:

SELECT COUNT(*) AS RowCount

FROM Customers AS c
JOIN Orders AS o
ON [Link] = [Link];

we are counting customer–order pairs, not distinct customers. To count distinct cus-
tomers who have at least one order, we must say so explicitly:

SELECT COUNT(DISTINCT [Link]) AS CustomersWithOrders

FROM Customers AS c
JOIN Orders AS o
ON [Link] = [Link];

The “duplicate rows” are only problematic if we misinterpret what the joined rows
represent.

Vo Hoang Nhat Khang 107

5 Mastering Joins

Caution: Aggregates after joins

When you aggregate after a join, always ask: What does each joined row represent?
If each row represents, say, a customerorderline item combination, then:

• COUNT(*) is counting line items, not orders or customers.

• SUM(TotalAmount) may double-count if the same order appears multiple

times.

When in doubt, start with a SELECT TOP (10) * (or LIMIT 10) to visually inspect
what one row in the joined result actually means.

Many-to-many explosion. In many-to-many relationships, failing to join through the

appropriate junction table can lead to combinatorial growth. For example, if products
and suppliers are related through a ProductSuppliers table, joining Products directly
to Suppliers on a vague condition (such as matching categories) may create a much
larger result than expected and distort aggregates like SUM() and AVG().

Missing rows due to inner join semantics

The second common pitfall is unintentionally discarding rows that do not have a match
in the joined table. Because an inner join returns only matching pairs, any row on either
side without a matching partner vanishes from the result.

Example: Customers without orders. If we write:

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
JOIN Orders AS o
ON [Link] = [Link];

customers with no orders are excluded entirely. If our analytical question is “list all
customers and show their orders if they have any”, an inner join is the wrong tool; we
should use a left outer join instead. Misusing an inner join in such situations leads to
silent data loss—no error is raised, but the result is incomplete.

Incorrect or incomplete join conditions

A third source of problems is specifying an incorrect join condition or omitting part
of a composite key. For example, if the natural key for a relationship is (CustomerID,
OrderDate), but we join only on CustomerID:
ON [Link] = [Link] -- missing date component

we may inadvertently match rows that should not be related, creating spurious du-
plicates and inflating aggregates. In extreme cases, forgetting the join condition en-
tirely (for example, using commas in FROM without a proper WHERE) produces a full
Cartesian product, often many orders of magnitude larger than intended.

Vo Hoang Nhat Khang 108

 5.3 SQL Left Join

Note: Inner vs. outer joins

An inner join keeps only the matched rows on both sides. In the next sections, we
will see outer joins, which keep all rows from one side (or both), filling in NULLs
where no match exists. Choosing between inner and outer joins is fundamentally
a question about which rows you are willing to discard.

5.3 SQL Left Join

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the table on the “left” side
of the join, and the matching rows from the table on the “right” side. When there is no
matching row on the right, the result still contains the left row, but all columns from
the right table are filled with NULL.
In contrast to an inner join—which returns only rows that have matches on both
sides—a left join preserves the left-hand table completely and “decorates” it with data
from the right-hand table where available. This makes left joins the natural tool for
queries phrased as:

“Show me all X, and include related Y if they exist.”

Note: Quick mental model

Think of a left join as: “Start with every row from the left table. For each one, try
to attach matching data from the right table. If nothing matches, keep the left row
anyway and fill the right-hand columns with NULL.”

5.3.1 Preserving Unmatched Rows

The general syntax of a left join is:

SELECT column_list
FROM LeftTable AS l
LEFT JOIN RightTable AS r
ON [Link] = [Link];

All rows from LeftTable appear in the result. For each left row, the database looks
for matching rows in RightTable according to the ON condition:

• If one or more matches are found, those matches are combined with the left row
(potentially producing multiple result rows).

• If no matches are found, the left row still appears once, with NULL values in all
columns that come from the right table.

Example: Customers with and without orders

Consider these simplified tables:

Vo Hoang Nhat Khang 109

5 Mastering Joins

Customers Orders
CustomerID CustomerName OrderID CustomerID TotalAmount
1 Alice 10 1 120.00
2 Bob 11 1 80.00
3 Carol 12 3 50.00

Here, Alice and Carol have orders; Bob does not. If we want to list all customers
together with their orders (if any), we can write:

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link];

The result is:

CustomerID CustomerName OrderID TotalAmount
1 Alice 10 120.00
1 Alice 11 80.00
2 Bob NULL NULL
3 Carol 12 50.00

Notice:

• Alice appears twice, once for each matching order.

• Carol appears once, with her single order.

• Bob appears once, even though there is no matching order. The right-side
columns for Bob are NULL.

This is the defining feature of left joins: rows from the left table are preserved re-
gardless of whether a match exists.

Typical use cases. Left joins are particularly useful when:

• You want to list all entities from a primary table, and show related data if present
(e.g., customers and their orders, products and their latest price, employees and
their manager).

• You want to identify entities that do not have related records (e.g., customers with
no orders, products never ordered).

• You are performing reporting where the left side defines the “universe” of inter-
est, and right-side data is optional.

Vo Hoang Nhat Khang 110

 5.3 SQL Left Join

5.3.2 Handling NULLs in Left Joins

Because left joins preserve unmatched rows by filling right-side columns with NULL,
understanding how to work with these NULL values is crucial. Two common themes
are:

1. Detecting rows with no match on the right.

2. Avoiding the accidental conversion of a left join back into an inner join via the
WHERE clause.

Finding rows without matches

In the Customers–Orders example, Bob has no orders, so OrderID and TotalAmount are
NULL in his row. To find exactly those customers without any orders, we can use an IS
NULL predicate on a right-side column:

SELECT [Link],
[Link]
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link]
WHERE [Link] IS NULL;

The logic is:

• The left join produces one row per customer, with order information if it exists.

• For customers without orders, all columns from Orders are NULL.

• Filtering with WHERE [Link] IS NULL keeps only those rows with no match.

Choosing which right-side column to test with IS NULL is a matter of convenience;

typically we use a non-nullable key column such as OrderID.

ON vs. WHERE: preserving outer join behavior

A subtle but important point: for left joins, conditions placed in the WHERE clause can
unintentionally undo the “outer” part of the join and turn it back into something more
like an inner join.
Consider these two queries:

-- Query A
SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link]
WHERE [Link] > 100;

-- Query B

Vo Hoang Nhat Khang 111

5 Mastering Joins

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link]
AND [Link] > 100;

In Query A, the predicate [Link] > 100 is in the WHERE clause. For cus-
tomers without orders, [Link] is NULL, so the condition evaluates to UNKNOWN,
and those rows are filtered out. The practical effect is that customers with no orders
are removed from the result; we have effectively lost the outer join behavior for them.
In Query B, the predicate is moved into the ON clause. Conceptually:

• The join tries to match customers with orders where TotalAmount > 100.
• If no such order exists for a customer, the customer still appears in the result, but
with NULL in the order columns.

Thus, Query B preserves the left table, while Query A does not.
Note: Practical rule of thumb
For outer joins:

• Use the ON clause for conditions that control which rows from the right side
count as a “match”.

• Use the WHERE clause for conditions that should eliminate rows after the outer
join effect has been applied.

If you move a right-side condition from ON to WHERE and unintentionally drop all
the NULL rows, you have effectively turned your left join into an inner join.

Replacing NULLs for presentation

Sometimes, NULL values from the right side are acceptable internally but undesirable in
presentation. For example, when listing customers and their total spending, customers
with no orders might appear with NULL totals. For display or reporting, we may wish
to show zero instead.
Most SQL dialects provide functions for this, such as COALESCE, ISNULL, or IFNULL.
For example:
SELECT [Link],
[Link],
COALESCE([Link], 0) AS TotalAmount
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link];

Here, if [Link] is NULL, the query returns 0 instead. This does not change
the underlying join semantics, but it can make output easier to interpret.

Vo Hoang Nhat Khang 112

 5.3 SQL Left Join

5.3.3 Left Joins with Aggregates

Left joins frequently appear together with aggregate functions when we want sum-
maries that include entities with no related rows.
Example: Total spending per customer (including those with none)
Suppose we want to compute how much each customer has spent, including cus-
tomers with zero spent so far. Using a left join and GROUP BY:

SELECT [Link],
[Link],
COALESCE(SUM([Link]), 0) AS TotalSpent
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link]
GROUP BY [Link], [Link];

Key points:

• The left join ensures that every customer appears at least once, even if they
have no orders.

• For customers with no orders, [Link] is NULL. SUM() ignores NULLs

and returns NULL, which we then convert to 0 using COALESCE.

• The GROUP BY groups rows by customer, so we get one summary row per
customer.

This pattern—left join from a “dimension” table to a “fact” table, aggregate, then
replace NULLs with zeros—is extremely common in reporting queries.

5.3.4 Left Join in Relation to Inner and Right Joins

To place left joins in context, it is helpful to briefly compare them with inner joins and
(later) right joins:

• Inner join: keep only rows that have a match on both sides.

• Left join: keep all rows from the left side; attach matches from the right side
when available.

• Right join: symmetric to left join, keeping all rows from the right side (we will
discuss this separately).

When choosing between inner and left joins, ask:

“Am I willing to drop left-side rows that have no match? Or do I want to

keep them and show NULLs (or zeros) where related data is missing?”

If you want to keep those unmatched rows, a left join is usually the right tool.

Vo Hoang Nhat Khang 113

5 Mastering Joins

5.4 SQL Right Join

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN. It returns all
rows from the table on the “right” side of the join, along with matching rows from the
table on the left. When there is no matching row on the left, the result still contains the
right row, but the columns from the left table are filled with NULL.
In other words, where a left join preserves the left table and optionally adds data
from the right, a right join preserves the right table and optionally adds data from the
left.
Note: Mental model
Think of a right join as: “Start with every row from the right table. For each one,
try to attach matching data from the left table. If nothing matches, keep the right
row anyway and fill the left-hand columns with NULL.”

5.4.1 Basic Form and Symmetry with Left Join

The general syntax of a right join is:

SELECT column_list
FROM LeftTable AS l
RIGHT JOIN RightTable AS r
ON [Link] = [Link];

This guarantees that:

• Every row from RightTable appears at least once in the result.

• Rows from LeftTable appear only if they have at least one matching row on the
right according to the ON condition.

• When no matching left row exists, the left-side columns are NULL.

Conceptually, every right join can be rewritten as an equivalent left join by swap-
ping the roles of the tables. For example, suppose we have:

SELECT l.col1, r.col2

FROM A AS l
RIGHT JOIN B AS r
ON [Link] = [Link];

This is logically equivalent to:

SELECT l.col1, r.col2

FROM B AS r
LEFT JOIN A AS l
ON [Link] = [Link];

We have:

• Swapped the order of the tables in the FROM/JOIN clause,

Vo Hoang Nhat Khang 114

 5.4 SQL Right Join

• Changed RIGHT JOIN to LEFT JOIN,

• Preserved the join condition (adjusting aliases as needed).

Because of this symmetry, many developers prefer to standardize on left joins only,
reasoning that any right join can be mentally and syntactically converted into a left join.
This can make complex queries easier to read because all outer joins follow the same
left-preserving pattern.
Example: Orders and Customers
Consider an example where some orders may have been imported with customer
IDs that do not exist (or no longer exist) in the current Customers table. We want
to list all orders, along with any customer information we still have:

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
RIGHT JOIN Orders AS o
ON [Link] = [Link];

This query ensures:

• Every order in Orders appears in the result.

• If [Link] matches, we see the customer name.

• If no matching customer exists (for example, the customer was deleted),

CustomerName is NULL, but the order still appears.

Rewriting the same logic as a left join by swapping tables:

SELECT [Link],
[Link],
[Link]
FROM Orders AS o
LEFT JOIN Customers AS c
ON [Link] = [Link];

The result is identical; only the syntactic perspective has changed.

5.4.2 Handling NULLs in Right Joins

As with left joins, unmatched rows on the preserved side (here, the right side) produce
NULL values for all columns coming from the other table. Two common patterns mirror
those discussed for left joins:

1. Detecting right-side rows that have no match on the left.

2. Avoiding accidental conversion of a right join into an inner join.

Vo Hoang Nhat Khang 115

5 Mastering Joins

Finding right rows with no left match

Returning to the Customers–Orders example, suppose we want a list of orders that no
longer have a valid customer record. Using the right join form:

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
RIGHT JOIN Orders AS o
ON [Link] = [Link]
WHERE [Link] IS NULL;

The logic is:

• The right join preserves all orders.

• For orders whose customer no longer exists, all columns from Customers are
NULL.

• The WHERE clause keeps only those rows where [Link] IS NULL, i.e., or-
ders without a matching customer.

This is the “mirror image” of using a left join to find customers without orders.

ON vs. WHERE in right joins

The same caution that applies to left joins also applies here: placing conditions on the
preserved side in the WHERE clause can remove rows that you intended to keep.
Compare:

-- Query A: filter in WHERE

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
RIGHT JOIN Orders AS o
ON [Link] = [Link]
WHERE [Link] > 100;

-- Query B: filter in ON
SELECT [Link],
[Link],
[Link]
FROM Customers AS c
RIGHT JOIN Orders AS o
ON [Link] = [Link]
AND [Link] > 100;

In both queries, we preserve all rows from Orders because it is on the right-hand
side of a right join. However:

Vo Hoang Nhat Khang 116

 5.4 SQL Right Join

• In Query A, the WHERE clause discards result rows where [Link] is NULL
or ≤ 100.

• In Query B, the condition is part of the join: it controls which orders are con-
sidered “matching” for the left table, but orders still appear in the result even if
they do not satisfy [Link] > 100 (with some columns potentially NULL,
depending on the exact intent and database behavior).

Note: Same rule, mirrored

For a right join:

• Use the ON clause for conditions that control which rows from the left table
count as matches.

• Use the WHERE clause for conditions that should eliminate rows from the final
result, including rows from the right table.

For a left join, the preserved side is the left table; for a right join, the preserved side
is the right table.

5.4.3 When (and When Not) to Use RIGHT JOIN

Because right joins are symmetric with left joins, they are not strictly necessary from a
language-expressiveness standpoint: any query written with a right join can be rewrit-
ten using left joins only. Nevertheless, there are some practical considerations.

When it can make sense

• When the “natural” primary table is on the right. In some schemas or legacy
codebases, query authors may naturally think of one table as the central one,
but due to existing style or tooling, that table happens to be written on the right
side. Using RIGHT JOIN can preserve that ordering without rearranging the FROM
clause.

• When aligning with existing code or teaching symmetry. In educational con-

texts, right joins can be useful to illustrate the symmetry among left, right, and
full joins, or to explain that full outer joins conceptually combine left and right
join behavior.

• During incremental refactoring. Occasionally, when refactoring complex queries,

introducing a right join temporarily can preserve logic until the entire query is
rearranged. This is more of a transition tool than a final design choice.

Why many teams avoid RIGHT JOIN in practice

In day-to-day work, many teams standardize on left joins only. Common reasons in-
clude:

• Readability. It is often easier to reason about queries if the “main” table (the one
whose rows must be preserved) is always written first and all outer joins are left

Vo Hoang Nhat Khang 117

5 Mastering Joins

joins. This yields a consistent mental model: “start from this table, then attach
optional data from others.”

• Avoiding mixed directions. Queries that mix left, right, and full joins in complex
ways can be harder to understand. Using left joins only reduces cognitive load.

• Simpler refactoring. When everything is expressed with left joins, adding or re-
moving joins, or inserting additional filtering, is often simpler because the “flow”
of preserved rows is always left-to-right.

5.4.4 Converting a RIGHT JOIN to a LEFT JOIN

As a practical skill, it is useful to be able to quickly rewrite right joins as left joins men-
tally or in code. The mechanical steps are:

1. Swap the table order in the FROM/JOIN clause.

2. Change RIGHT JOIN to LEFT JOIN.

3. Ensure that the join condition still refers to the correct aliases.

For example:

-- Original right join

SELECT a.col1, b.col2
FROM A AS a
RIGHT JOIN B AS b
ON [Link] = [Link];

-- Equivalent left join

SELECT a.col1, b.col2
FROM B AS b
LEFT JOIN A AS a
ON [Link] = [Link];

In both cases, every row from B appears in the result, with matching columns from
A where they exist and NULLs otherwise.

5.4.5 Summary
Right joins are conceptually simple: they are just left joins viewed from the opposite
side. They preserve all rows from the right table and attach matching rows from the left
table when available. Although they can be useful in some contexts, many practitioners
prefer to write queries using left joins only, taking advantage of the fact that any right
join can be rewritten by swapping table order and changing the join direction.

5.5 SQL Full Join

A FULL JOIN (or FULL OUTER JOIN) combines the behavior of LEFT JOIN and RIGHT
JOIN. It returns:

Vo Hoang Nhat Khang 118

 5.5 SQL Full Join

• all rows that have matches on both sides (like an inner join),
• all rows from the left table that do not have matches on the right,
• all rows from the right table that do not have matches on the left.

When there is no matching row on one side, the columns from that side are filled
with NULL. Conceptually, a full join is the union of a left outer join and a right outer
join, with duplicates removed where both sides match.

5.5.1 Combining Left and Right Perspectives

The general syntax of a full join is:
SELECT column_list
FROM LeftTable AS l
FULL [OUTER] JOIN RightTable AS r
ON [Link] = [Link];

The keyword OUTER is optional in many systems.

Example: Customers and Orders (with incomplete data).
Assume:

• Some customers have no orders.

• Some legacy orders reference customer IDs that no longer exist in Customers.

We want to see a combined view that includes:

• customers with their orders, if they exist;

• customers with no orders;

• orders whose customers are missing.

A full join expresses this directly:

SELECT [Link],
[Link],
[Link],
[Link]
FROM Customers AS c
FULL OUTER JOIN Orders AS o
ON [Link] = [Link];

The result set contains:

• Rows where CustomerID exists in both tables: customer and order columns
are both populated.

• Rows where CustomerID exists only in Customers: order columns are NULL.

• Rows where CustomerID exists only in Orders: customer columns are NULL.

Vo Hoang Nhat Khang 119

5 Mastering Joins

Interpreting the result. Compared to other joins:

• Inner join keeps only matches on both sides.

• Left join keeps all left rows, plus matching right rows.

• Right join keeps all right rows, plus matching left rows.

• Full join keeps all rows from both sides, matching when possible, and using NULL
to represent missing partners.

This makes full joins useful for:

• Comparing two datasets (e.g., before/after snapshots, source vs. target).

• Detecting mismatches or gaps between tables (e.g., items that appear in one but
not the other).

Example: Reconciliation between two tables.

Suppose we have two tables OldCustomers and NewCustomers, both with
CustomerID. A full join can highlight which customers were added, removed, or
preserved:

SELECT [Link] AS OldID,

[Link] AS NewID
FROM OldCustomers AS o
FULL OUTER JOIN NewCustomers AS n
ON [Link] = [Link];

Interpreting the rows:

• OldID and NewID both non-NULL: customer exists in both versions.

• OldID non-NULL, NewID NULL: customer was removed.

• OldID NULL, NewID non-NULL: customer was added.

Additional columns (such as names or attributes) can be compared to detect

changes.

5.5.2 Emulating Full Joins in Systems Without Support

Not all SQL dialects support FULL OUTER JOIN natively. For example, some popular
systems provide only inner joins and left/right outer joins. In such environments, we
can emulate a full join using a combination of left join, right join, and UNION.

Pattern 1: LEFT JOIN + RIGHT JOIN + UNION

The idea is:

• Use a left join to get all rows from the left table (plus matches from the right).

Vo Hoang Nhat Khang 120

 5.5 SQL Full Join

• Use a right join to get all rows from the right table (plus matches from the left).

• Combine the two result sets with UNION (or UNION ALL with careful filtering) to
avoid duplicates.

A common template is:

SELECT [Link],
[Link],
[Link]
FROM LeftTable AS l
LEFT JOIN RightTable AS r
ON [Link] = [Link]

UNION

SELECT [Link],
[Link],
[Link]
FROM LeftTable AS l
RIGHT JOIN RightTable AS r
ON [Link] = [Link];

This union of a left join and a right join approximates a full outer join. When us-
ing UNION (not UNION ALL), duplicate rows produced where both sides matched are
eliminated, which matches the semantics of a typical full join.

Pattern 2: LEFT JOIN + anti-join (for systems without RIGHT JOIN)

Some systems lack both FULL OUTER JOIN and RIGHT JOIN (for example, some config-
urations of MySQL). In that case, we can construct the full join using:

• a left join from LeftTable to RightTable, plus

• rows from RightTable that do not appear in LeftTable, found via an anti-join
pattern (NOT EXISTS or LEFT JOIN + IS NULL).

Template:

-- Part 1: all left rows, with matches on the right

SELECT [Link],
[Link],
[Link]
FROM LeftTable AS l
LEFT JOIN RightTable AS r
ON [Link] = [Link]

UNION

-- Part 2: right-only rows (no match on the left)

SELECT [Link],
NULL AS ColA,

Vo Hoang Nhat Khang 121

5 Mastering Joins

[Link]
FROM RightTable AS r
LEFT JOIN LeftTable AS l
ON [Link] = [Link]
WHERE [Link] IS NULL;

Explanation:
• The first query returns all left rows (with matching right values or NULL).
• The second query finds rows in the right table that have no matching row in the
left table; for these, left-side columns are filled with NULL.
• The UNION of both sets yields the full outer join result.
This pattern generalizes to more complex schemas, though it requires careful align-
ment of column lists and aliases.

Choosing UNION vs. UNION ALL

• UNION removes duplicates. It is often the safer default when emulating full joins,
since rows where both sides match might otherwise appear twice (once from
each half of the union).
• UNION ALL preserves duplicates and may be more efficient, but then the sub-
queries must be constructed to avoid double-counting matched rows.
In most straightforward emulation patterns, UNION is sufficient and simpler to rea-
son about, especially when a primary key or a clear unique identifier is present.

5.6 SQL Self Join

A SELF JOIN is a join in which a table is joined to itself. At first this may sound re-
dundant, but it is extremely useful whenever relationships or comparisons exist within
a single table: hierarchies (employees and managers), versioned records (current vs.
previous), or pairs of rows that meet some condition relative to each other.
Because SQL requires each table reference in a query to have its own alias, self joins
are always written using at least two aliases for the same underlying table.
SELECT ...
FROM TableName AS t1
JOIN TableName AS t2
ON [Link] = [Link];

Here, t1 and t2 both refer to TableName, but they represent different roles in the
relationship (for example, child vs. parent, or earlier vs. later).

5.6.1 Hierarchies and Parent–Child Relationships

One of the most common uses of self joins is to represent hierarchical relationships,
such as employees and their managers, categories and subcategories, or parts and sub-
components.

Vo Hoang Nhat Khang 122

 5.6 SQL Self Join

Example: Employees and managers.

Consider an Employees table where each row optionally references the employees
manager via a ManagerID column:

EmployeeID Name ManagerID Department

1 Alice NULL Executive
2 Bob 1 Engineering
3 Carol 2 Engineering
4 David 2 Engineering
5 Eva 1 Sales

Here:

• Alice has no manager (ManagerID is NULL); she might be the CEO.

• Bob reports to Alice (ManagerID = 1).

• Carol and David report to Bob.

To list each employee together with their managers name, we can join Employees
to itself:

SELECT [Link],
[Link] AS EmployeeName,
[Link] AS ManagerID,
[Link] AS ManagerName
FROM Employees AS e
LEFT JOIN Employees AS m
ON [Link] = [Link];

The explanation would be:

• e represents the employee.

• m represents the manager.

• The join condition [Link] = [Link] links each employee to

their manager, if one exists.

• A left join is used so that top-level employees (with ManagerID = NULL) still
appear, but with NULL manager information.

The result might look like this:

EmployeeID EmployeeName ManagerID ManagerName

1 Alice NULL NULL
2 Bob 1 Alice
3 Carol 2 Bob
4 David 2 Bob
5 Eva 1 Alice

Vo Hoang Nhat Khang 123

5 Mastering Joins

Multi-level hierarchies. The self-join pattern can be extended to multiple levels. For
example, to see an employee, their manager, and their managers manager, we can join
the Employees table three times:

SELECT [Link] AS Employee,

[Link] AS Manager,
[Link] AS GrandManager
FROM Employees AS e
LEFT JOIN Employees AS m
ON [Link] = [Link]
LEFT JOIN Employees AS gm
ON [Link] = [Link];

This approach works well for fixed small depths (two or three levels). For arbitrary-
depth hierarchies (organizational trees, folder structures), we usually need recursive
queries (e.g., recursive common table expressions), which go beyond the basic self-join
pattern but build on the same idea of a table referencing itself.

5.6.2 Self Joins for Comparisons Within a Table

Self joins are also useful when comparing rows within a table or finding pairs of rows
that satisfy some condition relative to each other.
Example: Comparing employees within the same department.
Suppose we want to find pairs of employees who work in the same department.
We can join the Employees table to itself, matching on Department:

SELECT [Link] AS EmployeeID1,

[Link] AS EmployeeName1,
[Link] AS EmployeeID2,
[Link] AS EmployeeName2,
[Link]
FROM Employees AS e1
JOIN Employees AS e2
ON [Link] = [Link]
AND [Link] < [Link];

Key points:

• The condition [Link] = [Link] ensures that only employ-

ees in the same department are paired.

• The condition [Link] < [Link] prevents pairing each pair

twice (A with B and B with A) and avoids pairing an employee with them-
selves.

This pattern is helpful when the task is naturally about relationships between rows
of the same table (e.g., potential mentors and mentees, colleagues in the same team, or
items with similar attributes).

Vo Hoang Nhat Khang 124

 5.6 SQL Self Join

Example: Finding the latest record per group (self join vs. aggregate).
Consider a ProductPrices table storing multiple price records per product over
time:
ProductID PriceDate UnitPrice
101 2025-01-01 10.00
101 2025-02-01 11.50
101 2025-03-01 11.00

A common question is: “What is the most recent price for each product?” One
solution uses aggregates (MAX(PriceDate) with GROUP BY), but a self join can also
express this directly:

SELECT [Link],
[Link],
[Link]
FROM ProductPrices AS p1
LEFT JOIN ProductPrices AS p2
ON [Link] = [Link]
AND [Link] < [Link]
WHERE [Link] IS NULL;

Explanation:

• For each row p1, we look for a later row p2 with the same ProductID.

• If such a later row exists, p1 is not the latest price.

• The WHERE [Link] IS NULL clause keeps only those rows in p1 that
have no later partner—that is, the latest record per product.

This “anti-self-join” pattern generalizes to many “best per group” queries (latest,
earliest, highest, lowest) and is especially useful in systems where advanced window
functions are not available.

Common pitfalls with self joins. Because self joins involve multiple aliases of the
same table, they can be error-prone if the roles are not clearly labeled:

• Ambiguous intent. Using generic aliases such as t1, t2, t3 can make queries hard
to read. More descriptive aliases like e (employee), m (manager), or current /
previous improve clarity.

• Unconstrained joins. Forgetting part of the join condition (e.g., failing to restrict
to the same department or product) can produce a much larger result set than
intended.

• Symmetric duplicates. When finding pairs, failing to enforce an ordering like

[Link] < [Link] can result in each pair appearing twice.

Vo Hoang Nhat Khang 125

5 Mastering Joins

Vo Hoang Nhat Khang 126

Chapter 6 Combining and Analyzing Data

6.1 SQL Union and Union All

Up to now, our queries have usually drawn their results from a single SELECT statement.
In practice, however, it is often useful to combine the results of multiple queries into a
single result set. For example:

• combining rows from similar tables (e.g., current and archived orders),
• merging results from different conditions (e.g., high-value customers and new
customers),
• stitching together datasets from different sources or time periods.

SQL provides set-like operators to support this kind of combination. The most
fundamental are UNION and UNION ALL, which combine the outputs of two (or more)
SELECT statements into a single result. Intuitively:

• UNION behaves like a set union: duplicates are removed,

• UNION ALL behaves like a multiset (bag) union: duplicates are preserved.

Note: Mental model

Think of UNION as “stack the two result sets, then apply DISTINCT” and UNION ALL
as “just stack them, do not deduplicate.”

In this section, we examine both operators, their semantics with respect to dupli-
cates, and the rules governing the compatibility of the column lists being combined.

6.1.1 SQL UNION

The UNION operator combines the results of two queries and removes duplicate rows
from the combined set. Its general form is:

SELECT column_list
FROM TableA
WHERE ...

UNION

SELECT column_list
FROM TableB
WHERE ...;

Vo Hoang Nhat Khang 127

6 Combining and Analyzing Data

Conceptually:

1. Each SELECT is evaluated independently, producing two intermediate result sets.

2. The two sets are concatenated.

3. Duplicate rows (considering all columns in the result) are removed.

Example: Combining customers from different regions

Suppose we want a single list of customers from Germany and Sweden, perhaps
because they are handled by the same service team. We could write:

SELECT CustomerID, CustomerName, Country

FROM Customers
WHERE Country = 'Germany'

UNION

SELECT CustomerID, CustomerName, Country

FROM Customers
WHERE Country = 'Sweden';

If a customer somehow appears in both subsets with exactly the same values in the
selected columns, UNION will include only one copy of that row in the final result.

UNION vs. repeated OR conditions. In many simple cases, a UNION of two queries
against the same table can be expressed instead as a single query with an OR in the
WHERE clause:

SELECT CustomerID, CustomerName, Country

FROM Customers
WHERE Country = 'Germany'
OR Country = 'Sweden';

This may be more efficient or easier for the optimizer to handle, and it avoids writing
two SELECT blocks.
However, UNION becomes essential when combining:

• results from different tables,

• queries that use different joins or groupings,

• or queries against different views or databases.

Ordering results with UNION. When using UNION, any ORDER BY clause applies to
the combined result set, not to the individual queries. The canonical pattern is:

SELECT ...
FROM ...
WHERE ...
UNION

Vo Hoang Nhat Khang 128

 6.1 SQL Union and Union All

SELECT ...
FROM ...
WHERE ...
ORDER BY ColumnName;

If we want to order each part separately before combining, we typically use sub-
queries or common table expressions (CTEs), for example:
Example: Ordering each side before UNION
SELECT *
FROM (
SELECT CustomerID, CustomerName, Country
FROM Customers
WHERE Country = 'Germany'
) AS g

UNION

SELECT *
FROM (
SELECT CustomerID, CustomerName, Country
FROM Customers
WHERE Country = 'Sweden'
) AS s
ORDER BY CustomerName;

Here, each subset is defined in a subquery, but the final ORDER BY still applies to
the union as a whole.

6.1.2 SQL UNION ALL

The UNION ALL operator is similar to UNION, but it does not remove duplicates. Instead,
it concatenates the results of the input queries and returns all rows exactly as they
appear.
SELECT column_list
FROM TableA
WHERE ...

UNION ALL

SELECT column_list
FROM TableB
WHERE ...;

Example: Combining logs from two sources

Suppose we store application logs in two tables, AppLogs_2024 and AppLogs_2025,
with identical schemas. To view all logs across both years:

SELECT LogDate, Severity, Message

FROM AppLogs_2024

Vo Hoang Nhat Khang 129

6 Combining and Analyzing Data

UNION ALL

SELECT LogDate, Severity, Message

FROM AppLogs_2025;

If the same log entry appears in both tables, UNION ALL will return two rows. This is
often desirable when logs are append-only records; duplicates may reflect repeated
events.

Performance considerations. Because UNION ALL does not have to remove duplicates,
it generally requires less work than UNION. There is no need to sort or hash the combined
result set to detect duplicates, which can be significant on large datasets.
A practical rule:

• Use UNION only when you genuinely need duplicate elimination.

• Prefer UNION ALL when duplicates are acceptable or meaningful, or when you
know by design that the two input sets are disjoint.

6.1.3 Set Semantics and Duplicates

The difference between UNION and UNION ALL reflects a deeper distinction between set
semantics and bag (multiset) semantics.

• UNION: set semantics. The combined result behaves like a mathematical set:
each distinct row appears at most once.

• UNION ALL: bag semantics. The combined result is a multiset: each row ap-
pears as many times as it appears in the inputs.

Example: Counting rows with UNION vs. UNION ALL

Assume two queries produce the following intermediate results (same column lay-
out):

Query 1 result Query 2 result

Alice Bob
Bob Carol

Then:

• UNION yields {Alice, Bob, Carol} (3 rows).

• UNION ALL yields {Alice, Bob, Bob, Carol} (4 rows).

If we wrap these in SELECT COUNT(*) FROM (...) queries, the counts will differ
accordingly, because UNION removes duplicates while UNION ALL preserves them.

Vo Hoang Nhat Khang 130

 6.1 SQL Union and Union All

Relationship to DISTINCT. A useful mental model is:

Query1
UNION
Query2

behaves like:

SELECT DISTINCT *
FROM (
Query1
UNION ALL
Query2
) AS Combined;

That is, UNION is equivalent to UNION ALL followed by a global DISTINCT over the
combined rows.

6.1.4 Compatibility of Column Lists

For UNION and UNION ALL to work correctly, the SELECT statements they combine must
be union-compatible. Although the exact rules vary slightly by implementation, the core
requirements are:

• The number of columns in each SELECT must be the same.

• Corresponding columns (by position) must have compatible data types.

Example: Matching column counts

The following is valid:

SELECT CustomerID, CustomerName, Country

FROM Customers_EU

UNION ALL

SELECT CustomerID, CustomerName, Country

FROM Customers_US;

But this will typically fail:

-- 3 columns in the first query, 2 in the second

SELECT CustomerID, CustomerName, Country
FROM Customers_EU

UNION ALL

SELECT CustomerID, CustomerName

FROM Customers_US;

because the two queries do not project the same number of columns.

Vo Hoang Nhat Khang 131

6 Combining and Analyzing Data

Data type compatibility. Each column position in the union is treated as a single log-
ical column in the combined result. The database must be able to determine a common
type for each pair of corresponding expressions. For example:
Example: Type compatibility across tables
SELECT CustomerID, Country
FROM Customers

UNION ALL

SELECT SupplierID, Country

FROM Suppliers;

This is typically acceptable, provided that CustomerID and SupplierID have com-
patible numeric types, and both Country columns are character types. The engine
will implicitly convert as needed to a common type. If not, we can cast explicitly:

SELECT CustomerID,
CAST(Country AS VARCHAR(50)) AS Country
FROM Customers

UNION ALL

SELECT SupplierID,
CAST(Country AS VARCHAR(50)) AS Country
FROM Suppliers;

Column names in the result. In most SQL dialects, the column names of a union
result are taken from the first SELECT statement.
Example: Column names from the first SELECT
SELECT CustomerID AS ID, CustomerName AS Name
FROM Customers

UNION ALL

SELECT SupplierID, SupplierName

FROM Suppliers;

The combined result will typically have columns named ID and Name, even though
the second query uses different underlying column names. If clearer names are
desired, we can wrap the entire union in an outer query:

SELECT ID AS EntityID,
Name AS EntityName
FROM (
SELECT CustomerID AS ID, CustomerName AS Name
FROM Customers
UNION ALL
SELECT SupplierID, SupplierName

Vo Hoang Nhat Khang 132

 6.2 SQL Group By

FROM Suppliers
) AS u;

6.2 SQL Group By

The GROUP BY clause allows SQL to move from row-by-row retrieval to set-level sum-
marization. Instead of returning one result row per input row, a query with GROUP BY
partitions the input into groups of rows that share the same value(s) for one or more
columns, and then computes a single result row per group using aggregate functions
(such as COUNT, SUM, AVG, MIN, MAX).
Conceptually, GROUP BY answers questions of the form:
“For each value (or combination of values) in column(s) X, summarize Y.”
For example:
• “For each customer, what is the total amount spent?”
• “For each country, how many customers do we have?”
• “For each month, what is the average order value?”
In this section, we will first clarify the semantics of grouping, then show how ag-
gregates interact with GROUP BY, and finally discuss common errors related to non-
grouped columns.

6.2.1 Grouping Semantics

The basic form of a query with grouping is:
SELECT group_column1,
group_column2,
...,
aggregate_expression1,
aggregate_expression2,
...
FROM SomeTable
WHERE ...
GROUP BY group_column1,
group_column2,
...;
Logically, the database engine performs the following steps:
1. Apply the FROM and WHERE clauses to obtain a filtered set of rows.
2. Partition this set into groups based on the values of the GROUP BY columns.
3. For each group, evaluate the aggregate expressions and produce one output row.
Within each group, non-aggregated columns in the SELECT list must be functionally
determined by the GROUP BY columns; in practice, this is enforced syntactically by re-
quiring that every non-aggregated column appearing in the SELECT list also appears in
the GROUP BY clause (with some dialect-specific exceptions, discussed later).

Vo Hoang Nhat Khang 133

6 Combining and Analyzing Data

Example: Counting customers by country

Consider a Customers table:
CustomerID CustomerName Country
1 Alice Germany
2 Bob Sweden
3 Carol Germany
4 David Sweden
5 Eva Germany

To count how many customers are in each country:

SELECT Country,
COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country;

The GROUP BY Country clause partitions the rows into two groups:

• Group 1: all rows with Country = ’Germany’.

• Group 2: all rows with Country = ’Sweden’.

For each group, COUNT(*) returns the number of rows in that group. The result
might be:

Country CustomerCount
Germany 3
Sweden 2

Example: Grouping by multiple columns

Suppose we have an Orders table:

OrderID CustomerID OrderDate TotalAmount

10 1 2025-01-01 120.00
11 1 2025-01-15 80.00
12 2 2025-02-01 50.00
13 3 2025-02-10 70.00

To summarize total spending per customer per year:

SELECT CustomerID,
YEAR(OrderDate) AS OrderYear,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID,
YEAR(OrderDate);

Here, each group is defined by a pair of values: (CustomerID, OrderYear). The

SUM is computed independently in each such group.

Vo Hoang Nhat Khang 134

 6.2 SQL Group By

6.2.2 Aggregates with Group By

Aggregate functions compute a single value from the set of rows in each group. Com-
mon aggregates include:
• COUNT() – number of rows (or non-NULL values),
• SUM() – total of numeric values,
• AVG() – average of numeric values,
• MIN(), MAX() – minimum and maximum values.
When used with GROUP BY, each aggregate operates within the group to which the
current result row corresponds.
Example: Average order value per customer
To compute the average order value for each customer:

SELECT CustomerID,
COUNT(*) AS OrderCount,
SUM(TotalAmount) AS TotalSpent,
AVG(TotalAmount) AS AvgOrderValue
FROM Orders
GROUP BY CustomerID;

For each CustomerID, we get:

• OrderCount: number of orders in the group,

• TotalSpent: sum of TotalAmount in the group,

• AvgOrderValue: average of TotalAmount in the group.

Aggregates on expressions. The argument to an aggregate function need not be a

simple column reference; it can be any expression.
Example: Aggregates on expressions and distinct values
To compute total revenue including tax:

SELECT CustomerID,
SUM(TotalAmount * 1.10) AS TotalWithTax
FROM Orders
GROUP BY CustomerID;

Or, to count distinct customers per country using a join between Customers and
Orders:

SELECT [Link],
COUNT(DISTINCT [Link]) AS CustomersWithOrders
FROM Customers AS c
LEFT JOIN Orders AS o

Vo Hoang Nhat Khang 135

6 Combining and Analyzing Data

ON [Link] = [Link]
GROUP BY [Link];

Here, the combination of LEFT JOIN, GROUP BY, and COUNT(DISTINCT ...) allows
us to count how many distinct customers in each country have placed at least one
order.

Interaction with HAVING (preview). The HAVING clause filters groups after aggre-
gation, whereas WHERE filters individual rows before grouping.
Example: Filtering groups with HAVING
To select only customers whose total spending exceeds 200:

SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 200;

Here, all rows are grouped by CustomerID first, then HAVING removes groups whose
SUM(TotalAmount) is 200 or less.

We will discuss HAVING in detail in the next section; here, it is enough to note that
aggregates can appear in HAVING but not in WHERE.

6.2.3 Common Errors (Non-Grouped Columns)

A frequent source of confusion with GROUP BY is the handling of non-grouped, non-
aggregated columns in the SELECT list. Most SQL dialects that closely follow the stan-
dard enforce a simple rule:
Every column in the SELECT list must either
• appear in the GROUP BY clause, or
• be part of an aggregate expression.
Violating this rule typically results in an error (for example, “column X is not func-
tionally dependent on the group by columns”).
Example: An invalid GROUP BY query
Consider:

SELECT Country,
CustomerName, -- problematic
COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country;

Here, Country is grouped, and COUNT(*) is an aggregate, but CustomerName is nei-

ther. For a given country group (e.g., Germany), there may be multiple different

Vo Hoang Nhat Khang 136

 6.2 SQL Group By

customer names. The database cannot know which CustomerName you intend to
display for that group, so most systems reject this query.
To make the query well-defined, we must either include CustomerName in the
grouping:

SELECT Country,
CustomerName,
COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country, CustomerName;

or aggregate it somehow (for example, by counting distinct names):

SELECT Country,
COUNT(*) AS CustomerCount,
COUNT(DISTINCT CustomerName) AS DistinctCustomerNames
FROM Customers
GROUP BY Country;

Dialect-specific behavior (only_full_group_by). Historically, some systems (notably

older MySQL configurations) allowed non-grouped, non-aggregated columns in the
SELECT list, returning arbitrary values from within each group.
Example: Non-strict GROUP BY behavior in some dialects
MySQL without ONLY_FULL_GROUP_BY enabled might accept:

SELECT Country,
CustomerName,
COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country;

In such configurations, CustomerName might be taken from some unspecified row

in each group, leading to non-deterministic behavior.

Modern best practice is to enable strict SQL mode (such as ONLY_FULL_GROUP_BY)

and avoid relying on this behavior.
For portable and logically sound SQL, always ensure that:

• Every non-aggregated column in SELECT, ORDER BY, and HAVING appears in the
GROUP BY list, or

• You can justify that it is functionally dependent on the grouped columns (in ad-
vanced systems that support this optimization).

Aggregates in WHERE vs. HAVING. Another common error is attempting to use

aggregate functions in the WHERE clause:

Vo Hoang Nhat Khang 137

6 Combining and Analyzing Data

Example: WHERE vs HAVING with aggregates

-- Invalid in standard SQL
SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
WHERE SUM(TotalAmount) > 200 -- not allowed
GROUP BY CustomerID;

Because WHERE is evaluated before grouping, aggregates are not yet defined there.
The correct form uses HAVING:

SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 200;

6.3 SQL Having

The HAVING clause is closely related to GROUP BY and aggregate functions. While WHERE
filters individual rows before grouping, HAVING filters groups of rows after aggregation.
Conceptually:

WHERE answers: Which rows should participate in the grouping?

HAVING answers: Which groups should be kept after we summarize them?

This distinction becomes crucial whenever a condition depends on an aggregate,

such as total spending > 200, average rating ≥ 4, or count of orders ≥ 5. Such con-
ditions cannot be evaluated on individual rows; they only make sense once rows have
been grouped and aggregated.

6.3.1 Filtering Groups vs. Filtering Rows

A typical query with grouping and HAVING looks like this:

SELECT group_column,
aggregate_expression
FROM ...
WHERE row_condition
GROUP BY group_column
HAVING group_condition_on_aggregates;

The logical order of evaluation is:

1. FROM + JOIN construct the initial row set.

2. WHERE filter individual rows.

3. GROUP BY partition remaining rows into groups.

Vo Hoang Nhat Khang 138

 6.3 SQL Having

4. Aggregate functions compute summaries per group.

5. HAVING filter groups based on aggregates or grouped columns.
6. SELECT project expressions for the surviving groups.
7. ORDER BY sort the final result (if requested).
Example: Customers with total spending above a threshold
Suppose we want to find customers whose total spending exceeds 200. Using an
Orders table with CustomerID and TotalAmount:

SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 200;

Here:

• GROUP BY CustomerID forms one group per customer.

• SUM(TotalAmount) computes total spending per customer.

• HAVING SUM(TotalAmount) > 200 keeps only groups (customers) whose to-
tal exceeds 200.

By contrast, the WHERE clause cannot refer to SUM in this way, because aggregation
has not yet occurred at the point where WHERE is evaluated.

Combining WHERE and HAVING. WHERE and HAVING often appear together in the
same query, each serving a distinct purpose.
Example: Combining WHERE and HAVING
Suppose we want:
Among orders placed in 2025, find customers whose total spending in
that year exceeds 500.
We can write:
SELECT CustomerID,
SUM(TotalAmount) AS Total2025
FROM Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 500;

Interpretation:
• The WHERE clause restricts the input to orders from the year 2025. Orders from
other years are completely excluded from the grouping.

Vo Hoang Nhat Khang 139

6 Combining and Analyzing Data

• The GROUP BY and SUM compute each customers total spending within 2025.

• The HAVING clause filters out customers whose total is at most 500.

A common optimization is to push as many row-level conditions as possible into

WHERE, so that fewer rows participate in grouping and aggregation.

6.3.2 Using Aggregates in Having

The defining feature of HAVING is that it can reference aggregate functions. This allows
us to express conditions over group-level summaries.
Example: Filtering groups by aggregate values
A typical pattern is to apply thresholds to counts, sums, or averages. For example:

• Customers with at least 3 orders:

SELECT CustomerID,
COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) >= 3;

• Products whose total quantity sold exceeds 1 000 units:

SELECT ProductID,
SUM(Quantity) AS TotalUnits
FROM OrderDetails
GROUP BY ProductID
HAVING SUM(Quantity) > 1000;

• Departments with an average salary above a threshold:

SELECT Department,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 70000;

In each case, the aggregate expresses a property of the group, and HAVING selects
only those groups whose property satisfies a condition.

HAVING can also refer to grouped columns without aggregates.

Example: Using non-aggregate columns in HAVING
For example, to filter out a particular group by name:
SELECT Country,
COUNT(*) AS CustomerCount
FROM Customers

Vo Hoang Nhat Khang 140

 6.3 SQL Having

GROUP BY Country
HAVING Country <> 'Unknown';

This is logically allowed, though many developers prefer to place purely non-
aggregate conditions in WHERE or GROUP BY for clarity. A more typical rewrite would
be:

SELECT Country,
COUNT(*) AS CustomerCount
FROM Customers
WHERE Country <> 'Unknown'
GROUP BY Country;

The choice can also influence performance, since WHERE filters rows before group-
ing.

Example: Using HAVING without GROUP BY

Some SQL dialects allow HAVING to be used even without an explicit GROUP BY. In
that case, the entire result set is treated as a single group. For example:

SELECT SUM(TotalAmount) AS GrandTotal

FROM Orders
HAVING SUM(TotalAmount) > 100000;

Here:

• If the grand total exceeds 100 000, one row is returned.

• Otherwise, no rows are returned.

This pattern behaves similarly to a WHERE on an aggregate, but written using HAVING.

Caution: Aggregates in WHERE

A frequent error is attempting to use aggregate functions in the WHERE clause:
-- Invalid in standard SQL
SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
WHERE SUM(TotalAmount) > 200 -- not allowed
GROUP BY CustomerID;

Because WHERE is evaluated before aggregation, the expression SUM(TotalAmount)

is not yet defined at that stage. The correct query uses HAVING:
SELECT CustomerID,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > 200;

Vo Hoang Nhat Khang 141

6 Combining and Analyzing Data

6.4 SQL Exists

The EXISTS predicate is used to test whether a subquery returns at least one row. Rather
than returning data from the subquery, EXISTS returns a simple Boolean: TRUE if the
subquery yields any rows, FALSE otherwise.
Conceptually, EXISTS answers questions of the form:
“Does there exist at least one related row that satisfies this condition?”
This makes EXISTS particularly useful for expressing relationships between tables,
enforcing logical conditions, and writing queries that read naturally in terms of exis-
tence or non-existence of related data.

6.4.1 Correlated Subqueries

The most common use of EXISTS involves a correlated subquery: a subquery that refers
to columns from the outer query. For each row of the outer query, the subquery is
evaluated with those column values, and EXISTS checks whether any matching row is
found.
Example: Customers who have placed at least one order
Suppose we have:

• Customers(CustomerID, CustomerName, Country, ...)

• Orders(OrderID, CustomerID, OrderDate, TotalAmount, ...)

We want to list customers who have placed at least one order. Using EXISTS:

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
WHERE EXISTS (
SELECT 1
FROM Orders AS o
WHERE [Link] = [Link]
);

Key points:

• The inner query is correlated with the outer query via [Link] =
[Link].

• For each customer c, the database checks whether there is at least one order
o with the same CustomerID.

• If such an order exists, the EXISTS predicate is TRUE, and the customer is in-
cluded in the result.

The inner query uses SELECT 1 rather than SELECT * because EXISTS cares only
about the presence of rows, not their contents. Any constant expression would do.

Vo Hoang Nhat Khang 142

 6.4 SQL Exists

Example: Employees who manage someone

Consider an Employees table with a ManagerID column. To find employees who are
managers (i.e., at least one person reports to them):

SELECT [Link],
[Link]
FROM Employees AS e
WHERE EXISTS (
SELECT 1
FROM Employees AS sub
WHERE [Link] = [Link]
);

Here, the correlated subquery searches for at least one subordinate (sub) whose
ManagerID matches the outer employee. If found, the outer employee is considered
a manager.

Relationally, a query of the form:

SELECT ...
FROM A
WHERE EXISTS (
SELECT 1
FROM B
WHERE [Link] = [Link]
AND ...additional conditions...
);
expresses a semijoin: retain only those rows in A that have at least one matching row
in B. The subquery defines the relationship, and EXISTS turns it into a Boolean test.

6.4.2 Exists vs. In

EXISTS and IN often appear in similar queries and can be logically equivalent in many
common cases. However, they differ in syntax, emphasis, and their interaction with
NULL values.
Example: IN with a subquery
A typical pattern with IN is:

SELECT [Link],
[Link]
FROM Customers AS c
WHERE [Link] IN (
SELECT [Link]
FROM Orders AS o
);

This selects customers whose CustomerID appears in the set of CustomerID values
returned by the subquery. Conceptually, the subquery produces a set of values,
and IN tests membership in that set.

Vo Hoang Nhat Khang 143

6 Combining and Analyzing Data

Example: EXISTS formulation of the same query

The same logical condition can be expressed with EXISTS:

SELECT [Link],
[Link]
FROM Customers AS c
WHERE EXISTS (
SELECT 1
FROM Orders AS o
WHERE [Link] = [Link]
);

Here:

• IN emphasizes membership in a set of values.

• EXISTS emphasizes the existence of related rows.

Modern query optimizers often transform one pattern into the other internally, so
performance differences are usually minimal when indexes and statistics are appropri-
ate. However, there are two conceptual differences worth noting.

NULL-handling differences. The most important behavioral difference is how IN in-

teracts with NULL. Suppose the subquery in an IN clause can produce NULL:
Example: NULLs in an IN subquery
SELECT [Link]
FROM Customers AS c
WHERE [Link] IN (
SELECT ReferralCustomerID
FROM Referrals
);

If ReferralCustomerID is nullable and the subquery returns NULL among other val-
ues, the semantics of IN can become subtle, especially for the negated form NOT IN.
Comparisons involving NULL may yield UNKNOWN, and an entire NOT IN predicate may
end up evaluating to UNKNOWN (and thus filter out all rows) unless NULLs are excluded
explicitly.
By contrast, EXISTS is typically immune to this issue: it simply checks whether the
correlated subquery returns any row, regardless of the presence of NULLs in the selected
columns. For this reason, EXISTS (and especially NOT EXISTS) is often preferred in
queries where NULLs might appear in the subquery result.

Readability and intent. From a design perspective:

• Use IN when thinking in terms of values: is this value in that set of values?.

• Use EXISTS when thinking in terms of relationships: does a related row satisfying
this condition exist?.

Vo Hoang Nhat Khang 144

 6.4 SQL Exists

Although both can be used in many circumstances, choosing the one that matches
your mental model often makes the query easier to read and maintain.

6.4.3 Anti-Semijoin Patterns with Not Exists

The negated form, NOT EXISTS, is a powerful way to express anti-semijoins: select rows
in one table for which no corresponding row exists in another table. This is the natural
SQL formulation of questions such as:

• Which customers have never placed an order?

• Which products have never been ordered?
• Which employees are not assigned to any project?
Example: Customers with no orders
To find customers who have never placed an order:

SELECT [Link],
[Link]
FROM Customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM Orders AS o
WHERE [Link] = [Link]
);

Interpretation:

• For each customer c, the correlated subquery looks for at least one order o
with the same CustomerID.

• If such an order is found, EXISTS is TRUE, and the NOT EXISTS becomes FALSE
(the customer is excluded).

• If no such order is found, EXISTS is FALSE, so NOT EXISTS is TRUE and the
customer is included.

Example: NOT IN vs. NOT EXISTS

The same intent might be phrased using NOT IN:

SELECT [Link],
[Link]
FROM Customers AS c
WHERE [Link] NOT IN (
SELECT [Link]
FROM Orders AS o
);

However, if [Link] can ever be NULL, this predicate can behave unexpect-
edly: the presence of NULL in the subquery result may cause NOT IN to evaluate to

Vo Hoang Nhat Khang 145

6 Combining and Analyzing Data

UNKNOWN for all rows, returning no results. To avoid such pitfalls, many practition-
ers prefer the NOT EXISTS formulation, which is robust in the presence of NULLs:

WHERE NOT EXISTS (

SELECT 1
FROM Orders AS o
WHERE [Link] = [Link]
);

Example: Anti-join pattern with composite keys

NOT EXISTS is also very effective when the relationship is defined by multiple
columns. For example, consider a table of Invoices and a table of Payments, where
a payment references an invoice by both InvoiceID and CustomerID. To find in-
voices that have not been paid:

SELECT [Link],
[Link],
[Link]
FROM Invoices AS i
WHERE NOT EXISTS (
SELECT 1
FROM Payments AS p
WHERE [Link] = [Link]
AND [Link] = [Link]
);

Here, NOT EXISTS expresses a precise anti-join over the composite key (InvoiceID,
CustomerID).

Example: NOT EXISTS with LEFT JOIN and IS NULL

In some query styles, the same logic is written using a left join and an IS NULL
filter:
SELECT [Link],
[Link]
FROM Customers AS c
LEFT JOIN Orders AS o
ON [Link] = [Link]
WHERE [Link] IS NULL;
This pattern:
• Left joins customers to orders,
• Preserves all customers,
• Filters to those for whom no order row exists (because all columns from
Orders are NULL).
From a logical perspective, this is equivalent to NOT EXISTS. Which form to use is
largely a matter of style and the specifics of the query optimizer, but NOT EXISTS
often expresses the no related row exists intent more directly.

Vo Hoang Nhat Khang 146

 6.5 SQL Any and All

6.5 SQL Any and All

The ANY (or SOME) and ALL keywords are quantifiers used in combination with compar-
ison operators and subqueries. They allow you to express conditions such as:

• greater than at least one value in this set,

• less than every value in this set,

• equal to any of these values.

Where EXISTS asks does some row exist?, ANY and ALL ask does this comparison hold
for some or all of these values?.

6.5.1 Quantified Comparisons

The general pattern is:

expression comparison_operator ANY (subquery)

expression comparison_operator ALL (subquery)

where:

• comparison_operator is one of =, <>, >, >=, <, <=,

• the subquery returns a single column of comparable values.

ANY (or SOME). expression > ANY (subquery) means:

“The expression is greater than at least one value returned by the subquery.”

More generally,

x op ANY({v1 , v2 , . . . , vn })
is logically equivalent to:

(x op v1 ) OR (x op v2 ) OR · · · OR (x op vn ).

ALL. expression > ALL (subquery) means:

“The expression is greater than every value returned by the subquery.”

More generally,

x op ALL({v1 , v2 , . . . , vn })
is logically equivalent to:

(x op v1 ) AND (x op v2 ) AND · · · AND (x op vn ).

Vo Hoang Nhat Khang 147

6 Combining and Analyzing Data

Example: Examples with numeric comparisons

Suppose a subquery returns the set of purchase quantities for a given product:

{5, 10, 20}

Then:

• Quantity > ANY (subquery) is true if Quantity is greater than 5 or 10 or 20;

effectively, greater than the minimum (5), though the exact logic is greater
than at least one value.

• Quantity > ALL (subquery) is true if Quantity is greater than 5 and 10 and
20; effectively, greater than the maximum (20).

This leads to useful patterns such as greater than all competitors prices or less than
any previous recorded value.

6.5.2 Any vs. Some vs. All

In standard SQL, SOME is a synonym for ANY. Most systems treat them identically:
price > ANY (subquery)
price > SOME(subquery) -- equivalent
The choice between ANY and SOME is largely stylistic. ANY is more common in practice;
SOME can sometimes read more naturally in English.
Equality with ANY.
Example: Matching any country from recent orders
Goal: Find customers whose country matches any country from a list of recent
orders.

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
WHERE [Link] = ANY (
SELECT DISTINCT Country
FROM Orders
);

This is logically similar to:

SELECT [Link],
[Link],
[Link]
FROM Customers AS c
WHERE [Link] IN (
SELECT DISTINCT Country
FROM Orders
);

Vo Hoang Nhat Khang 148

 6.5 SQL Any and All

Indeed, = ANY over a subquery is usually equivalent to IN (with some nuances

around NULL).

Inequalities with ANY and ALL.

Example: Comparing product prices to competitors
Goal 1: Find products whose price is higher than all prices of a competing product.

SELECT [Link],
[Link],
[Link]
FROM Products AS p
WHERE [Link] > ALL (
SELECT [Link]
FROM CompetitorProducts AS c
WHERE [Link] = [Link]
);

Here:

• The subquery returns a set of competitor prices in the same category.

• > ALL means the product is more expensive than each of those prices (strictly
greater than the maximum).

Goal 2: Find products cheaper than any competitor in the same category:

SELECT [Link],
[Link],
[Link]
FROM Products AS p
WHERE [Link] < ANY (
SELECT [Link]
FROM CompetitorProducts AS c
WHERE [Link] = [Link]
);

This is equivalent to less than at least one competitor price; if translated to extrema,
effectively less than the maximum competitor price, but the logical meaning is there
exists at least one competitor that is more expensive.

Empty subquery results and NULLs. The behavior of ANY and ALL when the sub-
query returns no rows or includes NULLs can be subtle:

• If the subquery returns no rows:

– x > ANY (empty set) is FALSE (no value to satisfy the comparison).
– x > ALL (empty set) is TRUE (vacuously true: x is greater than every value
in the empty set).

Vo Hoang Nhat Khang 149

6 Combining and Analyzing Data

• If the subquery includes NULL values, comparisons with those NULLs yield UNKNOWN,
and SQLs three-valued logic influences the overall result. In practice, it is often
safest to filter out NULLs in the subquery:

... ANY (
SELECT SomeValue
FROM ...
WHERE SomeValue IS NOT NULL
)

Because of these subtleties, many developers prefer EXISTS / NOT EXISTS for pres-
ence/absence and use ANY/ALL primarily for genuinely numeric or ordered compar-
isons.

6.5.3 Practical Query Patterns

Although ANY and ALL are less commonly used than joins or EXISTS, they shine in cer-
tain patterns.

Pattern 1: comparison to the minimum or maximum of a set

Instead of using an aggregate subquery, we can express comparisons via ALL and ANY:

• Greater than the maximum competitor price:

WHERE [Link] > ALL (

SELECT [Link]
FROM CompetitorProducts AS c
);

• Less than the minimum competitor price:

WHERE [Link] < ALL (

SELECT [Link]
FROM CompetitorProducts AS c
);

These are logically equivalent to using aggregates:

WHERE [Link] > (

SELECT MAX([Link])
FROM CompetitorProducts AS c
);

WHERE [Link] < (

SELECT MIN([Link])
FROM CompetitorProducts AS c
);

Using ALL / ANY can sometimes be more natural when the subquery structure is
complex or when you want to emphasize the quantified comparison.

Vo Hoang Nhat Khang 150

 6.5 SQL Any and All

Pattern 2: threshold relative to peers (per group)

Suppose we want employees whose salary is above every salary in another department.
Using ALL:

SELECT [Link],
[Link],
[Link]
FROM Employees AS e
WHERE [Link] = 'Engineering'
AND [Link] > ALL (
SELECT Salary
FROM Employees
WHERE Department = 'Sales'
);

Here, each engineers salary is compared to the entire set of salaries in Sales. Only
engineers whose salary is greater than all Sales salaries are returned.

Pattern 3: value matching any of several correlated values

Consider an Orders table and an OrderDetails table containing line items. We might
want to find orders whose total amount is greater than any single lines price in a related
promotion list:

SELECT [Link],
[Link]
FROM Orders AS o
WHERE [Link] > ANY (
SELECT [Link]
FROM OrderDetails AS d
WHERE [Link] = [Link]
);

This means there exists at least one line item whose unit price is less than the or-
der total a somewhat contrived example, but it illustrates that the subquery can be
correlated and still used with ANY/ALL.

Pattern 4: rewriting in terms of EXISTS and aggregates

Many ANY/ALL queries can be rewritten in other forms:

• = ANY (subquery) is often equivalent to IN (subquery).

• > ALL (subquery) can often be rewritten using a scalar aggregate such as > (SELECT
MAX(...)).

• Patterns like greater than any can be expressed as there exists a row with value
less than x using EXISTS.

For example:

WHERE x > ANY (SELECT value FROM T)

Vo Hoang Nhat Khang 151

6 Combining and Analyzing Data

is equivalent (under suitable assumptions and ignoring NULL subtleties) to:

WHERE EXISTS (
SELECT 1
FROM T
WHERE value < x
);

Vo Hoang Nhat Khang 152

Part III Building & Managing
Databases

Vo Hoang Nhat Khang 153

Chapter 7 Database Management

Relational databases do not exist only as collections of tables and queries. They must
also be created, named, organized, backed up, and sometimes carefully removed. In
most production environments, the lifecycle of a database—from its initial creation
through growth, backup, restoration, and eventual decommissioning—is as important
as the design of any individual schema.
This chapter introduces the core management operations around whole databases:
creating them, dropping them, and thinking about backup and restore from a con-
ceptual point of view. The exact syntax and tooling differ across database systems
(MySQL, PostgreSQL, SQL Server, Oracle, etc.), but the underlying ideas are quite
similar.

7.1 SQL Create DB

The CREATE DATABASE statement (or its equivalent) initializes a new, empty database in
a server instance. At this point the database has no user tables, indexes, or data; it only
has a name and some configuration settings such as default character set, collation, and
storage options.

Example: Creating a minimal database

A minimal form in many SQL dialects is:

CREATE DATABASE LibraryDB;

After this statement succeeds, you can switch into the new database and begin
creating tables and other objects. For example, in some systems:

USE LibraryDB;

and then:

CREATE TABLE Books (

BookID INT PRIMARY KEY,
Title VARCHAR(200),
Author VARCHAR(200),
PublishedAt DATE
);

Vo Hoang Nhat Khang 155

7 Database Management

Configuration options
Many systems allow additional parameters when creating a database, such as:

• Character set and collation (e.g., UTF-8, case sensitivity rules),

• File locations or tablespaces,

• Owner or default authorization,

• Template database from which to copy initial settings.

Example: Creating a database with options

A (simplified) example with options might look like:

CREATE DATABASE AnalyticsDB

WITH OWNER = analyst_role;

The exact syntax is vendor-specific, but the intent is the same: define the logical
database and its default properties.

Design and naming considerations

When creating databases, it is common to distinguish environments:

• MyApp_dev, MyApp_test, MyApp_prod for development, testing, and production;

• separate databases for analytics vs. transactional workloads;

• databases grouped by application or business domain.

Although the SQL statement itself is simple, the surrounding decisions about nam-
ing, access control, and resource isolation are architectural.

7.2 SQL Drop DB

The DROP DATABASE statement removes an entire database and all objects within it:
tables, views, indexes, stored procedures, and data. This operation is typically irre-
versible unless a recent backup exists.
The basic form is:

DROP DATABASE LibraryDB;

After executing this command, the database LibraryDB no longer exists on the
server. Any attempts to connect to it or query from it will fail.

Vo Hoang Nhat Khang 156

 7.2 SQL Drop DB

Safety mechanisms
Because dropping a database is destructive, many systems offer safety features:

• IF EXISTS to avoid errors when the database is already gone:

DROP DATABASE IF EXISTS LibraryDB;

• permissions or roles that restrict who is allowed to drop databases;

• requirements that no active connections or open transactions remain in the database

to be dropped.

From an operational perspective, it is standard practice to:

• ensure that the database is not in active use,

• take a final backup (if there is any chance the data may be needed),

• double-check the name of the database before executing DROP DATABASE.

Alternatives to dropping
Sometimes, instead of dropping a database completely, it may be preferable to:

• archive the database (back it up and move the backup to long-term storage),

• rename the database (if supported) to mark it as deprecated,

• revoke access so that users can no longer connect, even though the data still exists.

These approaches can reduce the risk of accidental data loss while still removing
the database from everyday use.
Note: Additional reminds
In practice, what counts as a “database” differs slightly between systems:

• In systems like MySQL or PostgreSQL, DROP DATABASE removes an entire

database/catalog (a collection of schemas, tables, and data).

• In systems like SQL Server or Oracle, similar effects may be achieved with
commands such as DROP DATABASE or by dropping individual schemas. Con-
ceptually, however, the risk is the same: large amounts of data disappear at
once.

Additionally, some engines place transaction-related restrictions on DROP

DATABASE:

• The command may be disallowed inside an explicit transaction (it must auto-
commit).

• It may require exclusive access: all existing connections to the target database
must be closed before the drop can proceed.

Vo Hoang Nhat Khang 157

7 Database Management

Because of this, many teams adopt operational policies such as:

• never running DROP DATABASE from ad hoc consoles connected to production;

• restricting the privilege to a small set of administrator roles;

• requiring change-management review or automated runbooks for any de-

structive operation.

7.3 SQL Backup DB

While CREATE DATABASE and DROP DATABASE are defined as SQL statements, backup
operations are more heterogeneous. The SQL standard says little about backups; real-
world systems provide a mix of SQL statements and external tools.
Conceptually, a database backup is a consistent snapshot of the database at a point
in time that can later be used to restore the database to that state. Backups are funda-
mental for:

• recovering from hardware or software failures,

• protecting against accidental or malicious data deletion,

• supporting long-term archival and audit requirements.

Logical vs. physical backups

Two broad categories are common:

Logical backups Export the database contents as SQL statements or other logical for-
mats. For example, a dump file might contain a sequence of CREATE TABLE and
INSERT statements that recreate the schema and data.

Physical backups Copy the underlying data files, transaction logs, or storage pages
used by the database engine. These backups are usually faster and more space-
efficient, but often more tightly coupled to the specific database system and ver-
sion.

Logical backups are often created with tools or commands that generate SQL, such
as:
Example: Conceptual backup command
-- Conceptual example (non-standard SQL)
BACKUP DATABASE MyAppDB TO 'backup_file';

or external utilities that are run from the command line rather than inside an SQL
query.

Vo Hoang Nhat Khang 158

 7.4 Restoring Database

Planning backup strategy

A robust backup strategy involves decisions about:

• Frequency: full backups daily or weekly, plus incremental backups.

• Retention: how long backups are stored (days, months, years).

• Location: local storage vs. remote storage vs. cloud.

• Testing: regularly restoring backups to verify that they are usable.

The details of actual backup commands are highly system-specific, so in this book
we treat them conceptually rather than prescribing exact syntax.

7.4 Restoring Database

Restoring a database is the inverse of backing it up: taking a backup (logical or physi-
cal) and using it to reconstruct a working database. Restore operations are at the heart
of disaster recovery plans.
At a high level, restoring a database involves:

1. Identifying the appropriate backup (which point in time or which version).

2. Creating or selecting a target database instance to restore into.

3. Applying the backup data (and optionally, additional transaction logs) to bring
the database to the desired state.

4. Verifying integrity and consistency after the restore.

Restoring from logical backups

When a backup is a logical dump (e.g., a file of CREATE and INSERT statements), restor-
ing often means:

• creating an empty database (e.g., CREATE DATABASE MyAppDB_restored;),

• connecting to that database,

• executing the dump file so that it recreates tables and inserts data.

Conceptually:
Example: Restoring from a logical dump
CREATE DATABASE MyAppDB_restored;
-- Then run the dump script inside MyAppDB_restored

This approach is portable and transparent but may be slower for large databases.

Vo Hoang Nhat Khang 159

7 Database Management

Restoring from physical backups

For physical backups, the restore process is usually tightly coupled to the database
engine and may involve:

• stopping the database or taking it offline,

• copying backed-up data files and log files into the correct locations,

• instructing the engine to recover or replay logs up to a specified point in time.

While powerful and efficient, this type of restore is generally performed by database
administrators using system-specific commands, not by everyday SQL queries.

Point-in-time recovery
Many enterprise systems support point-in-time recovery: restoring the database not
only to the time of the last full backup, but to an intermediate time by replaying trans-
action logs. Conceptually:

1. Restore the most recent full backup.

2. Apply incremental backups, if any.

3. Replay transaction logs up to the chosen time.

This allows recovery from errors such as an accidental table drop that occurred at
a known time; the database can be restored to just before the mistake.

Testing and documentation

From a reliability perspective, a backup that has never been tested is a potential liability.
Good practice is to:

• maintain written procedures for backup and restore,

• periodically perform test restores into non-production environments,

• verify that critical applications can run correctly against the restored database.

Vo Hoang Nhat Khang 160

Chapter 8 Tables, Schemas, and Data Types

8.1 SQL Create Table

A relational database is built from tables: named collections of rows, each row shar-
ing the same set of columns. When we design a schema, we are essentially deciding
which tables exist, which columns they contain, and how those columns are typed and
constrained.
The CREATE TABLE statement is the primary tool for defining this structure. It spec-
ifies:

• the table name,

• the column names and data types,

• optional constraints such as primary keys, uniqueness, and defaults.

Once a table is created, queries such as SELECT, INSERT, UPDATE, and DELETE operate
over the rows stored in that structure.

8.1.1 Defining Columns and Data Types

The basic form of CREATE TABLE is:
Example: Basic CREATE TABLE pattern
CREATE TABLE TableName (
ColumnName1 DataType1 [column_constraints],
ColumnName2 DataType2 [column_constraints],
...
[table_constraints]
);

Example: Simple Customers table

CREATE TABLE Customers (
CustomerID INT NOT NULL,
CustomerName VARCHAR(200) NOT NULL,
Country VARCHAR(100),
CreatedAt DATE NOT NULL,
PRIMARY KEY (CustomerID)
);

Here:

Vo Hoang Nhat Khang 161

8 Tables, Schemas, and Data Types

• CustomerID is an integer column and is part of the PRIMARY KEY. It uniquely iden-
tifies each row and cannot be NULL.
• CustomerName is a variable-length character column with a maximum length of
200 characters and must be present for every row.
• Country is optional (it may be NULL).
• CreatedAt stores the date when the customer record was created.

The PRIMARY KEY clause at the bottom is a table-level constraint; it could also be writ-
ten as a column-level constraint, depending on style and dialect.

Column-level vs. table-level constraints. Constraints can often be defined in two

places:

• Column-level (inline): directly after the column definition.

Example: Column-level primary key
CustomerID INT PRIMARY KEY

• Table-level: in a separate clause after all columns.

Example: Table-level primary key
PRIMARY KEY (CustomerID)

Table-level constraints are required when:

• the key spans multiple columns (composite keys),

• you want to name the constraint explicitly (e.g., for clarity or future modifica-
tion).

Composite keys. For tables where a single column is not sufficient to identify a row,
a composite primary key can be used:
Example: Composite primary key on OrderDetails
CREATE TABLE OrderDetails (
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (OrderID, ProductID)
);

In this design, each pair (OrderID, ProductID) must be unique. No single column
alone is the primary identifier.

Default values. Columns can have default values that are used when an INSERT omits
that column:

Vo Hoang Nhat Khang 162

 8.1 SQL Create Table

Example: Defaults for flags and timestamps

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(200) NOT NULL,
IsActive BIT NOT NULL DEFAULT 1,
CreatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Here, IsActive defaults to 1 (true) and CreatedAt defaults to the current times-
tamp when a row is inserted.

8.1.2 Choosing Appropriate Types

Choosing data types is not merely a technical detail; it encodes assumptions about the
domain and has consequences for correctness, performance, storage usage, and query
expressiveness. Good type choices:

• prevent invalid data from being stored,

• make queries simpler and clearer,

• help the optimizer choose efficient execution plans.

Although the exact set of types varies by system, there are common families.

Numeric types. Numeric data is typically stored using integer and fixed/variable-
precision types:

• Integer types (INT, BIGINT, etc.) for counts, identifiers, and discrete values.

• Exact decimals (DECIMAL(p, s), NUMERIC) for financial amounts or quantities

where rounding must be controlled precisely.

• Floating point (FLOAT, REAL) for scientific or approximate calculations where

small rounding errors are acceptable.

A typical guideline:

• Use integer types for IDs and counters.

• Use DECIMAL for money and other fixed-precision quantities.

• Use floating point only when you explicitly need approximate values.

Character and text types. Text data is stored in character types:

• CHAR(n) for fixed-length strings (less common in modern schemas),

• VARCHAR(n) for variable-length strings with an upper bound,

• text or large object types (e.g., TEXT, CLOB) for long documents or unbounded
text.

Vo Hoang Nhat Khang 163

8 Tables, Schemas, and Data Types

Choosing an appropriate maximum length (VARCHAR(50) vs. VARCHAR(500)) is partly

a domain decision: how long can a customer name realistically be? Too small, and valid
data will not fit; too large, and you may waste storage or encourage poor input valida-
tion upstream.
Whenever possible, avoid storing inherently structured data (such as dates, num-
bers, or JSON-like content) as plain text; use more specific types instead.

Date and time types. Modern systems provide dedicated types for temporal data:

• DATE for calendar dates,

• TIME for time of day,

• TIMESTAMP (or DATETIME) for date + time,

• time zoneaware variants where supported.

Using these types rather than storing dates as strings allows the database to:

• validate input (e.g., disallow 31 February),

• support date arithmetic (differences, truncation to month, etc.),

• index and order dates efficiently.

Boolean and enumerated types. Many databases support a BOOLEAN or BIT type for
true/false values, and some support enumerated types for restricted sets of values (e.g.,
’Pending’, ’Shipped’, ’Cancelled’).
Where enumerated types are not available or desired, a common pattern is to use:

• small integers with lookup tables,

• constrained VARCHAR columns with a CHECK constraint.

Example: Constrained status with CHECK
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Status VARCHAR(20) NOT NULL,
CHECK (Status IN ('Pending', 'Shipped', 'Cancelled'))
);

This ensures that invalid status values are rejected.

Keys, foreign keys, and types. When defining primary and foreign keys, it is essen-
tial that the corresponding columns use compatible types. For example:
Example: Matching primary and foreign key types
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
...
);

Vo Hoang Nhat Khang 164

 8.2 SQL Data Types

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

If [Link] is an INT, then [Link] should also be an INT,

not a string or a differently sized integer. A mismatch in types can lead to subtle bugs,
poor performance, or even failures to create the foreign key.
Note: Practical guidelines for choosing types.
Some pragmatic rules that work well in many designs:

• Prefer the most specific type that accurately represents the domain (date
types for dates, numeric types for counts, etc.).

• Avoid using large text types (TEXT, CLOB) for data that could be constrained
more narrowly.

• Use consistent types for the same concept across tables (e.g., all CustomerID
columns share the same type).

• Be explicit with precision and scale for decimal values used in finance or ac-
counting.

• Consider indexing requirements when choosing types: shorter keys and

fewer columns in indexes can improve performance.

8.2 SQL Data Types

Data types are the vocabulary with which a relational schema describes the shape of
data. They determine:
• which values can be stored in a column,
• how those values are compared and sorted,
• how much storage they occupy,
• which operations (arithmetic, string functions, date arithmetic) are allowed.
Although the exact names and details differ between systems (MySQL, PostgreSQL,
SQL Server, etc.), most relational databases support similar families of types: numeric,
character, date/time, Boolean, and various other types (JSON, binary, etc.). This sec-
tion surveys these families at a conceptual level.

8.2.1 Numeric Types

Numeric types represent quantities: counts, identifiers, amounts, and measured val-
ues. They can be divided into two broad groups: exact and approximate.

Vo Hoang Nhat Khang 165

8 Tables, Schemas, and Data Types

Integer types
Integer types store whole numbers without fractional parts. Common variants include:

• SMALLINT, INT (or INTEGER), BIGINT,

• optionally unsigned variants in some systems (e.g. INT UNSIGNED).

Typical uses:

• Primary keys and surrogate IDs (CustomerID, OrderID),

• counters (number of logins, number of items),

• small enumerated codes (if not represented as separate types).

Example: Integer columns

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Age SMALLINT,
LoyaltyPoints INT NOT NULL DEFAULT 0
);

Choosing between INT and BIGINT is often a question of scale: how many rows or
distinct values do you expect over the lifetime of the system?

Exact decimal types

Exact decimal types (often DECIMAL(p, s) or NUMERIC(p, s)) store numbers with a
fixed precision p (total digits) and scale s (digits after the decimal point). They are
essential for financial data or any domain where rounding must be controlled.
Example: Exact decimal types for invoices
CREATE TABLE Invoices (
InvoiceID INT PRIMARY KEY,
Amount DECIMAL(12, 2) NOT NULL, -- 10 digits before decimal
TaxRate DECIMAL(5, 4), -- e.g., 0.0750 for 7.5%
TotalAmount AS (Amount * (1 + TaxRate))
);

Using integer or floating-point types for money is usually a bad idea: integers force
manual scaling (e.g. storing cents), while floating point introduces rounding artifacts.

Approximate types (floating point)

Floating-point types (REAL, FLOAT, DOUBLE) represent numbers approximately, using
binary fractions. They can represent very large or small values but cannot represent
many decimal fractions exactly.
They are appropriate for:

• scientific measurements,

Vo Hoang Nhat Khang 166

 8.2 SQL Data Types

• statistical calculations,

• domains where small rounding errors are acceptable.

They are not suitable for precise accounting or exact comparisons (e.g. WHERE value
= 0.1) without careful tolerances.

8.2.2 Character and Text Types

Character types represent strings: names, addresses, codes, descriptions, and other
textual data. Most systems distinguish between fixed-length and variable-length strings.

Fixed-length vs. variable-length

• CHAR(n) stores strings with a fixed length n. Shorter values are padded (typically
with spaces). Common for codes of known length (e.g. country codes, status
codes).

• VARCHAR(n) stores strings up to length n, using only as much storage as needed.

Suitable for names, emails, etc.

Example: Character columns on Products

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
SKU CHAR(10) NOT NULL, -- fixed-length stock-keeping
,→ unit
ProductName VARCHAR(200) NOT NULL,
Category VARCHAR(100),
Description VARCHAR(1000)
);

In modern designs, VARCHAR is more common than CHAR, except for very short, fixed-
format codes.

Large text and Unicode

For long unstructured text (comments, documents, logs), many systems offer large
object types such as TEXT, CLOB, or similar. These can store thousands or millions of
characters.
Additionally, most contemporary databases support Unicode encodings (e.g. UTF-
8, UTF-16) so that a single VARCHAR can store text in multiple languages with accents
and non-Latin scripts. Choosing an appropriate character set and collation at the database
or column level ensures correct sorting and comparison behavior.

Design considerations
When choosing character types:

• Pick realistic length limits (VARCHAR(50) vs. VARCHAR(500)).

Vo Hoang Nhat Khang 167

8 Tables, Schemas, and Data Types

• Avoid using large text types for fields that could be constrained more tightly (e.g.
email addresses).

• Use specific types (date, numeric, Boolean) instead of encoding structured values
as free-form strings.

8.2.3 Date and Time Types

Temporal types represent points in time or durations. They are crucial for tracking
events, histories, and schedules.
Common categories:

• DATE: calendar date only (year, month, day).

• TIME: time of day (hours, minutes, seconds), sometimes with fractional seconds.

• TIMESTAMP / DATETIME: date and time combined.

• Time zoneaware variants in some systems (TIMESTAMPTZ, etc.).

Examples

Example: Date and time columns on Orders

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
ShippedAt TIMESTAMP NULL
);

Using date/time types rather than strings enables:

• validation (disallowing invalid dates),

• sorting and range queries (BETWEEN, >, <),

• date arithmetic (differences, adding intervals),

• efficient indexing on temporal columns.

Time zones and consistency

Time zones are a common source of subtle bugs. Typical strategies include:

• Storing timestamps in UTC in the database and converting to local time in the
application layer.

• Using explicit time zoneaware types where supported and necessary.

Whatever strategy is chosen, consistency matters more than the specific convention;
mixed or undocumented approaches are difficult to reason about.

Vo Hoang Nhat Khang 168

 8.2 SQL Data Types

8.2.4 Boolean and Other Types

Beyond numbers, text, and time, most relational systems support Boolean values and
a variety of specialized data types.

Boolean types

A Boolean type (BOOLEAN, BOOL, or a BIT variant) represents logical truth values: TRUE
or FALSE, plus NULL.
Example: Boolean flags on Users
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(255) NOT NULL,
IsActive BOOLEAN NOT NULL DEFAULT TRUE,
IsAdmin BOOLEAN NOT NULL DEFAULT FALSE
);

Where a native Boolean type is not available, a common pattern is to emulate it with
TINYINT (0/1) plus a CHECK constraint, or with CHAR(1) values such as ’Y’/’N’.

Binary, JSON, and other specialized types

Modern databases often include additional types, such as:

• Binary types: BINARY, VARBINARY, BLOB for storing raw bytes (files, hashes, en-
crypted data).

• JSON / XML: structured document types with built-in query functions (JSON
fields, path expressions).

• UUID / GUID: standardized 128-bit identifiers for globally unique keys.

• Geospatial types: points, lines, polygons, with spatial indexes and functions.

• other domain-specific types depending on the system.

Example: Table with a JSON payload

CREATE TABLE Events (
EventID INT PRIMARY KEY,
EventType VARCHAR(50) NOT NULL,
OccurredAt TIMESTAMP NOT NULL,
Payload JSON NOT NULL
);

These types can greatly simplify certain designs, but they also blur the line between
relational and semi-structured data. As always, it is worth asking whether a dedicated
table structure might better capture the datas relationships and constraints.

Vo Hoang Nhat Khang 169

8 Tables, Schemas, and Data Types

Choosing types with constraints

Data types work best in combination with constraints:

• NOT NULL to enforce presence,

• CHECK to restrict allowed values or ranges,
• UNIQUE and PRIMARY KEY to enforce identity,
• FOREIGN KEY to maintain relationships between tables.

Example: Type + constraint for non-negative balances

CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
Balance DECIMAL(12, 2) NOT NULL,
CHECK (Balance >= 0)
);

Here, the type DECIMAL(12, 2) ensures numeric precision, while the CHECK con-
straint encodes a business rule: balances cannot be negative.

8.3 SQL Alter Table

Once a table has been created and populated with data, it rarely remains unchanged.
New requirements emerge, existing fields prove too restrictive, errors in the original
design are discovered, or performance considerations suggest new indexes and con-
straints. The ALTER TABLE statement provides a controlled way to evolve table defini-
tions over time without recreating them from scratch.
At a high level, ALTER TABLE is used to:

• add, modify, or drop columns,

• add or remove constraints (primary keys, foreign keys, checks),
• rename columns or tables (in some dialects),
• adjust default values or nullability.

Because these operations affect existing data, they must be used with care. Schema
evolution is as much about protecting data and uptime as it is about changing defini-
tions.

8.3.1 Adding, Modifying, and Dropping Columns

The most common ALTER TABLE operations involve changing the set of columns in a
table.

Adding columns
To add a new column to an existing table:

Vo Hoang Nhat Khang 170

 8.3 SQL Alter Table

Example: Adding a new column

ALTER TABLE Customers
ADD COLUMN Email VARCHAR(255);

Some dialects use ADD without the word COLUMN; others require it. The new column
is created with the specified type and constraints.
If a NOT NULL constraint is specified, the database must know what value to assign
to existing rows. Often this is handled with a default:
Example: Adding a NOT NULL column with a default
ALTER TABLE Customers
ADD COLUMN IsActive BIT NOT NULL DEFAULT 1;

In this example:

• New rows that omit IsActive receive the default value 1.

• Existing rows are backfilled with 1 at the time of the schema change.

Without a default, adding a NOT NULL column may fail or require a multi-step mi-
gration (see below).

Modifying columns
Modifying a column typically involves changing its data type, length, nullability, or
default. Syntax varies by system; common forms include ALTER COLUMN, MODIFY, or
CHANGE.

Example: widening a string column. Suppose CustomerName was originally defined

as VARCHAR(100) and you decide to allow longer names:
Example: Widening a string column
ALTER TABLE Customers
ALTER COLUMN CustomerName VARCHAR(200);

(Exact syntax differs by dialect; some use MODIFY instead of ALTER COLUMN)
Widening a column (allowing more characters) is usually safe and efficient, because
existing values already fit within the new limit. Narrowing a column (VARCHAR(200)
to VARCHAR(100)) can be dangerous: values that are too long must either be truncated
or rejected, and the database may refuse the change if it would lose data.

Example: changing nullability. To make a previously nullable column required:

Example: Changing a column to NOT NULL
ALTER TABLE Customers
ALTER COLUMN Country VARCHAR(100) NOT NULL;

Before doing this, you must ensure that no existing rows have Country set to NULL.
Otherwise, the statement will fail or require explicit data cleanup:

Vo Hoang Nhat Khang 171

8 Tables, Schemas, and Data Types

Example: Backfilling NULL values before tightening nullability

UPDATE Customers
SET Country = 'Unknown'
WHERE Country IS NULL;

and then:
Example: Applying NOT NULL after cleanup
ALTER TABLE Customers
ALTER COLUMN Country VARCHAR(100) NOT NULL;

Changing default values. Defaults can be added, modified, or dropped so that future
inserts use a new baseline:
Example: Changing and dropping a default value
ALTER TABLE Orders
ALTER COLUMN Status SET DEFAULT 'Pending';

ALTER TABLE Orders

ALTER COLUMN Status DROP DEFAULT;

Again, exact syntax is dialect-dependent, but the concept is consistent.

Dropping columns
To remove a column from a table:
Example: Dropping a column
ALTER TABLE Customers
DROP COLUMN MiddleName;

This operation eliminates the column definition and its data for all rows. It is irre-
versible unless there is a backup. Dropping a column may be blocked if:

• the column participates in a primary or foreign key,

• there are indexes or constraints that depend on the column.

In such cases, constraints and indexes must usually be removed or adjusted before
the column can be dropped:
Example: Dropping a foreign key and its column
ALTER TABLE Orders
DROP CONSTRAINT FK_Orders_Customers; -- example name

ALTER TABLE Orders

DROP COLUMN CustomerID;

Because dropping a column discards data, it is good practice to:

Vo Hoang Nhat Khang 172

 8.3 SQL Alter Table

• deprecate the column first (stop using it in the application),

• verify that it is no longer referenced by queries or reports,

• ensure a backup or export exists in case the data is needed later.

Altering constraints and indexes (briefly)

Although this section focuses on columns, ALTER TABLE is also used to:

• add or drop primary keys:

Example: Adding and dropping a primary key constraint

ALTER TABLE Customers
ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);

ALTER TABLE Customers

DROP CONSTRAINT PK_Customers;

• add or drop foreign keys:

Example: Adding a foreign key constraint

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

• add or drop CHECK constraints and unique constraints.

These operations are central to maintaining referential integrity as schemas evolve.

8.3.2 Evolving Schemas Safely

Changing a tables structure in a live system is not just a matter of syntax. It has impli-
cations for:

• existing data (will any rows become invalid?),

• application code (do queries still compile and behave as expected?),

• performance and availability (does the change lock the table or trigger long-
running migrations?).

Safe schema evolution combines technical steps with process discipline.

Vo Hoang Nhat Khang 173

8 Tables, Schemas, and Data Types

Backward-compatible changes
Some changes are generally considered backward-compatible, meaning that existing ap-
plication code can continue to function without immediate changes:

• adding a new nullable column,

• adding a new table,

• widening a VARCHAR or increasing numeric precision,

• adding optional indexes or constraints that do not invalidate existing data.

Even for these changes, it is wise to:

• test in a staging environment,

• monitor performance and error logs after deployment.

Potentially breaking changes

Other changes are breaking and require coordinated updates:

• dropping columns or tables,

• renaming columns or tables,

• narrowing data types or tightening length limits,

• adding NOT NULL constraints to columns that were previously nullable,

• adding or tightening CHECK constraints.

Handling these safely often involves multi-step migrations.

Multi-step migrations for NOT NULL columns. A typical pattern for introducing a
new required column is:

1. Add the column as nullable, with an appropriate default for new rows:
Example: Step 1: add a nullable column with default
ALTER TABLE Orders
ADD COLUMN PaymentStatus VARCHAR(20) NULL DEFAULT 'Pending';

2. Backfill existing rows with meaningful values:

Example: Step 2a: backfill paid orders
UPDATE Orders
SET PaymentStatus = 'Paid'
WHERE PaidAt IS NOT NULL
AND PaymentStatus IS NULL;

Vo Hoang Nhat Khang 174

 8.3 SQL Alter Table

Example: Step 2b: backfill pending orders

UPDATE Orders
SET PaymentStatus = 'Pending'
WHERE PaidAt IS NULL
AND PaymentStatus IS NULL;

3. Change the column to NOT NULL once data is consistent:

Example: Step 3: enforce NOT NULL
ALTER TABLE Orders
ALTER COLUMN PaymentStatus VARCHAR(20) NOT NULL;

By separating the steps, you avoid failures and can monitor the impact between
phases.

Renaming and deprecating columns. When a column needs to be renamed or its

meaning changed, a gradual strategy reduces risk:
1. Add a new column with the desired name.
2. Migrate data from the old column to the new column.
3. Update application code to read from the new column (and optionally write to
both during a transition period).
4. Once all code uses the new column, drop the old column.
Some databases provide ALTER TABLE ...RENAME COLUMN syntax, but application
code that references the old name still needs to be updated.

Online vs. offline changes

On large tables, certain ALTER TABLE operations can be expensive:
• changing data types,
• adding NOT NULL constraints without defaults,
• rebuilding clustered indexes,
• some forms of adding or dropping columns.
Depending on the system, these may:
• lock the table for the duration of the operation,
• block reads or writes,
• require significant disk I/O.
Advanced database systems and cloud platforms sometimes provide online schema
change mechanisms that minimize downtime (e.g., by creating a new copy of the table
in the background and then switching over). Even when such features exist, careful
planning, monitoring, and testing are necessary.

Vo Hoang Nhat Khang 175

8 Tables, Schemas, and Data Types

Version control and migrations

In modern practice, schema changes are often managed via migration tools and version-
controlled scripts. Instead of manually running ad hoc ALTER TABLE statements, teams:

• write migration scripts that describe each change,

• check these scripts into version control alongside application code,
• apply migrations consistently across development, staging, and production.

8.4 SQL Drop Table

While CREATE TABLE introduces new structures into a schema, DROP TABLE removes
them entirely. Dropping a table deletes both its definition and all of its data. This is
a powerful and potentially destructive operation: once a table is dropped, the data it
contained can only be recovered from backups or archived copies.
In practice, dropping tables is part of:

• cleaning up obsolete or experimental structures,

• decommissioning features or modules,
• reorganizing schemas during major redesigns.

Because these actions can affect other tables through foreign keys and application
code, it is important to understand both the mechanics of DROP TABLE and strategies
for safe archiving.

8.4.1 Dropping Tables and Dependencies

The basic syntax is straightforward:
Example: Basic DROP TABLE
DROP TABLE TableName;

After this statement runs successfully:

• the table TableName no longer exists,

• all rows previously stored in it are removed,
• indexes, constraints, and triggers associated with the table are also removed.

Many systems support a defensive variant that avoids errors if the table does not
exist:
Example: DROP TABLE IF EXISTS
DROP TABLE IF EXISTS TableName;

This is useful in deployment scripts or testing environments where the presence of

a table is not guaranteed.

Vo Hoang Nhat Khang 176

 8.4 SQL Drop Table

Foreign key dependencies. Dropping a table that participates in foreign key relation-
ships requires extra care. Two main situations arise:

1. The table is referenced by other tables. For example, Orders might have a for-
eign key to Customers. Dropping Customers while the foreign key remains would
break referential integrity.

2. The table references other tables. For example, OrderDetails might reference
Orders and Products. Dropping OrderDetails may be blocked until constraints
are removed.

Most databases will refuse to drop a table that is referenced by a foreign key unless
you explicitly instruct them to remove dependent objects. In some systems, this is done
with a CASCADE option:
Example: DROP TABLE with CASCADE
DROP TABLE Customers CASCADE;

Conceptually, CASCADE means:

• drop the table,

• drop any foreign keys, views, or other dependent objects that rely on it.

Because this can have far-reaching effects, it is essential to understand what will
be removed before using CASCADE in production. In many teams, dropping a heavily
referenced table is treated as a high-risk operation that requires explicit review.

Order of operations. A safer approach is often to:

1. Identify all dependencies (foreign keys, views, triggers).

2. Drop or modify those dependencies explicitly.

3. Drop the table itself only after dependencies have been handled.

For example, to drop Customers that is referenced by Orders:

Example: Dropping a referenced table step by step
ALTER TABLE Orders
DROP CONSTRAINT FK_Orders_Customers; -- example name

DROP TABLE Customers;

This makes the sequence of changes more visible and easier to review.

Vo Hoang Nhat Khang 177

8 Tables, Schemas, and Data Types

Application-level dependencies. Beyond database-level constraints, tables are also

referenced by:
• application code (queries, ORMs, stored procedures),
• reports and dashboards,
• integration scripts and ETL jobs.
Dropping a table without updating these references will cause runtime errors. A
common practice is to:
• first mark the table as deprecated (for example, by renaming it with a prefix such
as Old_ or moving it into an archive schema),
• update all code to stop using it,
• only then drop the table once it is clearly unused.

8.4.2 Archiving Before Drop

Because dropping a table discards its data, a natural question arises:
Do we need to keep this data for legal, historical, or analytical reasons?
If the answer might be yes, archiving is usually preferable to immediate deletion.
There are several strategies for archiving a table before dropping it.

Copying the table into an archive

One straightforward approach is to create an archive table (possibly in a separate schema
or database) and copy the data into it before dropping the original.
Example: Archiving with CREATE TABLE AS
-- Create an archive copy of the table
CREATE TABLE Archive.Customers_2025_01_01 AS
SELECT *
FROM Customers;

Example: Archiving with SELECT INTO

SELECT *
INTO Archive_Customers_2025_01_01
FROM Customers;

Once the archive table is created and verified, you can safely drop or truncate the
original Customers table if it is no longer needed in production.
This pattern is often used when:
• schema changes are so large that starting with a fresh table is easier,
• only recent data needs to remain in the primary table, while historical data is
preserved for auditing or analytics.

Vo Hoang Nhat Khang 178

Chapter 9 Data Constraints and Relationships

Relational databases are not just about storing data; they are about preserving mean-
ingful data. Constraints give structure to that meaning. They define which values are
allowed, which combinations of values are valid, and how rows in different tables re-
late to each other.
In this chapter we look at the most important constraint types:

• NOT NULL – preventing missing values where they are not allowed.

• UNIQUE – ensuring that a set of columns does not contain duplicates.

• PRIMARY KEY – defining the identity of a row.

• FOREIGN KEY – expressing relationships between tables.

• CHECK – enforcing arbitrary logical conditions.

• DEFAULT – providing standard values when none are supplied.

Together, these constraints move a schema from a set of tables to a coherent rela-
tional model.

9.1 SQL Not Null

A NOT NULL constraint forbids a column from taking the value NULL. It is the simplest
form of constraint: a binary decision about whether a value must be present.

Basic usage
Example: Basic NOT NULL usage
CREATE TABLE Customers (
CustomerID INT NOT NULL,
CustomerName VARCHAR(200) NOT NULL,
Email VARCHAR(255) NULL,
Country VARCHAR(100) NOT NULL
);

Here:

• CustomerID, CustomerName, and Country must always have values.

• Email may be NULL (e.g., for customers who have not provided an email address).

Vo Hoang Nhat Khang 179

9 Data Constraints and Relationships

Every INSERT or UPDATE that attempts to set a NOT NULL column to NULL will be
rejected.

Design questions
Choosing which columns can be NULL is a design decision:

• If a value is required by the business (e.g., an order must have a customer), the
column should be NOT NULL.

• If the absence of a value is meaningful (unknown, not applicable, not yet as-
signed), allowing NULL may be appropriate.

A schema that overuses NULL may hide missing or inconsistent data; a schema that
forbids NULL everywhere may force the use of arbitrary dummy values (’Unknown’, 0,
’N/A’) that are even harder to interpret.

Adding NOT NULL later

Changing an existing nullable column to NOT NULL typically requires:

1. Ensuring there are no existing NULL values.

2. Deciding what to do with future missing values (default, error, etc.).

Example: Adding a NOT NULL constraint later

-- Clean up existing data
UPDATE Customers
SET Country = 'Unknown'
WHERE Country IS NULL;

-- Enforce NOT NULL constraint

ALTER TABLE Customers
ALTER COLUMN Country VARCHAR(100) NOT NULL;

This is a typical schema evolution pattern: clean the data first, then tighten the
constraint.

9.2 SQL Unique

A UNIQUE constraint ensures that no two rows share the same value (or combination of
values) in a set of columns. It enforces no duplicates at the database level.

Vo Hoang Nhat Khang 180

 9.2 SQL Unique

Column-level and table-level UNIQUE

Example: Single-column UNIQUE constraint
CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName VARCHAR(100) NOT NULL UNIQUE,
Email VARCHAR(255) NOT NULL,
CreatedAt TIMESTAMP NOT NULL
);

Here, UserName must be unique across all users.

For multi-column uniqueness (composite keys), UNIQUE is usually defined at the
table level:
Example: Composite UNIQUE constraint
CREATE TABLE Enrollments (
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrolledAt DATE NOT NULL,
UNIQUE (StudentID, CourseID)
);

This means:

The same student cannot be enrolled in the same course more than once.

UNIQUE and NULL

Most SQL dialects allow multiple rows with NULL in a UNIQUE column, because NULL is
treated as unknown rather than as a value equal to another NULL. For example, in many
systems:

• Email = ’a@[Link]’ can appear only once,

• Email = NULL can appear in multiple rows.

If you need a column that is both UNIQUE and NOT NULL, you must specify both
constraints:
Example: UNIQUE together with NOT NULL
Email VARCHAR(255) NOT NULL UNIQUE

UNIQUE vs. PRIMARY KEY

Both PRIMARY KEY and UNIQUE enforce uniqueness, but:

• A table has exactly one PRIMARY KEY, but may have multiple UNIQUE constraints.

• The primary key cannot contain NULL in most databases; UNIQUE columns often
can.

Vo Hoang Nhat Khang 181

9 Data Constraints and Relationships

A common pattern is:

• Use a surrogate integer primary key (UserID).

• Use UNIQUE constraints on natural keys (e.g., UserName, Email).

9.3 SQL Primary Key

A PRIMARY KEY constraint identifies the column(s) that uniquely identify each row in
a table. It encodes the relational idea of a rows identity.

Single-column primary keys

The simplest and most common case is a single-column primary key:

CREATE TABLE Customers (

CustomerID INT NOT NULL,
CustomerName VARCHAR(200) NOT NULL,
Country VARCHAR(100),
PRIMARY KEY (CustomerID)
);

Properties:

• The CustomerID values must be unique.

• CustomerID cannot be NULL.

• Many systems automatically create an index on the primary key.

Composite primary keys

Sometimes, no single column uniquely identifies rows; instead, a combination of columns
does. This is a composite primary key:

CREATE TABLE OrderDetails (

OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
PRIMARY KEY (OrderID, ProductID)
);

Here, the pair (OrderID, ProductID) is unique. This design reflects a natural key:
each product appears at most once per order.

Vo Hoang Nhat Khang 182

 9.4 SQL Foreign Key

Surrogate vs. natural keys

A recurring design question is whether to use:

• Natural keys identifiers derived from the domain (e.g. national ID, email, SKU).
• Surrogate keys artificial identifiers (e.g. auto-increment integers, UUIDs).

Common practice:

• Use surrogate primary keys for stability and simplicity.

• Enforce uniqueness of natural keys with separate UNIQUE constraints.

This allows natural keys to change (e.g. a user changes email) without rewriting
foreign keys.
Note: Some important things to keep in mind when working with PRIMARY KEY
• A table can have only one PRIMARY KEY constraint, but it may contain multiple
UNIQUE constraints.

• Columns in the primary key are implicitly NOT NULL in most SQL dialects;
attempting to insert NULL into a primary key column is rejected.

• Many systems automatically create an index on the primary key. This is use-
ful for joins and lookups, but also means that very wide composite keys can
have performance and storage costs.

• When using surrogate keys (e.g. auto-increment integers or UUIDs), it is

common to add UNIQUE constraints on business identifiers (such as email,
SKU, or invoice number) to prevent duplicates at the data level.

• Changing a primary key on a table that is referenced by foreign keys can be

expensive and disruptive: all referencing foreign keys must be updated. It is
often worth spending extra time choosing a stable primary key early in the
design.

9.4 SQL Foreign Key

A FOREIGN KEY constraint enforces relationships between tables. It declares that one
tables column(s) refer to another tables primary (or unique) key. This maintains ref-
erential integrity: you cannot create references to non-existent rows.

Basic foreign key example

Example: Basic foreign key
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(200) NOT NULL
);

Vo Hoang Nhat Khang 183

9 Data Constraints and Relationships

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

The foreign key here expresses:

Every [Link] must match some [Link].

The database will reject:

• Inserting an order with a CustomerID that does not exist in Customers.

• Deleting a customer who still has orders (depending on referential actions).

Referential actions: ON DELETE / ON UPDATE

Foreign keys can specify what happens when the referenced key changes:
Example: Foreign key with referential actions
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE;

Common options (names vary slightly by system):

• NO ACTION / RESTRICT: prevent deletion or update if dependent rows exist.

• CASCADE: propagate the deletion or update to child rows.

• SET NULL: set the foreign key column to NULL when the parent is deleted or up-
dated.

• SET DEFAULT: set the foreign key column to its default value.

Example: if ON DELETE CASCADE is set and a customer is deleted, all their orders are
automatically deleted as well. This may or may not be desirable; the choice reflects
business rules.

Composite foreign keys

When the referenced key is composite, the foreign key must match on all columns:

Vo Hoang Nhat Khang 184

 9.5 SQL Check

Example: Composite foreign key

CREATE TABLE Products (
ProductID INT,
SupplierID INT,
ProductName VARCHAR(200),
PRIMARY KEY (ProductID, SupplierID)
);

CREATE TABLE Shipments (

ShipmentID INT PRIMARY KEY,
ProductID INT NOT NULL,
SupplierID INT NOT NULL,
Quantity INT NOT NULL,
FOREIGN KEY (ProductID, SupplierID)
REFERENCES Products(ProductID, SupplierID)
);

Here, Shipments.(ProductID, SupplierID) must always refer to an existing row

in Products.
Note: Practical tips for foreign keys
In most systems, it is good practice to index foreign key columns (e.g.
[Link]) to speed up joins and cascade checks. Be conservative with ON
DELETE CASCADE in production schemas: while convenient during development, it
can cause large, unexpected deletions if a parent row is removed accidentally.

9.5 SQL Check

A CHECK constraint allows the database to enforce an arbitrary Boolean condition on
each row. It is a way to encode business rules directly into the schema.

Column-level checks
Example: Column-level CHECK constraint
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
Balance DECIMAL(12,2) NOT NULL,
CHECK (Balance >= 0)
);

This CHECK ensures that balances cannot be negative. Any INSERT or UPDATE that
would cause Balance < 0 is rejected.

Table-level checks
Checks can also involve multiple columns:

Vo Hoang Nhat Khang 185

9 Data Constraints and Relationships

Example: Table-level CHECK constraints

CREATE TABLE Promotions (
PromotionID INT PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
DiscountPct DECIMAL(5,2) NOT NULL,
CHECK (EndDate >= StartDate),
CHECK (DiscountPct >= 0 AND DiscountPct <= 100)
);

Here:

• The promotion must not end before it starts.

• The discount percentage must be between 0 and 100.

CHECK constraints and NULL

A CHECK constraint is considered satisfied when its condition evaluates to TRUE or UNKNOWN.
This means that if any operand in the expression is NULL, the result may be UNKNOWN,
and the check still passes.
Example: CHECK with NULL vs. NOT NULL
CHECK (Age >= 18)

If Age is NULL, the condition evaluates to UNKNOWN, and the row is allowed. If you
want to forbid NULL, combine CHECK with NOT NULL:
Example: CHECK combined with NOT NULL
Age INT NOT NULL,
CHECK (Age >= 18)

Note: Go simple!
CHECK constraints run on every INSERT and UPDATE, so keep them simple
enough for the optimizer to handle efficiently. Whenever a rule is “always true”
by definition (e.g. discounts never exceed 80%), it is usually worth encoding as a
CHECK rather than relying only on application code, so that bad data cannot slip
in from other tools.

9.6 SQL Default

A DEFAULT constraint specifies a value that is used when no value is provided for a
column in an INSERT. Defaults help:

• simplify insert statements,

• ensure consistent baseline values,

• avoid accidental NULLs for frequently used columns.

Vo Hoang Nhat Khang 186

 9.6 SQL Default

Basic examples
Example: Columns with DEFAULT values
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
Status VARCHAR(20) NOT NULL DEFAULT 'Pending',
CreatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
TotalAmount DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

Now the following INSERT:

Example: INSERT using column defaults
INSERT INTO Orders (OrderID, CustomerID)
VALUES (1001, 42);

implicitly sets:

• Status = ’Pending’,

• CreatedAt = the current timestamp,

• TotalAmount = 0.00.

DEFAULT and NOT NULL

Defaults and NOT NULL often go together. For example:
Example: DEFAULT with NOT NULL
IsActive BOOLEAN NOT NULL DEFAULT TRUE

This ensures that:

• IsActive is never NULL,

• if the application forgets to specify a value, it defaults to TRUE.

Changing and dropping defaults

Defaults can be adjusted as requirements evolve (syntax varies by system):
Example: Changing and dropping DEFAULT
-- Change default
ALTER TABLE Orders
ALTER COLUMN Status SET DEFAULT 'New';

-- Drop default
ALTER TABLE Orders
ALTER COLUMN Status DROP DEFAULT;

Vo Hoang Nhat Khang 187

9 Data Constraints and Relationships

When changing a default, remember that it affects only future inserts; existing rows
retain their current values unless explicitly updated.

Vo Hoang Nhat Khang 188

Chapter 10 Reading Execution Plans in MySQL

SQL is a declarative language: you describe the result you want, and the database figures
out a way to produce it. That “way” is the execution plan. In MySQL, the plan is chosen
by the optimizer and carried out by the executor.
Two queries that look similar on the page can behave very differently at runtime.
The reason is rarely “SQL magic” - it is almost always something concrete:

• the available indexes (or lack of them),

• the estimated number of rows that match each predicate,

• join order and join algorithms,

• whether MySQL must create a temporary table or perform an explicit sort,

• data distribution (skew, duplicates, selectivity).

Note: A common misconception: “top to bottom execution”

It is true that SQL has a logical evaluation order (roughly: FROM → WHERE → GROUP
BY → HAVING → SELECT → ORDER BY → LIMIT). But the physical execution plan is not
“the query text, read from top to bottom”. The optimizer can reorder joins, choose
different access paths, and apply transformations that still preserve correctness.

In MySQL 5.7, the primary tool for plan inspection is EXPLAIN. For official details,
see the MySQL 5.7 Reference Manual section on execution plan information.1

10.1 What an Execution Plan Is

An execution plan is MySQLs chosen strategy for executing a statement. A good plan
answers practical questions like:

• Access path: How is each table read (full scan, index range scan, point lookup)?

• Join order: Which table is read first, and which tables are probed next?

• Index usage: Which index is considered and which is actually chosen?

• Work estimates: How many rows will MySQL examine (estimated)?

• Extra steps: Will it need a temporary table, a sort pass, or other “hidden work”?
1
MySQL 5.7 Reference Manual: Execution Plan Information. [Link]
7/en/[Link]

Vo Hoang Nhat Khang 189

10 Reading Execution Plans in MySQL

When performance debugging becomes real (slow dashboards, timeouts, produc-

tion alerts), plans are your starting map: they tell you where the time is likely going,
and what kind of work MySQL believes it must do.

10.2 A Practical EXPLAIN Workflow

When performance matters, a reliable workflow looks like this:

1. Get the correct result first. Optimize only after correctness.

2. Run EXPLAIN. Treat it like looking under the hood.

3. Circle the expensive parts. Large row estimates, bad access types, Using temporary,
Using filesort.

4. Change one thing at a time. Add an index, rewrite a predicate, or restructure a

join.

5. Re-run EXPLAIN and compare.

Caution: A plan is not a benchmark

EXPLAIN reports what MySQL plans to do, along with estimates (e.g., expected rows
examined). Always validate improvements with real timing on realistic data sizes.
A plan can “look good” and still be slow if the estimates are wrong or the workload
is dominated by other factors (I/O, locks, network).

10.3 Using EXPLAIN in MySQL

In MySQL 5.7, EXPLAIN can be used for several statement types (including SELECT,
UPDATE, DELETE, INSERT, and REPLACE).2
Before we start interpreting plans, we need a shared mental picture of the schema
and the data. In this chapter, assume we have two tables, Customers and Orders.
Customers stores one row per customer. It has a primary key CustomerID and a
name.
Orders stores one row per order. It has a primary key OrderID, a customer reference
CustomerID, an order date, and a total amount.
To make the examples concrete, assume the tables contain the following rows.

CustomerID CustomerName
1 Alice
2 Bob
3 Carol
4 David

2
MySQL 5.7 Reference Manual: Execution Plan Information. [Link]
7/en/[Link]

Vo Hoang Nhat Khang 190

 10.3 Using EXPLAIN in MySQL

OrderID CustomerID OrderDate TotalAmount

101 1 2024-12-15 120.00
102 1 2025-01-05 80.00
103 2 2025-02-01 50.00
104 2 2024-11-20 40.00
105 3 2025-03-10 70.00
106 4 2025-01-01 90.00

We also assume the schema includes indexes that reflect typical production design.
Customers has its primary key. Orders has an index on CustomerID to support joins,
and an index on OrderDate to support date filtering:
Customers: PRIMARY KEY (CustomerID)

Orders: PRIMARY KEY (OrderID)

KEY fk_orders_customers (CustomerID)
KEY idx_orders_orderdate (OrderDate)
With this setup, the optimizer has real options. It can scan Orders and filter dates
row by row, or it can use the OrderDate index to find only the relevant orders first. It
can also decide when to look up matching customers. That is exactly what EXPLAIN
helps us see.

10.3.1 Filter-then-join plan

Before we talk about execution plans, we should see what the query actually [Link]’s
run the query normally:
SELECT [Link], [Link], [Link]
FROM Orders AS o
JOIN Customers AS c
ON [Link] = [Link]
WHERE [Link] >= '2025-01-01'
ORDER BY [Link];
and we get the following output:

OrderID OrderDate CustomerName

102 2025-01-05 Alice
103 2025-02-01 Bob
105 2025-03-10 Carol
106 2025-01-01 David

Now we ask a different question. Not “what rows do I get?”, but “how will MySQL
produce them?”
That is what EXPLAIN is for:
EXPLAIN
SELECT [Link], [Link], [Link]
FROM Orders AS o
JOIN Customers AS c
ON [Link] = [Link]
WHERE [Link] >= '2025-01-01';

Vo Hoang Nhat Khang 191

10 Reading Execution Plans in MySQL

Sample plan output:

table type possible_keys key rows filtered Extra

o ALL fk_orders_customers NULL 6 33.33 Using where
c eq_ref PRIMARY PRIMARY 1 100.00 NULL

First, MySQL chooses a join order. It starts with Orders. The word ALL means a
full scan: it reads all rows in Orders, then checks the date predicate row by row. That
explains Using where. The filter is applied, but only after the scan is already in motion.
Then, for each surviving order row, MySQL looks up the matching customer. This is
the best part of the plan. eq_ref with PRIMARY means a direct primary-key lookup that
returns at most one customer per order. This is exactly what you want for a typical
foreign-key join.
So the plan is not bad because the join is slow. The join is fast. The plan is bad
because the first step is a scan, and scans become painful when tables grow.
Note: Why we did not include ORDER BY in EXPLAIN
We ordered the query output to make the example easier to read. That ordering
is for humans. The plan discussion here is about how MySQL finds the matching
rows in the first place, so we keep the EXPLAIN query focused on the join and the
date filter. Later, when we discuss sorting and filesort, we will include ORDER BY
on purpose.

10.3.2 Read EXPLAIN output

MySQLs traditional EXPLAIN output can look intimidating because it has many columns.
In practice, you will spend most of your attention on a few high-signal ones:

• type: the access method (your first health check).

• key / possible_keys: what could be used vs. what MySQL chose.

• rows: estimated rows examined (a rough cost signal).

• filtered: estimated percentage of rows that survive predicates.

• Extra: “hidden work” flags (Using temporary, Using filesort, etc.).

The type column

A practical (imperfect, but useful) rule is:

“The further you are from ALL, the better”

Common values you will usually see are:

• ALL: full table scan (often expensive for large tables).

• index: full index scan (still scans “everything”, just through the index).

• range: index range scan (often good: “scan only the relevant slice”).

Vo Hoang Nhat Khang 192

 10.3 Using EXPLAIN in MySQL

• ref: non-unique index lookup (good for selective equality predicates).

• eq_ref: unique index lookup per row (excellent for PK/FK joins).

• const/system: treated as constant row sources (very fast).

Note: Dont memorize; build intuition

Instead of memorizing definitions, I will ask this question: “Is MySQL scanning a
lot of data, or jumping directly to what it needs?”

The Extra column

The Extra column is where MySQL quietly tells you about work that is happening
behind the scenes. Sometimes the plan looks fine at first glance because an index is
being used, but Extra reveals an additional step that can dominate runtime on larger
tables.
Two messages are especially common:

• Using filesort MySQL must perform an explicit sort step.

• Using temporary MySQL must build a temporary table to finish grouping, dis-
tinct, or sorting work.

Example: Filesort due to ORDER BY without a supporting index

EXPLAIN
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 1
ORDER BY OrderDate DESC;

This query is very natural: “give me the orders for one customer, newest first.”
MySQL can use the foreign key index on CustomerID to find the matching rows
quickly, but it still has to reorder them by OrderDate. That separate sorting step
appears as Using filesort.
Sample output:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Orders NULL ref fk_orders_customers fk_orders_customers 4 const 2 100.00 Using filesort

Despite the name, Using filesort does not automatically mean “sorting on disk.”
It means MySQL cannot output rows in the requested order straight from an index, so
it performs an explicit sort step in the execution plan.
If you frequently run this query shape, a composite index such as:

(CustomerID, OrderDate)

often lets MySQL read rows already ordered by OrderDate within each CustomerID,
reducing or removing the need for a separate sort.

Vo Hoang Nhat Khang 193

10 Reading Execution Plans in MySQL

Example: Temporary table due to grouping on an expression

EXPLAIN
SELECT YEAR(OrderDate) AS OrderYear,
COUNT(*) AS OrderCount
FROM Orders
GROUP BY YEAR(OrderDate);

This query groups by YEAR(OrderDate), which is a computed value rather than a

stored column. For a small table this is fine, but MySQL typically cannot use a sim-
ple index on OrderDate to group by the computed year. A common consequence
is that it builds a temporary table to track groups.
Sample output:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Orders NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary

Note: Why YEAR(OrderDate) often triggers Using temporary

When you write GROUP BY YEAR(OrderDate), MySQL must first compute a year
value for each row. Even if OrderDate is indexed, that index is ordered by full dates,
not by the extracted year. So MySQL cannot simply walk the index and “see” year-
groups in order. A temporary structure becomes the simplest way to collect counts
per year as rows are scanned.

A more index-friendly rewrite

If you run this report frequently and the table is large, you typically want a grouping
key that MySQL can read in an already-grouped order.
One practical approach is to store the year explicitly and index it.
Example: Making grouping cheap by storing the year
ALTER TABLE Orders
ADD COLUMN OrderYear SMALLINT
AS (YEAR(OrderDate)) STORED;

CREATE INDEX idx_orders_orderyear ON Orders (OrderYear);

EXPLAIN
SELECT OrderYear,
COUNT(*) AS OrderCount
FROM Orders
GROUP BY OrderYear;

Sample output:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Orders NULL index idx_orders_orderyear idx_orders_orderyear 3 NULL 6 100.00 Using index

In this version, the grouping key is a real stored column. MySQL can read rows in

Vo Hoang Nhat Khang 194

 10.3 Using EXPLAIN in MySQL

OrderYear order directly from the index, and it no longer needs a separate temporary
structure to track groups.
Caution: Stored generated columns are a design choice
A stored generated column takes space and must be maintained on writes. If
Orders is write-heavy, you may prefer to keep the schema simple and accept the
temporary table for the report query. If the table is read-heavy and this report is
common, the stored column is often worth it.

If you cannot change the schema

Sometimes you do not control the schema, or you do not want an extra column. In
that case, you can still make grouping more predictable by grouping on a date range
boundary rather than an extracted expression.
Example: Grouping by a year bucket without storing a new column
EXPLAIN
SELECT DATE_FORMAT(OrderDate, '%Y-01-01') AS YearStart,
COUNT(*) AS OrderCount
FROM Orders
GROUP BY DATE_FORMAT(OrderDate, '%Y-01-01');

Sample output:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Orders NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary

This does not magically avoid the temporary table. It is mainly a readability move:
it makes the grouping key explicit as “the start of the year”. The core cost is the same
because MySQL still must compute a derived value per row.
Note: A simple rule you can trust
Grouping by an expression usually means MySQL must compute first and group
later. Grouping by an indexed column gives MySQL a chance to group as it reads.
That one difference often decides whether you see Using temporary.

Vo Hoang Nhat Khang 195

10 Reading Execution Plans in MySQL

Vo Hoang Nhat Khang 196

Chapter 11 MySQL Reference Guide

11.1 Core SQL Keywords in MySQL

This section summarizes core SQL keywords as implemented in MySQL. The goal is
not to be exhaustive, but to give you a compact mental map of the most important
language elements you will use every day.

Data definition and structure

• CREATE DATABASE, DROP DATABASE
Create or remove databases.

• CREATE TABLE, ALTER TABLE, DROP TABLE

Define, modify, and remove tables.

• PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT

Column and table constraints for data integrity.

• ENGINE
MySQL-specific option used in CREATE TABLE / ALTER TABLE to choose the stor-
age engine (e.g. InnoDB):
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
Balance DECIMAL(12,2) NOT NULL
) ENGINE = InnoDB;

• AUTO_INCREMENT
MySQL keyword to define automatically increasing integer columns, usually used
for primary keys:
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
CustomerName VARCHAR(200) NOT NULL
);

Querying and filtering

• SELECT
Retrieve data from one or more tables.

• FROM
Specify the source table(s).

Vo Hoang Nhat Khang 197

11 MySQL Reference Guide

• WHERE
Filter rows before grouping or aggregation.

• GROUP BY, HAVING

Group rows and filter groups based on aggregate conditions.

• ORDER BY
Sort the result:

SELECT CustomerID, TotalAmount

FROM Orders
ORDER BY TotalAmount DESC, CustomerID ASC;

• LIMIT [OFFSET] (MySQL-specific syntax)

Limit the number of rows (with optional offset) returned by a query:

-- First 10 rows
SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 10;

-- Rows 1120 (offset 10, then 10 rows)

SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 10 OFFSET 10;

Modifying data
• INSERT, INSERT IGNORE, REPLACE
Insert new rows (with MySQL-specific variants for handling duplicates).

• UPDATE
Modify existing rows in-place.

• DELETE
Remove rows from a table.

• TRUNCATE TABLE
Quickly remove all rows from a table (DDL-style operation).

Joins and set operations

• INNER JOIN, LEFT JOIN, RIGHT JOIN
Combine rows from multiple tables based on join conditions.

• UNION, UNION ALL

Combine result sets from multiple SELECT statements.

• ON, USING
Specify join conditions.

Vo Hoang Nhat Khang 198

 11.2 MySQL Data Types

Other useful MySQL keywords

• EXPLAIN
Inspect the execution plan of a query.

• DESCRIBE / SHOW COLUMNS

Inspect table structure.

• SHOW TABLES, SHOW DATABASES

Inspect schema-level metadata.

• BEGIN, COMMIT, ROLLBACK

Control transactions (with InnoDB or other transactional engines).

11.2 MySQL Data Types

MySQL supports a rich set of data types. Choosing the right type at table definition
time is important for correctness, performance, and storage efficiency. Here we sum-
marize the main families: numeric types, string and text types, date and time types,
and some special MySQL types such as JSON.

11.2.1 Numeric Types

Numeric types in MySQL are divided into integer types and approximate/exact deci-
mal types.

Integer types
Common integer types include:

• TINYINT, SMALLINT, MEDIUMINT, INT (or INTEGER), BIGINT

• Optional UNSIGNED variants (no negative values).

Typical usage:

CREATE TABLE VisitStats (

VisitID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
UserID INT UNSIGNED NOT NULL,
PageViews INT UNSIGNED NOT NULL DEFAULT 0
);

Guidelines:

• Use INT or BIGINT for primary keys and IDs.

• Use smaller types when ranges are known to be limited (e.g. TINYINT for flags or
small codes).

Vo Hoang Nhat Khang 199

11 MySQL Reference Guide

Exact decimals
Exact decimal types in MySQL use DECIMAL(p, s):

• p: total number of digits,

• s: digits after the decimal point.

Example for financial data:

CREATE TABLE Payments (

PaymentID INT PRIMARY KEY,
Amount DECIMAL(12,2) NOT NULL,
TaxRate DECIMAL(5,4) NOT NULL, -- e.g. 0.0750
PaidAt DATETIME NOT NULL
);

Floating-point types
Approximate numeric types:

• FLOAT, DOUBLE

Use them when:

• you deal with scientific or statistical data,

• small rounding errors are acceptable,

• exact decimal representation is not required.

Avoid them for prices or financial amounts; prefer DECIMAL instead.

11.2.2 String and Text Types

MySQL offers several string types, differing in maximum length and storage strategy.

Fixed and variable-length strings

• CHAR(n): fixed-length strings (padded with spaces).

• VARCHAR(n): variable-length strings up to n characters.

Example:

CREATE TABLE Users (

UserID INT PRIMARY KEY,
UserName VARCHAR(100) NOT NULL,
CountryCode CHAR(2) NOT NULL,
Email VARCHAR(255) NOT NULL
);

Vo Hoang Nhat Khang 200

 11.2 MySQL Data Types

Text types
For longer text, MySQL provides:

• TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

Example:

CREATE TABLE Articles (

ArticleID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Body MEDIUMTEXT NOT NULL
);

Guidelines:

• Use VARCHAR for structured or moderately sized text (names, emails, titles).

• Use TEXT-family types for large, unstructured content.

11.2.3 Date and Time Types

MySQL supports several temporal types:

• DATE: calendar date (YYYY-MM-DD).

• TIME: time of day (HH:MM:SS, with optional fractions).

• DATETIME: date and time, no time zone.

• TIMESTAMP: date and time, stored as UTC internally, with range limits.

• YEAR: year values.

Example:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL,
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);

Typical pattern:

• Use DATE when you only care about dates.

• Use DATETIME or TIMESTAMP for audit fields such as CreatedAt, UpdatedAt.

• Standardize how you handle time zones at the application level (e.g. always store
UTC).

Vo Hoang Nhat Khang 201

11 MySQL Reference Guide

11.2.4 JSON and Other Special Types

MySQL includes several specialized types beyond standard relational types.

JSON
The JSON type is MySQLs way of saying: “Yes, you can store a structured document here,
and I will treat it as more than just a string.” Unlike a VARCHAR that happens to contain
JSON text, a real JSON column is validated on write. If the document is not valid JSON,
MySQL rejects it. MySQL also stores JSON values in an internal binary format designed
for fast access to nested keys and array positions, so it does not need to parse the text
every time you read a field out of it.1
Note: When JSON is a good fit
JSON shines when the shape of the data is genuinely flexible: event payloads, fea-
ture flags, external API responses, metadata that differs per row, or optional nested
attributes that would otherwise explode into many nullable columns.

Creating JSON values. You can insert JSON literals directly, but in real schemas it is
often cleaner to build documents with JSON_OBJECT() and JSON_ARRAY() so that quot-
ing and escaping are handled safely.
Let’s say we create a sample database:
DROP TABLE IF EXISTS Events;

CREATE TABLE Events (

EventID BIGINT UNSIGNED PRIMARY KEY,
EventType VARCHAR(50) NOT NULL,
OccurredAt DATETIME NOT NULL,
Payload JSON NOT NULL
);

INSERT INTO Events (EventID, EventType, OccurredAt, Payload) VALUES

(1, 'checkout', '2025-01-05 10:15:00',
JSON_OBJECT(
'user', JSON_OBJECT('id', 42, 'name', 'Alice'),
'total', 120.50,
'items', JSON_ARRAY(
JSON_OBJECT('sku', 'BK-001', 'qty', 1),
JSON_OBJECT('sku', 'PN-010', 'qty', 2)
)
)
),
(2, 'signup', '2025-02-01 09:00:00',
JSON_OBJECT(
'user', JSON_OBJECT('id', 77, 'name', 'Bob'),
'ref', 'campaign-2025'
)
);
1
MySQL 8.4 Reference Manual: The JSON Data Type. [Link]
[Link]

Vo Hoang Nhat Khang 202

 11.2 MySQL Data Types

Example: Selecting events and extracting fields from JSON

SELECT
EventID,
EventType,
OccurredAt,
Payload->>'$.[Link]' AS UserName
FROM Events
ORDER BY EventID;

Sample output:

EventID EventType OccurredAt UserName

1 checkout 2025-01-05 10:15:00 Alice
2 signup 2025-02-01 09:00:00 Bob

Extracting fields with JSON paths. Most JSON work in MySQL comes down to one
idea: a path expression selects a value inside the document. Paths start with $ to mean
“this document”, then walk into objects and arrays. A member lookup looks like
$.[Link], and an array position looks like $.items[0].2
Example: Extracting nested fields
SELECT EventID,
Payload->'$.[Link]' AS NameAsJson,
Payload->>'$.[Link]' AS NameAsText,
Payload->>'$.items[0].sku' AS FirstSku
FROM Events
ORDER BY EventID;

Sample output:

EventID NameAsJson NameAsText FirstSku

1 "Alice" Alice BK-001
2 "Bob" Bob NULL

The operators -> and -» are shorthand for extraction. The difference matters in
practice:

• -> returns a JSON value, so strings keep their JSON quotes.

• -» returns an unquoted SQL scalar when possible, which is usually what you
want for reporting and comparisons.

Note: A small mental model for paths

Read $ as “the document”, the dot as “go into this key”, and brackets as “pick
an array position”. Once you can read paths fluently, JSON queries stop feeling

2
MySQL 8.4 Reference Manual: JSON Path Syntax. [Link]
[Link]

Vo Hoang Nhat Khang 203

11 MySQL Reference Guide

magical.

Modifying JSON documents. MySQL offers several update-style functions. Three

that show up everywhere are JSON_SET(), JSON_INSERT(), and JSON_REPLACE(). They
all accept a document plus path–value pairs, but differ in whether they overwrite ex-
isting values or only add new ones.3

Example: Updating a JSON payload with JSON_SET

UPDATE Events
SET Payload = JSON_SET(Payload, '$.total', 130.00, '$.coupon',
,→ 'WELCOME10')
WHERE EventID = 1;

SELECT EventID, Payload->>'$.total' AS Total, Payload->>'$.coupon' AS

,→ Coupon
FROM Events
WHERE EventID = 1;

Sample output:

EventID Total Coupon

1 130.00 WELCOME10

Caution: JSON updates can hide schema drift

It is easy to introduce two spellings of the same concept, especially in large systems:
"coupon", "Coupon", "discountCode". If JSON becomes an important interface be-
tween teams, treat key names like an API: document them, version them, and test
them.

Searching inside JSON. For filtering, you can extract a field and compare it, or use
JSON search functions when you need “does this path exist?” or “does this array con-
tain a value?”.

Example: Filtering events by a JSON attribute

SELECT EventID, EventType, OccurredAt
FROM Events
WHERE Payload->>'$.ref' = 'campaign-2025';

Sample output:

EventID EventType OccurredAt

2 signup 2025-02-01 09:00:00

3
MySQL 8.4 Reference Manual: Searching and Modifying JSON Values. [Link]
refman/8.4/en/[Link]

Vo Hoang Nhat Khang 204

 11.2 MySQL Data Types

Indexing JSON fields. A JSON document can be large and deeply nested, but indexes
want stable, scalar values. The classic pattern in MySQL is to create a generated column
that extracts the field you care about, then index that generated column.4

Example: Indexing a JSON field via a generated column

ALTER TABLE Events
ADD COLUMN UserId BIGINT
GENERATED ALWAYS AS (CAST(Payload->>'$.[Link]' AS UNSIGNED)) STORED,
ADD INDEX idx_events_userid (UserId);

SELECT EventID, EventType

FROM Events
WHERE UserId = 42;

Sample output:

EventID EventType
1 checkout

This is the turning point where JSON stops being “a blob of nested text” and be-
comes queryable at scale. You keep the flexible payload, but you also give the optimizer
something index-friendly to hold on to.

Comparisons and ordering. MySQL allows JSON values to be compared with stan-
dard comparison operators, but some familiar SQL conveniences are not supported di-
rectly for JSON values in MySQL 8.4, such as BETWEEN, IN(), GREATEST(), and LEAST().
The practical workaround is to cast extracted values to a native SQL type before com-
paring.5

Example: Casting extracted JSON for consistent comparison

SELECT EventID,
CAST(Payload->>'$.total' AS DECIMAL(10,2)) AS Total
FROM Events
WHERE CAST(Payload->>'$.total' AS DECIMAL(10,2)) >= 100.00
ORDER BY Total DESC;

Sample output:

EventID Total
1 130.00

4
MySQL 8.4 Reference Manual: JSON column extraction operators and generated-column indexing notes.
[Link]
5
MySQL 8.4 Reference Manual: Comparison and Ordering of JSON Values. [Link]
doc/refman/8.4/en/[Link]

Vo Hoang Nhat Khang 205

11 MySQL Reference Guide

Note: A healthy rule of thumb

Store JSON when the shape is flexible, but pull stable, high-value attributes out
into real columns once you know you will query them often. That way you get the
best of both worlds: flexible ingestion and predictable performance.

Binary types
For raw binary data:

• BINARY(n), VARBINARY(n),

• TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.

Example:

CREATE TABLE Files (

FileID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Content LONGBLOB NOT NULL
);

Bit and Boolean

MySQL has:

• BIT(n): bit fields.

• BOOLEAN / BOOL: synonyms for TINYINT(1).

Example:

CREATE TABLE Features (

FeatureID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Enabled BOOLEAN NOT NULL DEFAULT TRUE
);

11.3 MySQL Built-in Functions

MySQL provides a large library of built-in functions. This section groups some of the
most commonly used ones by category. The focus is on quick reference and typical
usage patterns rather than exhaustive coverage.

11.3.1 String Functions

MySQL string functions operate on CHAR, VARCHAR, and text columns.

Vo Hoang Nhat Khang 206

 11.3 MySQL Built-in Functions

Basic string operations

• LENGTH(str): length in bytes.

• CHAR_LENGTH(str): length in characters.

• LOWER(str), UPPER(str): case conversion.

• TRIM([BOTH] ’x’ FROM str), LTRIM(str), RTRIM(str): remove surrounding char-

acters/whitespace.

• CONCAT(a, b, ...): concatenate multiple strings.

Example:

SELECT CONCAT(UPPER(LastName), ', ', FirstName) AS DisplayName

FROM Customers;

Substrings and searching

• SUBSTRING(str, pos, len): extract substring.

• LEFT(str, len), RIGHT(str, len).

• LOCATE(substr, str) or INSTR(str, substr): position of substring.

• REPLACE(str, from_str, to_str): replace occurrences.

Example:

SELECT Email,
SUBSTRING_INDEX(Email, '@', -1) AS Domain
FROM Users;

(SUBSTRING_INDEX is MySQL-specific and handy for splitting on delimiters.)

11.3.2 Numeric Functions

Numeric functions perform arithmetic or numeric transformations.

Basic arithmetic and rounding

• ABS(x): absolute value.

• ROUND(x, d): round to d decimal places.

• CEILING(x), FLOOR(x): round up/down to nearest integer.

• POWER(x, y) or POW(x, y): exponentiation.

Example:

SELECT Amount,
ROUND(Amount * 1.10, 2) AS AmountWithTax
FROM Invoices;

Vo Hoang Nhat Khang 207

11 MySQL Reference Guide

Random numbers and other utilities

• RAND() or RAND(seed): pseudo-random number in [0, 1).

• GREATEST(a, b, ...), LEAST(a, b, ...): max/min of arguments.

11.3.3 Date and Time Functions

MySQL includes many functions for working with DATE, TIME, and DATETIME / TIMESTAMP
types.

Getting the current time

• NOW() or CURRENT_TIMESTAMP: current date and time.

• CURDATE(): current date.

• CURTIME(): current time of day.

Extracting parts of a date/time

• YEAR(dt), MONTH(dt), DAY(dt).

• HOUR(dt), MINUTE(dt), SECOND(dt).

• DATE(dt): date part only.

• TIME(dt): time part only.

Example:

SELECT OrderID,
OrderDate,
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth
FROM Orders;

Date arithmetic
• DATE_ADD(date, INTERVAL n unit), DATE_SUB(...): add or subtract intervals.

• DATEDIFF(date1, date2): difference in days.

• TIMESTAMPDIFF(unit, datetime1, datetime2): difference in given units (e.g.

days, months).

Example:

SELECT OrderID,
OrderDate,
DATE_ADD(OrderDate, INTERVAL 30 DAY) AS DueDate
FROM Orders;

Vo Hoang Nhat Khang 208

 11.3 MySQL Built-in Functions

11.3.4 Aggregation and Window Functions

Classic aggregates

MySQL supports the usual aggregate functions:

• COUNT(*), COUNT(expr),

• SUM(expr), AVG(expr),

• MIN(expr), MAX(expr).

Example:

SELECT CustomerID,
COUNT(*) AS OrderCount,
SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;

Window (analytic) functions

Newer MySQL versions support window functions with OVER():

• ROW_NUMBER(), RANK(), DENSE_RANK(),

• SUM(), AVG(), COUNT() as window aggregates.

Example: running total per customer, ordered by order date:

SELECT CustomerID,
OrderID,
OrderDate,
TotalAmount,
SUM(TotalAmount) OVER (
PARTITION BY CustomerID
ORDER BY OrderDate
) AS RunningTotal
FROM Orders;

Window functions allow you to compute per-row analytics without collapsing groups
into a single row.

11.3.5 Control Flow Functions (IF, CASE, etc.)

MySQL supports several control flow constructs that allow conditional logic inside
queries.

Vo Hoang Nhat Khang 209

11 MySQL Reference Guide

IF and IFNULL
• IF(condition, true_expr, false_expr): returns true_expr if condition is true,
else false_expr.

• IFNULL(expr, alt): returns alt if expr is NULL, otherwise expr.

Example:

SELECT OrderID,
TotalAmount,
IF(TotalAmount >= 100, 'High', 'Normal') AS OrderCategory
FROM Orders;

CASE expressions
CASE is standard SQL and more flexible than IF:

SELECT OrderID,
TotalAmount,
CASE
WHEN TotalAmount >= 500 THEN 'VIP'
WHEN TotalAmount >= 100 THEN 'Premium'
ELSE 'Standard'
END AS Segment
FROM Orders;

Other helpful functions

• NULLIF(a, b): returns NULL if a = b, otherwise returns a. Useful for avoiding
division by zero:
SELECT Sales,
Targets,
Sales / NULLIF(Targets, 0) AS AchievementRatio
FROM MonthlyPerformance;

These reference sections are intended as a quick guide while you practice MySQL.
As you write more queries, you will naturally build intuition for which keywords, data
types, and functions are most useful for your style of analytics and application devel-
opment.

Vo Hoang Nhat Khang 210

 11.3 MySQL Built-in Functions

Further Reading and References

This chapter collects a small set of primary sources and highquality books that you can explore
after finishing this book. They are grouped into official documentation, practical SQL guides,
and deeper relational theory.

Official Documentation
• MySQL 8.0 Reference Manual. Oracle. The canonical reference for MySQL
syntax, behaviour, and features, including full details of SQL statements, data
types, and server configuration. Available online: [Link]
refman/8.0/en/

• MySQL Documentation Portal. Entry point to all MySQL manuals (server, Work-
bench, connectors, cloud services, and more). Useful when you need authorita-
tive details for specific tools or deployment environments. [Link]
com/doc/

• SQL Standard (ISO/IEC 9075). The formal definition of the SQL language, pub-
lished by ISO/IEC. Access usually requires purchase via national standards bod-
ies, but many summaries and discussions are available online.

Practical SQL and MySQL Books

• John L. Viescas. SQL Queries for Mere Mortals: A Hands-On Guide to Data Manip-
ulation in SQL (4th ed., AddisonWesley, 2018). A very readable, example-driven
guide to writing correct and expressive queries across major SQL dialects; excel-
lent for practicing the mental habits of query formulation.

• Alan Beaulieu. Learning SQL (OReilly, latest edition). A concise but thorough in-
troduction that moves from basic queries to joins, grouping, and more advanced
patterns. Good as a first serious SQL book and as a compact reference.

• Anthony Molinaro et al. SQL Cookbook (OReilly, 2nd ed.). A collection of “how
do I do X?” recipes for real-world querying problemsgreat for seeing multiple
solutions and idioms once you already know the basics.

• Ben Forta. SQL in 10 Minutes, Sams Teach Yourself (Sams Publishing, latest edi-
tion). Short, focused lessons that are good for quick refreshers or for readers who
prefer very small steps and many hands-on exercises.

Vo Hoang Nhat Khang 211

11 MySQL Reference Guide

• SQL Pocket / Quick References. Several publishers offer small pocket guides
(for example, OReillys SQL Pocket Guide) that summarise syntax and functions on
a few dozen pages. These are handy when you already understand the concepts
and just need exact syntax quickly.

Relational Theory and Database Design

• C. J. Date. An Introduction to Database Systems (multiple editions, AddisonWes-
ley). A classic, in-depth textbook on database systems with a strong focus on the
relational model. Recommended if you want to understand the theory underly-
ing SQL and relational databases.

• C. J. Date. Database Design and Relational Theory: Normal Forms and All That Jazz
(2nd ed., Apress, 2019). A practitioner-oriented book on how to apply relational
theory to schema design, including keys, dependencies, and normalization.

• E. F. Codds original relational papers. Historical but still enlightening if you are
curious about the origin of the relational model. Collections and commentaries
(for example, Codd and Relational Theory by C. J. Date et al.) provide guided access
to these foundational ideas.

• General Database Textbooks. Comprehensive works such as Database System

Concepts (Silberschatz, Korth, and Sudarshan) or similar university texts cover
transaction management, indexing, query processing, and distributed databasestop-
ics that go beyond the scope of this introductory SQL book but are essential for
full database engineering.

Vo Hoang Nhat Khang 212

Afterword

When you opened this book, SELECT and JOIN might have felt like mysterious spells.
Now, if you have walked with me through these chapters, they are part of your ev-
eryday vocabulary. Somewhere between the first simple query and the last advanced
pattern, something important happened: you stopped just using SQL and started to
understand it.
That understanding is quiet and invisible. It shows up not in a single big moment,
but in small ones: when you look at a messy requirement and your mind naturally
starts to think in tables and relationships; when a query fails and instead of frustra-
tion you feel curiosity; when you realize you can design your own solution instead of
searching for one more snippet to copy.
This book was never meant to be the final word on SQL or on MySQL. Databases
evolve, projects change, and there will always be new features, new engines, and new
buzzwords. But underneath all of that, the core ideas you have practiced here remain
the same: sets, joins, keys, constraints, and the discipline of asking precise questions
about data.
If there is one thing I hope you carry forward, it is this:

You can reason about data. You can read it, shape it, and question it with confidence.

It does not matter whether you are building products, doing research, exploring
ideas as a student, or simply trying to understand the numbers behind a story. The
ability to think clearly about data is a kind of quiet power. SQL - and MySQL in par-
ticular, as you have used throughout this book - is just one of the tools that gives that
power a concrete, practical form.
As you move on from these pages:

• Keep experimenting. Try queries that feel too complex. Break them, then fix
them.

• Keep asking “why?” when something works, not only when it fails.

• Keep designing schemas that respect your data and the people behind it.

If, someday, you open a large, unfamiliar MySQL database (or any relational database)
and feel a small sense of calm instead of fear; if you sketch a schema on a whiteboard
and see how the pieces fit; if you help someone else write their first query and watch
their eyes light up - that will be the real continuation of this book.
Thank you for reading, for thinking, and for trusting me to guide you for a little
while on your learning journey. I hope the time you spent here will echo in many
projects, many ideas, and many quiet victories still ahead.
About This Book About the author
This book is a practical guide for VO HOANG NHAT KHANG is a Ph.D.
readers who want to move beyond copy- student in Natural Language
paste SQL and build a deep, relational Processing at MBZUAI, working at the
understanding of data. intersection of language, vision,
and learning from large-scale data.
Starting from first principles, the
His research interests include
book walks through core querying,
multimodal representation learning,
joins, grouping, constraints, and
vision-language model, uncertainty
MySQL-specific techniques. Each
quantification, and large language
chapter encourages you to read queries
models.
as precise statements about sets and
relationships, not just as syntax to This book grew out of many
memorize. conversations with developers and
Whether you are a developer, data analysts who felt confident copying
analyst, or student, this book is queries from old code, but less
designed to give you a solid mental confident explaining why those
model of how SQL “thinks” so that your queries worked. It is written
queries become clearer, safer, and more for readers who want to make that
expressive over time. leap: from memorizing patterns to
understanding SQL as a precise language
for describing data.

Version 0.1 • 2026