• DBMS join is a binary operation that allows combining join products and selecting in one single statement.
  • If data needs to be retrieved from two or more tables, we can use joins to retrieve data.
  • Tables in DBMS are associated with the primary key and foreign keys.

Types of Joins

Inner Joins: Theta, Natural, EQUI

Outer Join: Left, Right, Full

Inner Joins

  • We can use an inner join to return rows from both tables based on a satisfying condition.
  • It is the default join type and is the most widely used join operation.
  • Inner join can classify into three subtypes

Theta Join

  • It is represented by the symbol θ which allows us to join two tables based on condition.
  • This join works for all comparison operators
  • This join is also called Theta Join.


A ⋈θ B
  • Theta join can use any conditions in the selection criteria.

Natural Join

  • This join does not use any comparison operators.
  • Attributes should have the same name and domain in this join.
  • In this join, two relations should have at least one common attribute between relations.
  • It performs selection forming equality on those attributes which appear in both relations by eliminating the duplicate attributes.

EQUI join

  • When Theta join uses an equivalence condition, the EQUI join can be used.
  • This join is the most difficult join to be done on an RDBMS because RDBMS have fundamental performance problems.

Outer Join

  • This join returns all the attributes of both tables depending on certain conditions.
  • If there are no attributes present for any one of the tables, it returns NULL irrespective of a row of the table attribute.
  • Outer Join is further classified as:
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join

Left Outer Join

  • If a left outer join is performed on two tables, it returns all the rows of the left table, even if there is no matching row for it in the right table.


A Left Outer Join B

Right Outer Join

  • It returns all the rows of the second table even if there is no matching row in the first table, performing Right Outer Join.


A Right Outer Join B

Full Outer Join

  • It returns all the rows of the first and second Table.


A Full Outer Join B





Categorized in: