SQL INNER join query for MySQL

INNER 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 tableManagersEmployee
id name m_id
1 John 2
2 Greek Tor 1
3 Alex John 0
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
3 Alex 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
Main Table (emp ): Table with id , name and m_id. Each employ has one unique id and one m_id ( manager id which is part of id field )

Note that we have only one table main table and other two Managers and Employee reports are generated out of the main table only.

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

Employee report

To generate employee report we will use the matching id of employee. Note the id =3 , here since we kept him without boss but still as an employee we can display.

To generate the employee report 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.id

Employee Manager report

Now let us use inner join to create one report to display 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

Note : Here record with id=3 is missing as Alex John does not have any Manager.
id emp_name manager
1 John Greek Tor
2 Greek Tor Alex 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 IF NOT EXISTS `emp` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL DEFAULT '',
`m_id` int(4) DEFAULT '0',
UNIQUE KEY `id` (`id`)
) ;

--
-- Dumping data for table `emp`
--

INSERT INTO `emp` (`id`, `name`, `m_id`) VALUES
(1, 'John', 2),
(2, 'Greek Tor', 3),
(3, 'Alex John', 0),
(4, 'Mike tour', 1),
(5, 'Brain J', 3),
(6, 'Ronald', 3),
(7, 'Kin', 4),
(8, 'Herod', 3),
(9, 'Alen', 2),
(10, 'Ronne', 1);

Number of User Comments : 23


Google+

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....
Mainuddin Bhuiyan

31-01-2014

Go ahead...........Don't think ...site is useless
I am use this site 3to 5 times in every week.

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

What is the most important factor of a web site

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer