Projection Operation: Relational Algebra Theory vs. The Reality of Queries

Reading Time: 7 minutes

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 π (Pi).

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:

π_{a1, a2, … , an} (R)

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 results in rows (tuples) that are identical, relational algebra acts as a ruthless data police officer: it instantly eliminates those duplicates, leaving only unique values.

A Real-World Example

Let’s look at how this theory works on a table called Employees:

Employee_ID	Name	Gender	City
E01	Ali	Male	Jakarta
E02	Budi	Male	Bandung
E03	Citra	Female	Jakarta
E04	Dedi	Male	Jakarta

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:

π_City (Employee)

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:

City
Jakarta
Bandung

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. Based on E.F. Codd’s sacred relational algebra rules, the output should only show two unique rows: Jakarta and Bandung.

However, when you hit Enter or click Execute, here is the plot twist. Your monitor displays this instead:

+---------+
| City    |
+---------+
| Jakarta |
| Bandung |
| Jakarta |
| Jakarta |
+---------+

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?

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 Transaction_Date 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,000 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 query result that is theoretically perfect—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:

+---------+
| City    |
+---------+
| Jakarta |
| Bandung |
+---------+

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.

0