SQL - (Equi|Simple|Inner|Natural) Join

1 - 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.

3 - 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) 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.

4 - Example

4.1 - Example 1

4.2 - 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

[email protected]>select table1.id "ID_table1", table2.id "ID_table2", table2.id_2 "ID2_table2"
  2  from table1, table2
  3  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.

4.3 - Example 3

5 - Natural Join

SQL - Natural Join - a natural join is an equi-join on the same attribute of each relation.

6 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap