📗 Definition
Relational algebra is a theoretical framework that provides a set of operations to manipulate relational data, which is data that is organized in tables or relations. These operations include selection, projection, union, intersection, difference, join, and division. Relational algebra forms the basis for the design and implementation of database systems, and it provides a way to reason about queries and data manipulations in a precise and mathematical manner. The results of relational algebra operations are also relations, which can be used as inputs for further operations or to generate reports and other outputs.
📗 Basic Relational Algebra Operations in SQL
SQL is a programming language used for managing and querying data in relational database systems. It includes a set of operations that are based on the principles of relational algebra. Here are some of the basic SQL relational algebra operations:
♦ Selection (σ)
The selection operation is used to retrieve a subset of rows from a table that satisfy a given condition. The SQL syntax for selection is:
SELECT column1, column2, ... FROM table_name WHERE condition;
For example, to select all the records from a table where the age is greater than 18:
SELECT * FROM student WHERE age > 18;
♦ Projection (₶)
The projection operation is used to retrieve a subset of columns from a table. The SQL syntax for projection is:
SELECT column1, column2, ... FROM table_name;
For example, to select only the name and age columns from a student table:
SELECT name, age FROM student;
♦ Union (U)
The union operation is used to combine the result sets of two or more SELECT statements into a single result set. The SQL syntax for union is:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
For example, to select all the records from two student tables:
SELECT * FROM student1 UNION SELECT * FROM student2;
♦ Intersection (∩)
The intersection operation is used to retrieve the common rows between two tables. The SQL syntax for intersection is:
SELECT column1, column2, ... FROM table1 INTERSECT SELECT column1, column2, ... FROM table2;
For example, to select the records that are common between two student tables:
SELECT * FROM student1 INTERSECT SELECT * FROM student2;
♦ Difference (-)
The difference operation is used to retrieve the rows that are in one table but not in another. The SQL syntax for difference is:
SELECT column1, column2, ... FROM table1 EXCEPT SELECT column1, column2, ... FROM table2;
For example, to select the records that are only in student1 table:
SELECT * FROM student1 EXCEPT SELECT * FROM student2;♦ Join
The join operation is used to combine rows from two or more tables based on a related column. The SQL syntax for join is:
SELECT table1.column1, table2.column2, ... FROM table1 JOIN table2 ON table1.column_name = table2.column_name;
For example, to select all the students with their corresponding department names:
SELECT student.name, department.name FROM student JOIN department ON student.department_id = department.id;
These are some of the basic SQL relational algebra operations that can be used to manipulate data in a relational database system.
♦ Types of Join
⭐ Inner Join
Inner join returns the rows from both tables where the join condition is satisfied. This is the most commonly used join type. The syntax for inner join is:
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
For example, if we have two tables "employees" and "departments" with the following data:
employees table:
departments table:
To join these two tables on the "department_id" column, we can use inner join as follows:
SELECT emp_name, department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;This will result in the following output:
⭐ Left Join
Left join returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, the result will be NULL. The syntax for left join is:
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
For example, to get all the employees and their corresponding departments, including those who don't belong to any department, we can use left join as follows:
SELECT emp_name, department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;This will result in the following output:
⭐ Right Join
Right join is the opposite of left join. It returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, the result will be NULL. The syntax for right join is:
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
For example, to get all the departments and their corresponding employees, including those without employees, we can use right join as follows:
SELECT emp_name, department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
This will result in the following output:
⭐ Full Outer Join
Full outer join returns all the rows from both tables, including those without matching rows. The syntax for full outer join is:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;For example, to get all the employees and their corresponding departments, including those without departments and all the departments and their corresponding employees, including those without employees, we can use full outer join as follows:
SELECT emp_name, department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
This will result in the following output:
⭐ Cross Join
Cross join is also known as a Cartesian product. It returns all possible combinations of rows from both tables. The syntax for cross join is:
SELECT * FROM table1 CROSS JOIN table2;For example, to get all possible combinations of employees and departments, we can use cross join as follows:
SELECT emp_name, department_name FROM employees CROSS JOIN departments;This will result in the following output:
These are the different types of join operations in SQL. The choice of the join type depends on the requirement and the data available in the tables.
♦ Division
The division operation is used to find all the values in one table that are related to all the values in another table. The SQL syntax for division is more complex than other operations and requires subqueries. For example, to find all the students who have enrolled in all the courses:
SELECT s.student_name FROM student s WHERE NOT EXISTS ( SELECT c.course_name FROM course c WHERE NOT EXISTS ( SELECT * FROM enrollment e WHERE e.student_id = s.student_id AND e.course_id = c.course_id ) );
This query first selects the student name from the student table, and then checks for all the courses where the student has not enrolled. If there are no such courses, the query returns the student name.
Relational algebra operations provide a powerful way to manipulate data in a relational database system, and SQL provides a convenient way to express these operations in a programming language. Understanding and mastering these operations is an essential skill for any developer or data analyst working with relational databases.
Thanks for reading, and happy coding!
Relational Algebra in DBMS: A Complete Guide -> Effective Transaction Management in Database Management Systems (DBMS)