In Relational Algebra (RA), various operations can be performed on relations (tables) to
manipulate data in a structured way. Here’s a breakdown of the common operations you’ve
mentioned — Cartesian Product, Projection, Union, Intersection, and Difference — along with
their SQL equivalents and examples:
1. Cartesian Product (×)
The Cartesian Product (also called Cross Product) combines every row from one relation with
every row of another relation. This results in a relation with all possible combinations of rows
between the two tables. It’s the basis for join operations but is less frequently used directly.
Relational Algebra:
• A × B: The Cartesian product of relations A and B.
SQL Equivalent:
• In SQL, the Cartesian Product can be achieved by simply joining two tables without a JOIN
condition (which will lead to a cross join).
SELECT *
FROM table1, table2;
Example:
Consider two tables students and courses:
-- Students table
| student_id | student_name |
|------------|--------------|
|1 | Alice |
|2 | Bob |
-- Courses table
| course_id | course_name |
|-----------|-------------|
| 101 | Math |
| 102 | English |
The Cartesian Product of these tables will look like:
-- Result of Cartesian Product
| student_id | student_name | course_id | course_name |
|------------|--------------|-----------|-------------|
|1 | Alice | 101 | Math |
|1 | Alice | 102 | English |
|2 | Bob | 101 | Math |
|2 | Bob | 102 | English |
3. Union (∪)
Union combines the rows from two relations, excluding duplicates. Both relations involved must
have the same number of attributes with compatible data types.
Relational Algebra:
• R ∪ S: The union of relations R and S returns all distinct rows from both relations.
SQL Equivalent:
• In SQL, Union is performed using the UNION operator (which automatically removes
duplicates). To include duplicates, you can use UNION ALL.
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;
Example:
Given two tables employees and contractors with the same structure:
-- Employees table
| id | name |
|----|-------|
| 1 | Alice |
| 2 | Bob |
-- Contractors table
| id | name |
|----|-------|
| 3 | Carol |
| 2 | Bob |
The Union of these tables would return all distinct rows from both:
SELECT id, name
FROM employees
UNION
SELECT id, name
FROM contractors;
-- Result:
| id | name |
|----|-------|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
4. Intersection (∩)
Intersection returns only the rows that are present in both relations. The relations must have the
same number of attributes and compatible data types.
Relational Algebra:
• R ∩ S: The intersection of relations R and S returns only the rows that appear in both R and
S.
SQL Equivalent:
• In SQL, Intersection can be implemented by using the INTERSECT operator.
SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;
Example:
Using the same employees and contractors tables:
SELECT id, name
FROM employees
INTERSECT
SELECT id, name
FROM contractors;
-- Result:
| id | name |
|----|-------|
| 2 | Bob |
In this case, only Bob is present in both the employees and contractors tables.
5. Difference (−)
Difference returns the rows that are present in one relation but not in another. Both relations must
have the same number of attributes with compatible data types.
Relational Algebra:
• R − S: The difference between relations R and S returns the rows that are in R but not in S.
SQL Equivalent:
• In SQL, Difference can be implemented using the EXCEPT operator (some databases may
use MINUS instead).
SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;
xample:
Using the same employees and contractors tables:
SELECT id, name
FROM employees
EXCEPT
SELECT id, name
FROM contractors;
-- Result:
| id | name |
|----|-------|
| 1 | Alice |
In this case, Alice is the row in employees that is not present in contractors, so she is included in
the result.
