Relational algebra: operations on tables, the way arithmetic is operations on numbers
Before SQL, there is relational algebra — a small set of formal operations, each taking one or more relations and producing a new relation, that every SQL query is secretly built from.
After this lesson
You should be able to
- Distinguish selection (filtering rows) from projection (choosing columns).
- Explain what a join does and why it needs a matching condition between two relations.
Selection picks rows, projection picks columns — never confuse the two
Selection (written with the Greek letter sigma) filters a relation down to the tuples that satisfy a condition — "only Students where branch equals CSE" — keeping all columns but fewer rows. Projection (written with the Greek letter pi) keeps only specified columns from a relation, across every row — "only name and roll_number, from every Student" — fewer columns but all matching rows. These are exactly the two independent axes of a table: which rows, and which columns, and every query needs to reason about both separately.
Set operations — union, intersection, and set difference — combine two relations that share the same structure (same columns, same types). Union of CSE_Students and ECE_Students gives every student in either branch. Intersection would give students appearing in both, which for disjoint branches is naturally empty. Set difference — Students minus GraduatedStudents — gives students not yet graduated.
Joins: connecting two relations through a shared value
A join combines rows from two relations based on a matching condition, most commonly a foreign key matching a primary key — Student join Department where Student.dept_id equals Department.dept_id produces one row per student, with that student's department details attached. This is the algebra-level version of following the foreign-key link from the previous lesson: relational algebra makes explicit, as one operation, the act of walking a reference from one table to another.
Renaming lets a relation or attribute be referred to under a different name within a query — necessary when the same relation must be used twice in one query, such as finding pairs of students in the same branch, which needs two copies of Student distinguished from each other. Division answers questions of the form "find X that relate to every Y" — for example, "find students enrolled in every course a department offers" — a genuinely harder operation than a simple filter, built from the simpler operations above.
Try it yourself
Using Book(isbn, title, author) and Borrows(isbn, membership_id, due_date), write in plain words the selection, projection, and join needed to answer: "what are the titles of books currently borrowed by member M101?"
Need a hint?
Selection narrows Borrows down to rows where membership_id equals M101. The join connects those rows to Book using the shared isbn. Projection then keeps only the title column.
Check the worked solution
First, select Borrows rows where membership_id equals M101. Then, join that result with Book on isbn to attach each borrowed book's details. Finally, project just the title column from the joined result. This three-step chain — select, join, project — is exactly the pattern behind most everyday SQL queries.
Quick check
Why can a join not simply combine two relations without any matching condition?
Why this lesson exists
Syllabus mapping
Relational Algebra, Selection and projection, set operations · renaming, Joins, Division, Examples of Algebra queries
Maps to course outcomes CO1, CO2.