What is joins in DBMS ?

  • DBMS join is a binary operation that allows to combine join product and selecting in one single statement.
  • If data needs to be retrived 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:

  • To return rows from both tables based on a satisfying condition we can use inner join.
  • It is default join type and is the most widely used join operation.
  • Inner join can classified into three subtypes
    • Theta Join
    • Natural Join
    • Equi Join

Theta Join:

  • It is represented by symbol θ which allows us join two tables based on condition.
  • This join works for all comparison operators
  • This join is also called as 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 same name and domain in this join.
  • In this join,two relations should have atleast one common attributes 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 equivalence condition, EQUI join can be used.
  • This join is the most difficult join to be done on an RDBMS because RDBMS have essential performance problems.

Outer Join:

  • This join returns all the attributes of both the tables depending on certain conditions.
  • If there is no attributes present for any one of the tables it returns NULL irrespective of 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 for it 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

Leave a Reply

Your email address will not be published.

You May Also Like