Home | About | View All Posts

22 Jul 2014

Difference between Join, Left Join, Right Join in mysql

Joins are used to collect required data from two or more tables on the basis of common column in those tables of a database.

Database has been created for a "Sales Organisation" for demonstrating "JOIN" in mysql. This sample database has been given for downloading also. Download link is available at lower part of this article.

Database has two tables - 'executives' containing records of its executive, 'items' containing records of all good items that are sold by this organization.

Printshot of Table - "executives" :


Printshot of Table - "items" :




"JOIN" differentiation has been experimented on these -'executives' and 'items' tables. As we know Joins are following types -
  1. Inner Join or Join
  2. Left Join
  3. Right Join
  1. Inner Join or Join:
    It fetches records or rows when there is at least one match in two tables. It doesn't list unmatched(a field with NULL value) records or rows. Following query has been used for Inner Join -
    ////////////////////Syntax/////////////////////////////
    SELECT col_name1, col_name2, col_name3...
    FROM table1
    INNER JOIN table2
    ON table1.col_name = table2.col_name;
    ////////////////////Query/////////////////////////////
    SELECT name, itemname
    FROM executives join items
    on executives.eid = items.eid
    Query output printshot has been presented below:



  2. Left Join:
    It fetches ALL ROWS from the left table, even if there are no matches(check NULL in printshot) in the right table. Thus it lists unmatched records of the left table in addition.

    Additional record has been circled in violet color in printshot. Here 'Rajesh' has not sold any item so it has NULL value in 'itemname' column. Due to 'Left Join' query it is listed additionally with matched records of Vinit, Ahok and Abhimanyu of left table.

    Following query has been used for Left Join -
    ////////////////////Syntax/////////////////////////////
    SELECT col_name1, col_name2, col_name3...
    FROM table1
    LEFT JOIN table2
    ON table1.col_name = table2.col_name;
    ////////////////////Query/////////////////////////////
    SELECT name, itemname
    FROM executives left join items
    on executives.eid = items.eid
    Query output printshot has been presented below:



  3. Right Join:
    It fetches ALL ROWS from the right table, even if there are no matches(check NULL in printshot) in the left table. Thus it lists unmatched records of the right table in addition.

    Additional record has been circled in violet color in printshot. Here 'Laptop' item has not been sold by any executive so it has NULL value in 'name' column.

    Due to 'Right Join' query it is listed additionally with matched records of Monitor, Keyboard, Smart Phone and Mouse of right table.

    Following query has been used for Right Join -
    ////////////////////Syntax/////////////////////////////
    SELECT col_name1, col_name2, col_name3...
    FROM table1
    RIGHT JOIN table2
    ON table1.col_name = table2.col_name;
    ////////////////////Query/////////////////////////////
    SELECT name, itemname
    FROM executives right join items
    on executives.eid = items.eid
    Query output printshot has been presented below:



Tags : , ,

0 comments:

Post a Comment