Contact Us
  
 
 
    SearchSearch   RegisterRegister   Log inLog in 

Example of join in mysql

Example of join in mysql

 
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems
View previous topic :: View next topic  
Author Message
admin
Site Admin


Joined: 24 Aug 2006
Posts: 30

PostPosted: Sun Dec 16, 2007 9:39 am    Post subject: Example of join in mysql Reply with quote




Mysql database give powerful thing that is mysql join.
Join mean joining data of two or more than two tables in mysql database.
SQL defines two basic joins

OUTER Join all records from one table and matched records from other table
1. left join
2. right join
INNER Join matched records from both table only
1. inner join


1. Left Join in MySql

Left join joins data from left to right side
This can be shown by example
Table 1 CityMaster

Code:
SELECT iCityID, iStateID, sCityName FROM citymaster c


Code:

+---------+----------+------------+
| iCityID | iStateID | sCityName  |
+---------+----------+------------+
|       3 |        5 | Chandigarh |
|       4 |        5 | Hissar     |
|       5 |        8 | Jabalpur   |
|       6 |        6 | jammu      |
+---------+----------+------------+


Table 2 StateMaster

Code:
SELECT iStateID, sStateName FROM statemaster s



Code:

+----------+------------------+
| iStateID | sStateName       |
+----------+------------------+
|        2 | Haryana          |
|        7 | Himachal Pradesh |
|        6 | Jammu & Kashmir  |
|        3 | New Delhi        |
|        5 | Punjab           |
|        8 | Uttar Pradesh    |
|        9 | west bengal      |
+----------+------------------+


In the left join key of iStateID from cityMaster table match with StateMaster table, mean all rows (left side) with match rows of right side table(StateMaster)

Left join query e.g

Code:

SELECT c.iCityID, c.iStateID, c.sCityName,s.sStateName  FROM citymaster c left join statemaster s on s.iStateID=c.iStateID


alway try to use alias name for table and field name otherwise it will show error of
Code:
field list is ambiguous


Out of above query will be

Code:

+---------+----------+------------+-----------------+
| iCityID | iStateID | sCityName  | sStateName      |
+---------+----------+------------+-----------------+
|       3 |        5 | Chandigarh | Punjab          |
|       4 |        5 | Hissar     | Punjab          |
|       5 |        8 | Jabalpur   | Uttar Pradesh   |
|       6 |        6 | jammu      | Jammu & Kashmir |
+---------+----------+------------+-----------------+


All rows from left table with matching rows from right side


2. Right Join in MySql

Right join mysql almost same as left join, difference is only, it takes all rows of right side table and match with left side rows.

Take a same example of above
Table 1 CityMaster

Code:
SELECT iCityID, iStateID, sCityName FROM citymaster c


Code:

+---------+----------+------------+
| iCityID | iStateID | sCityName  |
+---------+----------+------------+
|       3 |        5 | Chandigarh |
|       4 |        5 | Hissar     |
|       5 |        8 | Jabalpur   |
|       6 |        6 | jammu      |
+---------+----------+------------+


Table 2 StateMaster

Code:
SELECT iStateID, sStateName FROM statemaster s



Code:

+----------+------------------+
| iStateID | sStateName       |
+----------+------------------+
|        2 | Haryana          |
|        7 | Himachal Pradesh |
|        6 | Jammu & Kashmir  |
|        3 | New Delhi        |
|        5 | Punjab           |
|        8 | Uttar Pradesh    |
|        9 | west bengal      |
+----------+------------------+


In this, we will use right join. iStateID will match with left side of table citymater with iStateID key. If matching value is not found, it will return null in the column.

Code:

SELECT c.iCityID, c.iStateID, c.sCityName,s.sStateName  FROM citymaster c right join statemaster s on s.iStateID=c.iStateID


Output of right join for above query like..

Code:

+---------+----------+------------+------------------+
| iCityID | iStateID | sCityName  | sStateName       |
+---------+----------+------------+------------------+
|    NULL |     NULL | NULL       | Haryana          |
|    NULL |     NULL | NULL       | Himachal Pradesh |
|       6 |        6 | jammu      | Jammu & Kashmir  |
|    NULL |     NULL | NULL       | New Delhi        |
|       3 |        5 | Chandigarh | Punjab           |
|       4 |        5 | Hissar     | Punjab           |
|       5 |        8 | Jabalpur   | Uttar Pradesh    |
|    NULL |     NULL | NULL       | west bengal      |
+---------+----------+------------+------------------+


3. INNER Join in SQL

Inner join take only matching records from both tables. We have to match on particular column; this column should be same in nature.
Lets see in example

Table 1 CityMaster

Code:
SELECT iCityID, iStateID, sCityName FROM citymaster c


Code:

+---------+----------+------------+
| iCityID | iStateID | sCityName  |
+---------+----------+------------+
|       3 |        5 | Chandigarh |
|       4 |        5 | Hissar     |
|       5 |        8 | Jabalpur   |
|       6 |        6 | jammu      |
+---------+----------+------------+


Table 2 StateMaster

Code:
SELECT iStateID, sStateName FROM statemaster s



Code:

+----------+------------------+
| iStateID | sStateName       |
+----------+------------------+
|        2 | Haryana          |
|        7 | Himachal Pradesh |
|        6 | Jammu & Kashmir  |
|        3 | New Delhi        |
|        5 | Punjab           |
|        8 | Uttar Pradesh    |
|        9 | west bengal      |
+----------+------------------+


In the inner join key of iStateID from cityMaster table match with StateMaster table, if iStateID key of cityMaster will match with iStateID key of StateMaster return these rows.

Code:

SELECT c.iCityID, c.iStateID, c.sCityName,s.sStateName  FROM citymaster c inner join statemaster s on s.iStateID=c.iStateID


Output of inner join for above query like..

Code:

+---------+----------+------------+-----------------+
| iCityID | iStateID | sCityName  | sStateName      |
+---------+----------+------------+-----------------+
|       3 |        5 | Chandigarh | Punjab          |
|       4 |        5 | Hissar     | Punjab          |
|       5 |        8 | Jabalpur   | Uttar Pradesh   |
|       6 |        6 | jammu      | Jammu & Kashmir |
+---------+----------+------------+-----------------+
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems All times are GMT
Page 1 of 1

 
Jump to:  
  Quick Post a Reply Topic
     
  Name (required)  
     
  Email Address (will not be published) (required)  
     
  Subject of topic (required)  
     
   
     
   
     
  Advertisement  
     
 
     


  More Links  
 
JSP Introduction
JSP Setup
Simple JSP Example
JSP Variable
Array Declaration in JSP
JSP Forms and User Input
Form Validation
JSP Cookies
JSP Session Object
Loop, content collection, iterator, conditional check in JSP
JSP Application Object
JSP Include
JSP Response Object
JSP Request Object
JSP Error Handling
JSP File Handling Object
JSP Action Tag
JSP Implicit Objects
JSP Declaration
JSP Directive
JDBC Introduction
JDBC Driver
JDBC Connection
JDBC Prepared/Statement
JDBC ResultSet
JDBC Close Connection object
JDBC Insert
JDBC Display Records
JDBC Update
JDBC Delete
JDBC Sorting
JDBC Query
JDBC Exception
JDBC Add Batch
JDBC Transaction/RollBack