Quick Contact

    DBMS Joins

    DBMS Joins can be simply represented as the connecting or merging the related rows from two different relations into an individual type. It is represented by Bowtie (⋈). It is necessarily a cartesian product followed by a selection criterion.

    Syntax of Join Operation

    A⋈< Join Condition>B

    Example

    Consider the following relation

    EMPLOYEE
    EMP-ID EMP-NAME
    111 Rohan
    112 Vishal
    113 Sumit
    SALARY
    EMP-ID SALARY
    111 40,000
    112 30,000
    113 20,000

    Operations: (EMPLOYEE⋈SALARY)

    Results
    EMP-ID EMP-NAME SALARY
    111 Rohan 40,000
    112 Vishal 30,000
    113 Sumit 20,000
    Types of Join Operations

    There are basically two types of joins

    1. Inner Joins
    2. Outer Joins
    DBMS Joins
    Inner Joins

    These joins are the one that has the rows that satisfy some conditions and rest are rejected.

    Types of Inner Joins

    There are following types of Inner Joins

    Theta Join(θ)

    The general method of the join operation is known as a Theta join. It is expressed by the symbol(θ).

    Syntax of Theta Join(θ)

    A⋈θ B

    Theta join can operate any positions in the selection test.

    Example

    Consider the following table

    Table X
    Column1 Column2
    1 1
    1 2
    Table Y
    Column1 Column2
    1 1
    1 3

    Operations:A⋈X.column2>Y.column2 (B)

    Result
    Column1 Column2
    1 2
    EQUI Join(⋈)

    It is also known as Inner Join. It is denoted by (=) equal sign and shows all the data which are frequent between two relations. If no data is chosen, return null. The query of the inner join measures each row of Relation1 with each row of Relation2 to find all combinations of rows that appease the join condition. When the join condition is fulfilled, attribute values for each doubled combination of rows of X and Y are mixed into a resultant row.

    DBMS Joins
    Syntax of EQUI Join(⋈)

    Relation1.attribute_name=Relation2.attribute_name

    Example: Consider the following relations

    Student
    Student-id Name Age
    121 Nisha 19
    122 Meera 20
    123 Rahul 17
    Course
    Couse-id Student-id Course-Name
    221 121 B.tech
    222 122 MCA
    223 123 MCA

    Query: Display the name of the student who studies in each course.

    Name (σ(Student.Student-id=Course.Student-id)(student x course))

    Output
    Name
    Nisha
    Meera
    Rahul
    Natural Join(⋈)

    Natural join is the set of all mergers of rows in R and S that are equal to their common attributes. It is represented by (⋈) symbol.

    Example

    Consider the table EMPLOYEE and SALARY.

    EMPLOYEE
    EMP-ID EMP-NAME
    111 Rohan
    112 Vishal
    113 Sumit
    SALARY
    EMP-ID SALARY
    111 40,000
    112 30,000
    113 20,000

    Query: Get the employee name with their salary name.

    EMP-NAME,SALARY (EMPLOYEE⋈SALARY)

    Output
    EMP-NAME SALARY
    Rohan 40,000
    Vishal 30,000
    Sumit 20,000
    Conditional Join(⋈C)

    Conditional join is used when we need to join two or more relations based on some conditions. Example: select students whose Roll-No is higher than EMP-No of employees

    Syntax of Conditional Join(⋈C)
    Syntax of Conditional Join(⋈C)

    STUDENT⋈C STUDENT.Roll-No>EMPLOYEE.Emp-No EMPLOYEE

    Example

    Consider the following relation

    STUDENT
    Roll-No Name Age
    111 Mahesh 20
    112 Ashutosh 18
    113 Neha 18
    EMPLOYEE
    EMP-No Name Age
    111 Mahesh 20
    112 Ramesh 22
    113 Rajesh 23

    Query: Get information on those employees and students whose student roll no is more than employee no.

    σ(STUDENT.ROLL-NO>EMPLOYEE.EMP-NO)(STUDENT X EMPLOYEE)

    Output
    Roll-No Name Age Emp-No Name Age
    112 Ashutosh 18 111 Mahesh 20
    113 Neha 18 111 Mahesh 20
    Outer Join

    Outer Join shows all data from both the participating relation which satisfy the join condition along with rows which do not satisfy the join condition.

    Types of Outer Join

    There are three types of an outer join

    Left Outer Join (ADBMS Joins B)

    The left outer join is denoted by DBMS Joins symbol and shows all the rows from the left relation and the duplicate rows from the right relation. And if there is exists no duplicate data in the right relation, it shows the null value.

    DBMS Joins
    Syntax of Left Outer Join

    A DBMS Joins B

    Example

    Consider the following relation

    Customer
    Name Address Age
    Amar Sec-12 Noida
    Rohan Sec-16 Delhi
    Rajesh/td> Sec-21 Faridabad
    Account
    Name Account Branch
    Amar A-101 Noida
    Kumar A-201 Delhi
    Rajesh/td> A-301 Delhi

    Operations: (Customer DBMS Joins Account)

    Output
    Name Address City Account Branch
    Amar Sec-12 Noida A-101 Noida
    Rohan Sec-16 Delhi Null Null
    Rajesh Sec-21 Faridabad A-301 Delhi
    Right Outer Join (A DBMS Joins B)

    The right outer join is denoted by DBMS Joins symbol and shows all the rows from the right relation and the duplicate rows from the left relation. If there is exists no duplicate data on the left relation, it shows the NULL value.

    DBMS Joins
    Syntax of Right Outer Join

    A DBMS Joins B

    Example

    Consider the following relation

    Customer
    Name Address Age
    Amar Sec-12 Noida
    Rohan Sec-16 Delhi
    Rajesh/td> Sec-21 Faridabad
    Account
    Name Account Branch
    Amar A-101 Noida
    Kumar A-201 Delhi
    Rajesh/td> A-301 Delhi

    Operations: (Customer DBMS Joins Account)

    Output
    Name Address City Account Branch
    Amar Sec-12 Noida A-101 Noida
    Kumar Null Null A-201 Delhi
    Rajesh Sec-21 Faridabad A-301 Delhi
    Full Outer Join (A DBMS Joins B)

    The full outer join is denoted by DBMS Joins symbol and merge the outcome of both right and left outer joins and returns all matching or un-matching data from both the relation.

    DBMS Joins
    Syntax of Full Outer Join

    A DBMS Joins B

    Example

    Consider the following relation

    Customer
    Name Address Age
    Amar Sec-12 Noida
    Rohan Sec-16 Delhi
    Rajesh/td> Sec-21 Faridabad
    Account
    Name Account Branch
    Amar A-101 Noida
    Kumar A-201 Delhi
    Rajesh/td> A-301 Delhi

    Operations: (Customer DBMS Joins Account)

    Output
    Name Address City Account Branch
    Amar Sec-12 Noida A-101 Noida
    Rohan Sec-16 Delhi Null Null
    Rajesh Sec-21 Faridabad A-301 Delhi
    Kumar Null Null A-201 Delhi

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.