Why do database textbooks demand duplicate removal while your SQL code doesn’t? Let’s unmask the biggest compromise between pure mathematics and computer performance.
1. Introduction
The projection operation is one of eight operations introduced by Edgar F. Codd (better known as E.F. Codd), a British mathematician and computer scientist who was then working for IBM. He published a landmark paper entitled “Relational Completeness of Data Base Sublanguages” in March 1972.
In this legendary paper, Codd proposed a radical idea that shocked the industry: What if we hide the physical complexity of the computer from the user? Codd introduced Relational Algebra — a pure mathematical approach built on the foundation of Set Theory and predicate logic. He suggested that instead of viewing data as a messy chain of pointers, we should represent it elegantly as two-dimensional tables (called relations), made up of rows (tuples) and columns (attributes).
Relational algebra acts as a mathematical, procedural query language. It provides a set of logical operations to manipulate and filter these data sets without caring about how the computer stores them on a hard disk. Codd’s massive contribution not only birthed the relational model but also sparked the creation of the query language we know today as SQL, earning him the Turing Award — the Nobel Prize of computer science — in 1981.
Yet, here lies the ultimate irony. The SQL we use today to implement E.F. Codd’s sacred mathematical theory actually hides a “dark secret” — a major compromise made for the sake of real-world industry demands.
2. The Projection Operation: A “Sacred” Vertical Slice
To understand why SQL and mathematics clash, we need to dissect one of the most fundamental operations in relational algebra: Projection (symbolized by the Greek letter π).
If we imagine a database table as a cake, the Projection (Ď€) operation is the knife that slices the table vertically. Its main job is highly specific: choose which columns (attributes) you want to see, and discard the rest.
In the legendary textbook by Thomas M. Connolly & Carolyn E. Begg, “Database Systems: A Practical Approach to Design, Implementation, and Management” (2015), this operation is defined with absolute precision:
The Projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.
Pay close attention to that last phrase: “…and eliminating duplicates.” This is where the mathematical purity of relational algebra comes into play. Because E.F. Codd built this system on Set Theory, the strict laws of sets must be enforced. In mathematics, a set cannot have duplicate members. If you have a set of colors like {Red, Blue, Red}, it is mathematically redundant and automatically reduced to {Red, Blue}.
This iron rule applies strictly to the Projection operation. If slicing columns produces rows (tuples) that are identical, relational algebra acts as a ruthless data police officer: it instantly eliminates duplicates, leaving unique values.
A Real-World Example
Let’s look at how this theory works on a table called Employees:
Imagine you are a manager who only wants to know which cities your employees come from. You don’t need their IDs, names, or genders. In relational algebra, you would write this projection query:
Here is what happens behind Codd’s mathematical curtain:
- Step 1: The system performs a vertical cut. It isolates the City column and discards everything else. The temporary result is a list containing: Jakarta, Bandung, Jakarta, Jakarta.
- Step 2: Set theory laws are enforced. Because Jakarta appears three times, the system flags them as invalid duplicates and instantly prunes the extra twin data. The final result of that projection is a clean, elegant, and theoretically perfect table:
On paper, this operation is logical, beautiful, and completely free of data redundancy. However, this mathematical beauty shatters the moment software engineers try to implement the π formula into real computer systems using SQL.
3. The Plot Twist: Reality When We Write SQL
Now, let’s leave the classroom full of math equations behind and open our code editors in the real world.
Imagine you are building an application using a popular relational DBMS like SQL Server, MySQL, PostgreSQL, or Oracle. You have the exact same Employees table. To find the employees’ home cities, you type the most standard SQL command in the world:
SELECT City
FROM Employees;
Theoretically, this query directly represents the Projection operation (πCity (Employee)) we just discussed. According to E.F. Codd’s rules for relational algebra, the output should show only two unique rows: Jakarta and Bandung.
However, when you hit Enter or click Execute, here is the plot twist. Your monitor displays this instead:
The duplicates ARE NOT DELETED! Jakarta confidently appears three times, standing in perfect alignment with the number of employees in the original table. SQL just served you a dataset that — by pure mathematical standards — is considered flawed because it contains duplicate elements.
This contradiction naturally raises eyebrows. How can a programming language claimed to be the global standard for implementing the Relational Model openly violate the most fundamental rule of the model’s inventor?
The secret lies in a foundational shift of mathematical models. While E.F. Codd’s relational algebra strictly operates on Set Semantics (where duplicate rows are automatically purged), relational database management systems (RDBMS) process data using Bag Semantics — often referred to as Multiset Semantics. In a bag, identical elements are allowed to coexist peacefully.
Why are theory on paper and practice in the field so wildly inconsistent? Did the brilliant engineers who created SQL at IBM make a mistake? Or did they intentionally “rebel” against E.F. Codd’s pure theory to hide a flaw?
The truth is, they weren’t being sloppy or lazy. There is a massive — and incredibly logical — reason why this compromise had to be made.
4. The Reason Behind SQL’s “Rebellion”
SQL’s decision to let duplicate data stick around wasn’t a historical accident; it was a conscious design choice. Early SQL architects slammed into the hard realities of the industry. They realized that mathematical perfection on paper often has to be sacrificed when dealing with hardware limitations and pragmatic business needs.
There are two primary reasons why SQL chose to pivot away from strict relational algebra:
(a) The Heavy Performance Overhead
In a world of pure relational algebra, every single time you slice columns (Projection), the computer is forced to guarantee that no twin rows exist.
Imagine you are a giant e-commerce platform with a Transactions table containing 100 million rows of data. You want to run a projection to see the TransactionDate column.
- If SQL obeyed the theory: Every time the query runs, the database engine would have to allocate massive amounts of RAM just to sort those 100 million rows, comparing them one by one to eliminate identical dates. This deduplication process takes an immense amount of CPU time. A query that should take 0.5 seconds could balloon into minutes just because it’s forced to clean up duplicates.
- SQL’s Shortcut: To achieve lightning-fast speeds, SQL adopts a pragmatic approach called Multiset Semantics (or Bag Semantics). SQL treats tables like a shopping bag: it grabs the data instantly from the hard drive exactly as it is, without wasting time checking for duplicates. ### (b) Business Logic: Duplicates Are the Lifeblood of Accounting
In pure mathematics, the number 50 appearing five times inside a set is still evaluated as a single element: {50}. But to a financial manager or accountant, 50 appearing five times means five different customers made purchases, totaling $250 in revenue.
Imagine if you ran a projection on the Total_Spent column in your sales table:
- If pure theory applied automatically: If 100 people happened to spend exactly $50 today, a strict relational algebra system would delete 99 of those records and leave just one row showing $50.
- The Impact: Business aggregate functions in SQL would immediately become a disaster. Commands to calculate total revenue (SUM) or average sales (AVG) would break completely because the duplicate data representing real, unique transactions was wiped out by the system for the sake of “pure math.” This is why commercial industries ultimately won the argument. System performance and data integrity for business analysis are far more valuable than rigid compliance with set theory. SQL intentionally breaks Codd’s rule so that databases can work incredibly fast and remain useful in the real world.
5. The Middle Ground: The ISO SQL Standard Solution
Seeing the massive chasm between academic theory and industry needs, the international standards committee stepped in to mediate. They didn’t want SQL to lose its strong mathematical roots, but they also couldn’t sacrifice the performance that businesses worldwide relied on.
To resolve this “cold war” between purist computer scientists and pragmatic developers, the SQL standards committee (formally governed by ISO/IEC 9075) settled on a highly elegant middle ground.
Instead of forcing one side to compromise, the ISO SQL Standard designed a flexible language architecture by offering two operational modes that programmers can deliberately choose from:
(a) The Default Mode: SELECT ALL (Industry Compromise)
When you write a standard query like this:
SELECT City
FROM Employees;
Behind the scenes, the database engine implicitly translates your query by adding an invisible keyword, ALL:
SELECT ALL City
FROM Employees;
This is the default mode adopted by the SQL Standard. It intentionally allows duplicates to prioritize high-speed execution and support business calculations (SUM, COUNT, AVG) that require full transactional history.
(b) The Magic Switch: SELECT DISTINCT (Back to Mathematical Purity)
The ISO SQL committee still deeply respected E.F. Codd’s scientific legacy. Therefore, they provided a “magic switch” in the form of the DISTINCT keyword.
If a programmer needs a theoretically perfect query result — completely stripped of duplicate rows according to Set Theory laws — they simply activate this switch explicitly:
SELECT DISTINCT City
FROM Employees;
The moment DISTINCT is typed, the database engine (whether it’s PostgreSQL, MySQL, or Oracle) shifts gears. The computer is now willing to work a bit harder to sort the data in memory, hunt down every identical row, filter them out, and deliver a 100% unique result:
If you inspect the Query Execution Plan of that query, you will see precisely how the database achieves this mathematical purity. To eliminate duplicates, the database engine typically relies on one of two heavy internal operations: Sorting (such as a Sort Distinct operation, which orders the rows to group duplicates together) or Hashing (such as a Hash Aggregate, where the engine builds an in-memory hash table to filter out repetitive values).
Because these operations require additional CPU cycles and memory (and can occasionally spill to disk if the dataset is massive), using DISTINCT indiscriminately on large tables can degrade performance. However, this cost can often be mitigated by proper database design—such as creating an Index on the projected columns, which allows the database engine to traverse pre-sorted data instantly without executing an expensive runtime sort.
Through this elegant compromise, the ISO SQL Standard created harmony. Structurally, SQL defaults to a super-fast, Multiset-based system for daily industrial operations, while keeping the door wide open for anyone who needs to enforce strict relational algebra whenever necessary.
6. Conclusion: A Beautiful Compromise Between Theory and Industry
Ultimately, the clash between the Projection operation in relational algebra and the SELECT statement in SQL teaches us a valuable lesson about how technology evolves. At its core, the world of software engineering is a discipline built on trade-offs.
The relational algebra introduced by E.F. Codd in 1970 is far from a broken or obsolete concept. On the contrary, it is a masterpiece that provides an unshakeable logical foundation. Without Codd’s strict set theory, we wouldn’t have a clear blueprint for how data should be organized, joined, and optimized inside a machine.
Yet, SQL was born out of real-world necessity. It wasn’t designed to win a math competition; it was built to solve practical business problems that demand blazing-fast speeds, memory efficiency, and accounting accuracy. SQL chose to relax the mathematical rules slightly to deliver the performance the global industry required.
So, when you open a textbook like Connolly & Begg (2015) and read that a relational projection must eliminate duplicates, remember that the book is 100% right in theory. And when you open your database terminal and type SELECT DISTINCT, remember that this single keyword is the beautiful bridge connecting both worlds.
DISTINCT is proof that pure theory and pragmatic practice don’t have to be enemies. It serves as a reminder to all tech practitioners that while we need a strong theoretical compass to build great systems, we must also stay flexible enough to shake hands with the realities of the physical world.
Hopefully beneficial. Your feedback will be precious.
References
Codd, E.F. (March 1972). “Relational Completeness of Data Base Sublanguages” in Computer Sciences. San Jose, California: IBM Research Laboratory.
Connolly, Thomas M., & Begg, Carolyn E. (2015). Database Systems: A PracÂtical Approach to Design, Implementation, and Management. (6th Edition). Essex, England: Pearson Education.
Coronel, C., Steven, M., Crockett, K., & Blewett, C. (2020). Database Principles: Fundamentals of Design, Implementation, and Management. 3rd Edition. Hampshire, United Kingdom: Cengage Learning
