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 -
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 -
- Inner Join or Join
- Left Join
- Right Join
- 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/////////////////////////////
Query output printshot has been presented below:
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
- 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/////////////////////////////
Query output printshot has been presented below:
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
- 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/////////////////////////////
Query output printshot has been presented below:
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
0 comments:
Post a Comment