Relational Algebra

Relational algebra is the math under SQL. Each operator takes one or two tables and returns a *new* table, so you can chain them to express any query. Four do most of the work: selection keeps rows, projection keeps columns, join stitches tables together, and the set operators combine same-shaped tables.

The core operators

1. 1Selection σ — σ_condition(R) keeps only the rows passing a test. Same columns, fewer rows. This is SQL's WHERE.
2. 2Projection π — π_cols(R) keeps only the listed columns and drops the rest, removing duplicate rows that result. This is SQL's SELECT list.
3. 3Join ⋈ — R ⋈ S pairs rows of R and S that agree on their shared column(s). The natural join equals SQL's inner join.
4. 4Set ops ∪ − ∩ — union, difference, and intersection combine two *union-compatible* tables (same columns, same types).

Sample tables

Employee(eid, name, dept_id, salary)
Department(dept_id, dept_name)

Expressions and their SQL

σ_{salary > 50000}(Employee)
  = SELECT * FROM Employee WHERE salary > 50000;

π_{name, salary}(Employee)
  = SELECT DISTINCT name, salary FROM Employee;

Employee ⋈ Department
  = SELECT * FROM Employee NATURAL JOIN Department;   -- match on dept_id

Composing a real query

"The names of employees in the Sales department" chains all three core operators — join, then select, then project:

π_{name}( σ_{dept_name = 'Sales'}( Employee ⋈ Department ) )

= SELECT e.name
  FROM Employee e JOIN Department d ON e.dept_id = d.dept_id
  WHERE d.dept_name = 'Sales';