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