Editing Relational algebra (section)

=== Semijoin ===

The left semijoin (⋉ and ⋊) is a joining similar to the natural join and written as <math alt="𝑅 ⋉ 𝑆">R \ltimes S</math> where <math alt="𝑅">R</math> and <math alt="𝑆">S</math> are [[relation (database)|relation]]s.{{efn|In [[Unicode]], the ltimes symbol is ⋉ (U+22C9). The rtimes symbol is ⋊ (U+22CA)}} The result is the set of all tuples in <math alt="𝑅">R</math> for which there is a tuple in <math alt="𝑆">S</math> that is equal on their common attribute names. The difference from a natural join is that other columns of <math alt="𝑆">S</math> do not appear. For example, consider the tables ''Employee'' and ''Dept'' and their semijoin:{{citation needed|date=April 2022}}

{{col-begin|width=auto; margin:0.5em auto}}
{{col-break}}
{| class="wikitable"
 |+           ''Employee''
 |-
 ! Name   !! EmpId !! DeptName
 |-
 | Harry  || 3415  || Finance
 |-
 | Sally  || 2241  || Sales
 |-
 | George || 3401  || Finance
 |-
 | Harriet || 2202 || Production
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+           ''Dept''
 |-
 ! DeptName   !! Manager
 |-
 | Sales  || Sally  
 |-
 | Production|| Harriet 
|}
{{col-break|gap=2em}}
{| class="wikitable"
 |+  ''Employee'' ⋉ ''Dept''
 |-
 ! Name   !! EmpId !!  DeptName
 |-
 | Sally  || 2241   || Sales
 |-
 | Harriet || 2202 || Production
|}
{{col-end}}

More formally the semantics of the semijoin can be defined as
follows:

<math alt="𝑅 ⋉ 𝑆 = { 𝑡 : 𝑡 ∈ 𝑅 ∧  ∃ 𝑠 ∈ 𝑆(Fun(𝑡 ∩ 𝑠))}">R \ltimes S = \{ t : t \in R \land \exists s \in S(\operatorname{Fun}(t \cup s)) \}</math>

where <math alt="Fun(𝑟)">\operatorname{Fun}(r)</math> is as in the definition of natural join.

The semijoin can be simulated using the natural join as follows. If <math alt="a<sub>1</sub>, ..., a<sub>n</sub>">a_1, \ldots, a_n</math> are the attribute names of <math alt="𝑅">R</math>, then

<math alt="𝑅 ⋉ 𝑆 = Π_{a<sub>1</sub>, ..., a<sub>n</sub>} (𝑅 ⋈ 𝑆)">R \ltimes S = \Pi_{a_1, \ldots, a_n}(R \bowtie S).</math>

Since we can simulate the natural join with the basic operators it follows that this also holds for the semijoin.

In Codd's 1970 paper, semijoin is called restriction.<ref name="Codd1970" />