SQL INNER join query for MySQL

Inner Join Venn diagram Inner Join will return common records appearing in tables.
INNER JOIN is also known as JOIN.
Inner Join requires two tables to be joined by linking with ON condition. We can add more condition by using WHERE clause.
Area as per venn diagram B
These two table we will use for our examples
Table one ( t1 ) Table two ( t2)
id name1
1one1
2two1
3three1
id name2
1one2
2two2
4four2


Joining tables Using SQL LEFT Join, RIGHT Join and Inner Join

QUERY using Inner Join

The common records between two tables will be returned.
SELECT * from t1 INNER JOIN t2 on t1.id=t2.id
idname1idname2
1one11one2
2two12two2

Example : products & sales table

Inner Join of two tables
AreaDescription
B & C There are matching values in both tables for p_id column
AThese products ( table 1 ) have no matching p_id in sales ( table 2 )
DThese sales (table 2 ) have no matching p_id in products ( table 1 )
Type of join and expected output
TypeTable 1Table 2Details
LEFTA + B C All from Left ( Table 1 ) and matching from right (Table 2 )
RIGHTB C + D All from Right ( Table 2 ) and matching from left (Table 1 )
INNERB C Matching from Left ( Table 1 ) and right (Table 2 )
CROSSA + B C + D All from Left ( Table 1 ) join to all from right (Table 2 )
INNER JOIN will return rows where join column value is available in both tables. Here the area B of products_v2 table and area C of sales_v2 table will be returned.
SELECT * from products_v2 a INNER  JOIN sales_v2 b on a.p_id=b.p_id
Output is here
p_idproductpricesale_idc_idp_idproductqtystore
3Monitor75123Monitor2ABC
4CPU55224CPU1DEF
3Monitor75313Monitor3ABC
2RAM90442RAM2DEF
3Monitor75523Monitor3ABC
3Monitor75633Monitor2DEF
2RAM90722RAM3ABC
2RAM90832RAM2DEF
3Monitor75923Monitor2ABC
SQL DUMP of products , sales and customers table
Above list includes only those rows where p_id is matching in both tables.

INNER JOIN using three tables

We will add customers_v2 table along with products_v2 and sales_v2 table
SELECT * from sales_v2 a 
INNER JOIN products_v2 b on a.p_id=b.p_id
INNER JOIN customers_v2 c on a.c_id=c.c_id
sale_idc_idp_idproductqtystorep_idproductpricec_idcustomer
442RAM2DEF2RAM904Rani
722RAM3ABC2RAM902Raju
832RAM2DEF2RAM903Alex
123Monitor2ABC3Monitor752Raju
313Monitor3ABC3Monitor751Rabi
523Monitor3ABC3Monitor752Raju
633Monitor2DEF3Monitor753Alex
923Monitor2ABC3Monitor752Raju
224CPU1DEF4CPU552Raju

Example: Find out students who have paid fees

We will use inner join to list out students who have paid fees.

student6

Student detils
id, name,class,sex ( 5 records)

student_fee

Payment details
fee id, student id , paid date, amount

You can download SQL Dump of these two tables ( with other tables) here.

SELECT a.id, name,class, sex, dt,amount from student6 a 
INNER JOIN  student_fee  b ON a.id=b.id
idnameclasssexdtamount
1John DeoFourfemale2013-01-08200
1John DeoFourfemale2013-01-10100
2Max RuinThreemale2013-01-24120
3ArnoldThreemale2013-02-02211
2Max RuinThreemale2013-02-07150
3ArnoldThreemale2013-02-06135
4Krish StarFourfemale2013-02-14100
SQL RIGHT JOIN LEFT JOIN SQL CROSS JOIN INNER JOIN : Table joining itself
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com






    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 your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer