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

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - About

An equi-join also known as simple join link tables based on the equality between the values in the column of one table and the values in the column of another. Because the same column is present in both tables, the join synchronizes the two 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.

Advertising

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.

Advertising

4.3 - Example 3

5 - Natural Join

A natural join clause permit you to avoid to specify the join condition. The resultant join is an inner join performed on the primary/foreign key relationship.

5.1 - Example

ops$tkyte%ORA10GR2> CREATE TABLE t1 ( x INT PRIMARY KEY, y INT, z INT );
 
TABLE created.
 
ops$tkyte%ORA10GR2> CREATE TABLE t2 ( x INT PRIMARY KEY, y REFERENCES t1, a INT );
 
TABLE created.

Obviously, to join t1 to t2, you would use t1.x = t2.y, the primary/foreign key..

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO t1 VALUES ( 1, 1 , 100);
 
1 ROW created.
 
ops$tkyte%ORA10GR2> INSERT INTO t1 VALUES ( 2, 2 , 200);
 
1 ROW created.
 
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( 1,  1 , 300);
 
1 ROW created.
 
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( -2, 1 , 400);
 
1 ROW created.
 
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( 2,  2 , 500);
 
1 ROW created.
 
ops$tkyte%ORA10GR2> INSERT INTO t2 VALUES ( -4, 2 , 600);
 
1 ROW created.
 
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT * FROM t1 NATURAL JOIN t2;
 
         X          Y          Z          A
---------- ---------- ---------- ----------
         1          1        100        300
         2          2        200        500

Hmmm…..

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT * FROM t1 JOIN t2 ON (t1.x = t2.y);
 
         X          Y          Z          X          Y          A
---------- ---------- ---------- ---------- ---------- ----------
         1          1        100          1          1        300
         1          1        100         -2          1        400
         2          2        200          2          2        500
         2          2        200         -4          2        600

oh, there we go

6 - Documentation / Reference

Advertising
data/type/relation/sql/equi_join.txt · Last modified: 2017/09/13 21:21 by gerardnico