About
Relational Operator - Equi-joins in SQL
An equi-join also known as simple join link relation (tables, …) based on the equality between the values in the column of one relation (table, …) and the values in the column of another. Because the same column is present in both tables, the join synchronizes the two relations (tables, …)
The restriction conforms to the following syntax:
TableA.column_a = TableB.column_a
In a normalized database the columns used in an equi-join are usually the primary key from one table and the foreign key in the other.
Syntax
Equality Notation
select *
from R1 JOIN R2
on R1.A = R2.A
Cross product notation
select *
from R1, R2
where
R1.A = R2.A
Algorithm
- Source fields from the two data sources are merged into a single record
- Only shared records are processed
- Records that are in only one data source are dropped
The result of an equi-join can be defined as the outcome of :
- first taking the Cartesian product (or SQL - Cross Join / Cartesian Product / Cross Product) of all records in the tables (combining every record in table A with every record in table B)
- then return all records which satisfy the join predicate.
Example
Example 1
Example 2
| table1 (inner set) | table2 (outer set) |
|
|---|---|---|
| Column ID | Column ID | Column ID_2 |
| A | A | |
| B | B | A |
| D | C | C |
| A | A | |
select table1.id "ID_table1", table2.id "ID_table2", table2.id_2 "ID2_table2"
from table1, table2
where table1.id = table2.id;
ID_table1 ID_table2 ID2_table2
---------- ---------- ----------
A A A
A A
B B A
The value C from the table1 and D from the table2 disappear because they don't exist in the two tables.
Example 3
Natural Join
SQL - Natural Join - a natural join is an equi-join on the same attribute of each relation.