Correspondence between relational algebra (Relation Algebra) and SQL statements

The execution of SQL statements is generally translated into relational algebra and then executed (which can effectively improve execution speed), so we need to

Understand the correspondence between relational algebra and SQL statements.

Just like high school algebra consists of +-*/and numbers, relational algebra is composed of the union, intersection, join operators, and relationship instances

Composed of.

There are five base operators for relational algebra, and these five base operators can derive other combinations of operator characters. Each of them is:

Select (Σ, selection), projection (π, projection), cross multiply (x, cross-product),

Poor (-, set-difference) and (Gamma, Union)

Their correspondence to the SQL statement is:

The selection (Σ, selection) corresponds to where in the SQL statement, which indicates that rows that meet certain criteria are selected.

such as: Σrating>8 (S2) is equivalent to select * from S2 where rating>8;

The projection (π, projection) is equivalent to the select in the SQL statement ... A distinct that represents which columns are selected. Note: The projection is going to be heavy!

such as: Πsname,rating (Σrating>8 (S2)) is equivalent to select Sname, rating from S2 where rating>8;

The fork multiplication (x, cross-product) is equivalent to the from in the SQL statement, which means that the sum of the elements on each side of the set is exhaustive

such as: AxB equivalent to select * from A, B; Note: Two sets cannot have a re-name when cross-multiply

Poor (-, set-difference) r-s returns all tuples in R instead of s

and (Gamma, Union) rυs returns all tuples contained in R or in S

Note: Both the correlation and the difference require that the two relationship instances be compatible. and compatible means: 1. The two relationship instance fields have the same number 2. corresponding field type, same value range

Synthetic operators:

Synthetic operators are derived from the combination of base operators, which is a shorthand notation.

(∩, intersection) R∩s returns a tuple that is both in R and in S.

A natural connection (?, natural join) is equivalent to a cross-multiplication, and then a relationship instance with a common property. If there is no public property, then the result is a cross-multiply

In addition to (÷, Division) R÷s, return R contains a relationship instance that differs from the columns in the s total but other columns.

For example:

The upper and lower blue and golden portions of R contain S, while the middle orange part contains only S, and the white part does not contain s at all, so r÷s=

Correspondence between relational algebra (Relation Algebra) and SQL statements