When we write SQL, it often feels like we’re just describing the result we want and letting the database handle the rest. But under the hood, every SQL query is translated into a sequence of relational operators.

These operators come from relational algebra, and they are the fundamental building blocks the database uses to design, reorder, and optimize queries.

Think of them as the vocabulary the database understands.

Selection (Filtering Rows)

Selection chooses a subset of tuples (rows) from a relation that satisfy a predicate.

Conceptually, this is where filtering happens.

Example idea:

"Give me only the rows where status = `active`"

Relational algebra:

σ status = `active` (Users)

SQL:

SELECT * FROM users WHERE status = 'active';

Selection reduces the amount of data early, which is why databases try to push it as close to the data source as possible.

Projection (Choosing Columns)

Projection selects which attributes (columns) should appear in the result.

Example idea;

"I only care about user IDs and emails."

Relational algebra:

π user_id, email (Users)

SQL:

SELECT user_id, email FROM users;

Projection also allows expressions and reordering of the attributes, not just simple column selection.

Union (Combining Results)

Union merges two relations with the same schema, producing all tuples that appear in either input.

Example idea:

"Give me all users from table A or table B."

Relational algebra:

A ∪ B

SQL:

SELECT * FROM A
UNION
SELECT * FROM B;

Union is useful when data is split across multiple relations but represents the same logical entity.

Intersection (Finding Common Tuples)

Intersection returns only the tuples that appear in both relations.

Example idea:

"Which users appear in both datasets?"

Relational algebra:

A ∩ B

SQL:

SELECT * FROM A
INTERSECT
SELECT * FROM B;

This operator is less common in everyday SQL but powerful for analytical queries.

Difference (Set Subtraction)

Difference returns tuples that appear in one relation but not the other.

Example idea: “Users who signed up but never activated their account.”

Relational algebra:

A - B

SQL:

SELECT * FROM A
EXCEPT
SELECT * FROM B;

This operator is often used to express anti-joins or exclusive logic.

Cartesian Product (All Combinations)

Cartesian product pairs every tuple from one relation with every tuple from another.

Relational algebra:

A × B

SQL:

SELECT * FROM A CROSS JOIN B;

On its own, this operator is rarely useful because it explodes in size but it becomes powerful when combined with selection.

Join is a combination of Cartesian product and selection. It pairs tuples from two relations where shared attributes match.

Example idea:

"Attach each order to its customer"

Relational algebra:

Orders ⋈ Customers

SQL:

SELECT *
FROM orders
JOIN customers
USING (customer_id);

Joins are the heart of relational queries and the primary reason normalization works.

Designing Queries with Operators

The key insight of relational algebra is that operator order matters for performance, but not for correctness.

For example:

  • Filter then join
  • Join then filter

Both produce the same result, but one can be orders of magnitude faster.

This is why SQL is declarative. You describe the result, and the database uses relational operators to figure out how to get it efficiently.

Why this Matters

Relational operators aren’t just academic theory. They are the mental model behind:

  • Query Planning
  • Query optimization
  • Index usage
  • Execution strategies

Stay tuned for more.