We will get a concise overview of seven common relational algebra operations: Select, Projection, Union, Intersection, Difference, Product, and Join, followed by six additional operations, including extensions used to describe SQL queries.
This article is based on the lessons I teach about DBMS at Fondazione PIN in Prato in 2025.
Take a look at the Relational Algebra Cheat Sheet.
If you want to practice SQL interactively after studying these operators, try Seagull, my browser-based SQL exercise project for database courses.
Common Relational Algebra Operations#
We can consider relational algebra to be a procedural language, meaning it describes the procedure to follow in order to obtain a result. Here are seven common operations for manipulating and querying relations. They are not all independent primitives: intersection and join can be derived from other operations.
Each operator takes one or more relations as input and produces a new relation as output, allowing us to chain operations together.
| σ | Select |
| π | Projection |
| ∪ | Union |
| ∩ | Intersection |
| − | Set Difference |
| × | Cartesian Product |
| ⨝ | Join |
Select (σ)#
Select (σ) filters the tuples of a relation based on a condition.
Example: σ (age > 30) (People) - Selects only the people whose age is greater than 30.
Projection (π)#
Projection (π) selects specific attributes of a relation and eliminates duplicate tuples. Its SQL counterpart is SELECT DISTINCT over the selected columns.
Example: π (FirstName, LastName) (People) - Returns only the first names and last names from the People relation.
Union (∪)#
Union (∪) produces a relation that combines two compatible relations (with the same schema), removing duplicates.
Example: Students ∪ Teachers - Merges students and teachers into a single relation.
Intersection (∩)#
Returns only the tuples present in both relations.
Example: Employees ∩ Managers - Finds those who are both employees and managers.
Set Difference (−)#
Returns the tuples that are in one relation but not in the other.
Example: Students − Graduates - Students who have not graduated.
Cartesian Product (×)#
Combines every tuple of one relation with every tuple of another.
Example: Students × Courses - Associates every student with every possible course.
Join (⨝)#
The natural join shown here combines tuples that agree on all attributes shared by the two relations.
Example: with Students(student_id, name) and Registrations(student_id, course_id), Students ⨝ Registrations joins students and registrations on their only common attribute, student_id.
Other Operations#
In addition to the seven operations above, here are six more operations, some from classical algebra and others from extensions:
Rename (ρ)#
Changes the name of a relation or its attributes.
Example: ρ (New_Students, Name → Student) (Students) - Rename the relation to New_Students and the attribute Name to Student.
Assignment (←)#
Stores the result of a relational expression in a temporary variable.
Example: Temp ← σ (age > 30) (People) - Store in Temp the people older than 30.
Duplicate Elimination (δ)#
In bag (multiset) algebra, this removes duplicate tuples. Classical relations already contain no duplicates, so they do not need this operator.
Example: δ (π (Name) (Students)) - Returns only unique student names when projection uses bag semantics.
Aggregation (G)#
Calculates aggregate values such as COUNT, SUM, AVG, MIN, MAX on groups of tuples.
Example: G (Class) COUNT(*) (Students) - Counts the number of students for each class.
Sorting (τ)#
This ordering extension returns tuples in a specified order. A classical relation has no ordering.
Example: τ (LastName ASC) (Students) - Sorts students by last name in ascending order.
Division (÷)#
Finds the elements of one relation that are associated with all the elements of another relation.
Example: Enrollments(student_id, course_id) ÷ Required_Courses(course_id) - Returns the student_id values of students enrolled in every required course.
Relational Algebra and RDBMS#
Relational algebra describes logical operations, while a DBMS chooses physical algorithms to execute them. For example, consider these two expressions for an inner join, assuming age belongs only to S:
σ (age > 30) (R ⨝ S)(R ⨝ (σ (age > 30) (S)))
We can do a join between two relations and then filter for age > 30, or filter relation S for age > 30 first and then do the join.
The expressions are logically equivalent, but evaluating them in the order written can produce different-sized intermediate results. Which approach could reduce the work, and why?
σ (age > 30) (R ⨝ S)(R ⨝ (σ (age > 30) (S)))
Filtering first can reduce intermediate results; a SQL optimizer may produce the same plan for both formulations. Actual performance depends on the physical plan and the data, not just the order in which the expressions are written.
The relational model is independent of the implementation of the query language. SQL is the de facto standard, although it exists in various dialects.
Continue studying DBMS fundamentals#
If this article helped, the most relevant next steps are:
- Relational Data Modeling for tables, keys, and relationships;
- Database Normalization for anomalies and normal forms;
- History of Databases and SQL for the context behind the relational model;
- SQLite Indexes Explained to connect algebraic operations with real query performance.