Friday, April 23, 2021
More

    Sql Join

    Must Read

    Programmerhttp://www.improgrammer.net
    We started this site to inspire young minds to motivate and encourage them towards Programming Language. In this site you will get programming tutorials, tech, programming facts, programming fun and programming blogs.

    Join is used to retrieve data from two or more database tables. Using SQL Joins we can combine records from two or more tables in a database. There are different kinds of join in SQL. The joins are performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. Joins can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc. Let’s see one Example to understand Join.

    Sql Join
    Sql Join

    Table 1 − STUDENT Table

    Id Name Batch Subject Marks
    1 Rahul A Maths 99
    2 Kirti B Science 87
    3 James C English 99
    4 Uday A English 85
    5 Angel B Science 83

     

    Table 2 − RESULT Table

    RID STUDENT_ID Grade
    102 3 A
    101 2 B
    103 4 B

     

    let us join these two tables in our SELECT statement

    SELECT ID, NAME, Batch, Grade
    FROM STUDENT, RESULT
    WHERE STUDENT.ID = RESULT.STUDENT_ID;

    This would produce the following result.

    Id Name Batch Grade
    3 James C A
    2 Kirti B B
    4 Uday A B

     

    Different Types of SQL JOINs

    • Inner Join – This Join returns rows when there is a match in both tables.
    • Outer Join – There are three types of outer join.
      1. Left Join − This Join returns all rows from the left table, even if there are no matches in the right table.
      2. Right Join − This Join returns all rows from the right table, even if there are no matches in the left table.
      3. Full Join − This Join returns rows when there is a match in one of the tables.
    • Self Join − This Join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
    • Cartesian Join − This Join returns the Cartesian product of the sets of records from the two or more joined tables.

    Example of Joins

    Consider two table College and Hostel for understands types of join and difference between joins.

    Table 1: College

    College
    Name Id Department
    Jons A1 Computer
    Thomas A2 Computer
    Greek A3 IT

     

    Table 2: Hostel

    Hostel
    Name Hostel_Name Room No
    Thomas Hilltop 101
    Greek Academic 102
    Luis Art 103

     

    Output of Inner Join

    inner join

     

    Inner Join
    Name Id Department Hostel_Name Room No
    Thomas A2 Computer Hilltop 101
    Greek A3 IT Academic 102

     

    Output of Left Outer Join

    Left join

    Left Outer Join
    Name Id Department Hostel_Name Room No
    Jons A1 Computer Null Null
    Thomas A2 Computer Hilltop 101
    Greek A3 IT Academic 102

     

    Output of Right Outer Join

    Right Join

    Right Outer Join
    Name Id Department Hostel_Name Room No
    Thomas A2 Computer Hilltop 101
    Greek A3 IT Academic 102
    Luis Null Null Art 103

     

    Output of Full Outer Join

    Full outer join

    Full Outer Join
    Jons A1 Computer Null Null
    Thomas A2 Computer Hilltop 101
    Greek A3 IT Academic 102
    Luis Null Null Art 103

    In short SQL join is used to improve data integrity and reduce the number of queries requirements. Using join easily combine data from multiple tables and get data as per requirements from different tables.

    See More: More Popular NoSQL Database

    Latest Articles

    More Recipes Like This