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

Data System Architecture

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.

Tableainnertableb

Syntax

Equality Notation

select *
from R1 JOIN R2
on R1.A = R2.A

Cross product notation

cross product

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 :

Example

Example 1

Equi Join Example

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

Inner Join Result Discard

Natural Join

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

Documentation / Reference





Discover More
Obiee Foreign Key Complex Join
OBIEE 10G - Complex (Join|Operator) (Physical and Logical)

In 10g, A complex join is a metadata of sql join statement in the repository. In 11g, they doesn't exist anymore. In the business model, OBI recommends using complex logical joins instead of foreign...
Joiner Operator
OWB - Joiner operator

The Joiner operator joins multiple row sets from different sources with different cardinalities, and produces a single output row set. The Joiner operator results in a WHERE clause in the generated SQL...
Card Puncher Data Processing
Oracle Database - Effect of a filter on a outer join table

When a filter is set on a outer join table, the result is the same that if a inner/equality join was used. The procedure is as follow: The outer join sql return all values and full in the missing values...
Card Puncher Data Processing
Oracle Database - Hash joins

Hash Join The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true: A large amount of data needs to be joined. ...
Card Puncher Data Processing
R - Join Data Frame (Merge)

where: the “by” parameters specifies the join column. Default: join by common variable names The by.x and by.y parameters must be used if the matching variables have different names the “all”...
Relational Algebra Between Sql And Query Plan
Relational Operator - Equi-joins

An Equi-join is a join where the condition (predicate) is an equality. theta join An equi-join links two relations (tables, ...) on common values in a column in relation 1 with a column in relation...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...
Data System Architecture
SQL - Natural Join

A natural join is an equi-join on the same attribute name (column) of each relation (table, query, ...) It's a shorthand equi-join where the equality predicate applied to all shared attributes. ...
Data System Architecture
SQL - Outer Join

An outer join extends the result of a equi join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other...
Data System Architecture
SQL - Semijoins

A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Semijoin and...



Share this page:
Follow us:
Task Runner