| | |
SQL INNER join query for MySQLINNER join SQL command is mostly used to join one table to it self. The biggest advantage of doing this is to get linking information from the same table. We will try to understand this with an example. The best example of INNER join will be employee table where we will keep the employee and its manager as a single record. This way by linking to the table it self we will generate a report displaying it as two linked tables. Each record will have one additional field storing the data of the manager by keeping the employee ID and we will use M_ID ( manager ID ) to link with main employee ID. This way we will link two virtual tables generated from one main table. Here is the table. You can download /copy the sql dump file to create your own MySQL table for testing.
| Main table | Managers Table | Employee table |
| id |
name |
m_id |
| 1 |
John |
2 |
| 2 |
Greek Tor |
1 |
| 3 |
Alex John |
1 |
| 4 |
Mike tour |
1 |
| 5 |
Brain J |
3 |
| 6 |
Ronald |
3 |
| 7 |
Kin |
4 |
| 8 |
Herod |
3 |
| 9 |
Alen |
2 |
| 10 |
Ronne |
1 |
|
| id |
emp_name |
| 2 |
Greek Tor |
| 1 |
John |
| 1 |
John |
| 1 |
John |
| 3 |
Alex John |
| 3 |
Alex John |
| 4 |
Mike tour |
| 3 |
Alex John |
| 2 |
Greek Tor |
| 1 |
John |
|
| id |
emp_name |
| 1 |
John |
| 2 |
Greek Tor |
| 3 |
Alex John |
| 4 |
Mike tour |
| 5 |
Brain J |
| 6 |
Ronald |
| 7 |
Kin |
| 8 |
Herod |
| 9 |
Alen |
| 10 |
Ronne |
|
In the table you can see every record has one manager id field known as m_id. We have used the unique id of the employee in the m_id field to mark who is the manager for the employee.
To generate the manager table we have used this SQL
SELECT t1.id,t1.name as emp_name from emp as t1 INNER JOIN emp as t2 on t1.id=t2.m_id
To generate the employee table we have used this SQL
SELECT t1.id,t1.name as emp_name from emp as t1 INNER JOIN emp as t2 on t2.id=t1.m_id
Now let us use inner join to create one report on who is the manager of which employee. Check this SQL
SELECT t1.id, t1.name as emp_name, t2.name as manager FROM emp as t1 INNER JOIN emp as t2 on t2.id = t1.m_id
| id | emp_name | manager |
|---|
| 1 |
John |
Greek Tor |
| 2 |
Greek Tor |
John |
| 3 |
Alex John |
John |
| 4 |
Mike tour |
John |
| 5 |
Brain J |
Alex John |
| 6 |
Ronald |
Alex John |
| 7 |
Kin |
Mike tour |
| 8 |
Herod |
Alex John |
| 9 |
Alen |
Greek Tor |
| 10 |
Ronne |
John |
|
|
CREATE TABLE `emp` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(25) NOT NULL default '',
`m_id` int(4) NOT NULL default '0',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `emp`
--
INSERT INTO `emp` VALUES (1, 'John', 2);
INSERT INTO `emp` VALUES (2, 'Greek Tor', 1);
INSERT INTO `emp` VALUES (3, 'Alex John', 1);
INSERT INTO `emp` VALUES (4, 'Mike tour', 1);
INSERT INTO `emp` VALUES (5, 'Brain J', 3);
INSERT INTO `emp` VALUES (6, 'Ronald', 3);
INSERT INTO `emp` VALUES (7, 'Kin', 4);
INSERT INTO `emp` VALUES (8, 'Herod', 3);
INSERT INTO `emp` VALUES (9, 'Alen', 2);
INSERT INTO `emp` VALUES (10, 'Ronne', 1);
| | sajid | 29-03-2009 |
|---|
| this is very easy sum | | Lane | 11-06-2009 |
|---|
| thank you, this was very helpful. | | Niyas | 21-08-2009 |
|---|
| Your Website is Very Helpful for programmers like us, The way of giving examples was superb. | | lavanya | 27-08-2009 |
|---|
| gud website for beginners | | Ptarmigan | 11-10-2009 |
|---|
| a fave website: love anti-style style: craiglist for webbies | | Vinod | 09-11-2009 |
|---|
| Thanks a lot, i got the complete idea of Inner join. | | Karu | 24-11-2009 |
|---|
| How to create Main Table or Inner joint. Please give sql in table level | | vimal | 03-02-2010 |
|---|
Thanks i got the complete idea of Inner join.
| | Zameer | 16-02-2010 |
|---|
| Plus2net is one of the best source to learn :) | | fei | 02-03-2010 |
|---|
| this all tutorial in your web in very helpfull for me. and for beginner programers, thanks | | ann | 11-03-2010 |
|---|
| thanks..!!gud website for beginners... | | Narasimha Varman | 12-04-2010 |
|---|
| thank you plus2net. Its very helpful. Examples shown here are live one. | | Garrettraj | 16-04-2010 |
|---|
| Thanks.....i got the complete idea of Limits.Examples shown here are live one. | | jacob | 25-07-2010 |
|---|
| thanks a lot its wonderful explanation of inner join ................. | | ashoks | 09-10-2010 |
|---|
| Thanks i got the complete idea of Inner join. | | PJ4YOU | 31-10-2010 |
|---|
| This is a brilliant way to explain,But I have a question.What is the difference between INNER and SELF Join? | | priyalogasamy | 18-03-2011 |
|---|
| i need to select account numbers from 1 table by checking conditions in other 2 tables | | surendra | 23-09-2011 |
|---|
| Thank you ,i got the full idea about inner join | | Prashant Sahu | 24-11-2011 |
|---|
| Can you tell me differnce between self join, equi join and inner join? | | Vikas Yadav | 15-05-2012 |
|---|
| The representation method is superb..Every biggner can understand easily...Very very thanks ...Vikas | | Jyodentist | 03-08-2012 |
|---|
| Thank you,Good explanation with example. | | Subhash Patel | 16-08-2012 |
|---|
| its learnt by me easily from here.......nicee.... |
|
|
|
|
|
|
