MySQL INNER JOIN Tutorial & Examples

This tutorial shows you how to write ANSI-Style inner joins with the INNER JOIN keywords. Included are a general description, some syntax examples and a comparison between inner and cross joins.

Note: In MySQL the join keywords JOIN and CROSS JOIN are synonymous with INNER JOIN. That means: All example statements found in this article work fine when you replace INNER JOIN with JOIN or CROSS JOIN.


Syntax

Here are syntax examples for the impatient. Basically, ANSI-style join conditions can be specified with two different keywords: USING and ON. Take a look at the following examples:

-- inner join with USING clause
SELECT *
FROM <firstTable> a INNER JOIN <anotherTable> b
USING(<columnName>)

-- inner join with ON clause
SELECT *
FROM <firstTable> a INNER JOIN <anotherTable> b
ON a.<someColumn> = b.<anotherColumn>

Now we should take a closer look at inner joins to understand what they really do.

Basics

Probably the most common join operation MySQL supports is an inner join. It identifies and combines only matching rows which are stored in two related tables. A join condition, which indicates how the tables are related, is added with the keywords ON or USING :

  • ON is used when the relationship column has a different name
  • USING is used when the relationship column has the same name in both tables

Take a look at the examples:

 

-- INNER JOIN with ON clause
SELECT *
FROM tableA a
INNER JOIN tableB b
ON  a.someColumn = b.otherColumn

-- INNER JOIN with USING clause
SELECT *
FROM tableA a
INNER JOIN tableB b
USING(columnName)

Inner Join vs Cross Join

In MySQL, the keywords CROSS JOIN and INNER JOIN are synonymous. ANSI SQL defines a CROSS JOIN as a join without a condition which builds the Cartesian product of two tables. In that case, MySQL combines every row in the left table with every row in the right table and returns the result set.

-- inner join without a condition: cross join
SELECT *
FROM <firstTable> CROSS JOIN <anotherTable>

When you have to build the Cartesian product of two tables, use the CROSS JOIN keywords to indicate your intensions. It makes it easy to read your statement and of course, keeps your code more portable.

Inner Join vs Outer Join

The major difference between outer and inner joins is: an outer join is able to identify rows that were not matched by any row in the joined table. Therefor, if you’re searching for rows from tableA that have no related entry in tableB at all, you have to use an outer join. Please see the tutorial about outer joins for a more information.

More than one join

It’s also very common to add more than one join to a single statement. There is no special syntax required. You only have to write a second (a third, and so on) join:

-- more than one inner join
SELECT *
FROM tableA a
INNER JOIN tableB b
ON  a.someColumn = b.otherColumn
INNER JOIN tableC c
ON b.anotherColumn = c.nextColumn

Inner Join with comma operator

An inner join can also be written with the help of the so called comma operator. Please read the dedicated tutorial about writing inner joins with the comma operator for more information.

You can host your MySQL projects on regular web hosting packages or a dedicated server. Nowadays, all of them should support inner joins.

External Resources

This entry was posted in Join Tutorials and tagged , , , . Bookmark the permalink.

48 Responses to MySQL INNER JOIN Tutorial & Examples

  1. Pritesh Loke says:

    Very good explanation on MySQl joins.

  2. Farhan says:

    Really great work, I was searching basic for mysql JOINS, I think this is a great and easiest example. Thanks

  3. anil says:

    thanku so much its very nice explanation from you really thanks.

  4. Big thanks, really great Tutorials about Joins.
    Easy to understand and very short.

  5. Arvie says:

    Really great, easy-to-understand tutorials … much appreciated!

  6. Rupesh kumar says:

    can u tell we one thing.

    Can we use join statement to insert / update /delete the data inot multiple table at once?. we can do it by trigger. but can we use join?
    thanks..

    • jbrinkmann says:

      Hi there. I think you can only delete, update and insert data into one table at a time. When you do INSERT…SELECT or UPDATE…SELECT you can use join operation in the SELECT statement, though.

  7. Sowmya says:

    Thanks a lot.This is a great tutorial

  8. Pingback: MySQL STRAIGHT JOIN Tutorial & Examples » MySQL Join Tutorials

  9. Pingback: MySQL NATURAL JOIN Tutorial & Examples » MySQL Join Tutorials

  10. Pingback: MySQL INNER JOIN Tutorial: the comma operator » MySQL Join Tutorials

  11. Really very easy to understand with the a lot of optional queries for a single work piece

  12. Jeroen van den Broek says:

    You showed an example of an INNER JOIN between multiple (>2) tables.
    Can you also use the ‘comma operator’ alternative to join more than 2 tables, like:
    SELECT *
    FROM tableA a, tableB b, tableC c
    WHERE a.someColumn = b.otherColumn
    AND b.anotherColumn = c.nextColumn

    I know it is possible in other DBMS’s, but would like to know whether I can use that in MySQL?

    • jbrinkmann says:

      Hi. Thanks for your comment. This is possible in MySQL as well. For more readable statements I prefer to write ANSI-style joins (with INNER JOIN and so on), though. If you write larger queries, with sub-selects for example), it’s much easier to understand what a statement does.

  13. Richa says:

    good tutorial…

  14. Amit says:

    Great dude ……………..’

  15. Marc says:

    I have been doing simple equijoins for years. Your explanation and tutorial cut through Teh Complicated.

    Thank you.

  16. Pragam says:

    nice tutorial……………..

  17. Awwh man, thankyou very very much!!
    I’ll learn so many things by here!!

  18. srinadh says:

    i have to join two tables having two common columns by Natural Join. and then the resultant table i have to left outer join with another table. how?

    • jbrinkmann says:

      Hi. Can you paste the CREATE statements of the tables in question to pastebin.com? Just leave a link and some informations about the relationship between the tables/columns.

  19. Stanton Oto says:

    Thanks for this wonderful post! It has been very useful. I wish that you’ll carry on sharing your wisdom with us.

  20. Pingback: JavaPins

  21. Chrisz says:

    I just want to ask using JOIN statement will increase the speed of searching some data in a database? is that correct? Can u also give a simple example a JOIN statement with the combination of WHERE?

  22. Jagadish Debnath says:

    Excellent.

  23. David Reed says:

    No where on the net have I found such a great description of joins. Thank you for taking the time to explain.

  24. Chetan Wagh says:

    Thanks to for explanation.

  25. Oriley says:

    Simple and straight forward.

  26. Kat says:

    Thanks !! :) this is one of the best article or tutorial on using join with more than two tables out there – if not the best. bookmarked but share buttons came back with errors for linked in and twitter.

  27. tushar says:

    nice one sir , thanks !

  28. ch_bhanu says:

    thank you so much for providing such a nice tutorial.

  29. Kellee Lemone says:

    I’m still learning from you, as I’m trying to reach my goals. I definitely enjoy reading all that is posted on your site.Keep the tips coming. I loved it!

  30. Justin says:

    Thank you so much for the great info! I am trying to join two tables one table defines a relationship between 2 users (userA, userB) and the other contains user information (id, name, email, etc…) I simply want to be able to display a users “friends” from the relationship table filtered by their user ID (session). I was advised to try and Join that tables and have been able to do that but I can’t seem to filter the results by the session variable… Any help is appreciated. Thanks so much.

    • jbrinkmann says:

      Hi. Thanks for your feedback! Can you maybe post your table structure using pastebin.com ? Would make it much easier :)

  31. Reazul says:

    Thank you sir. As I am a beginner this is helped me a lot.

  32. Bhavik says:

    This is the best tutorial i have ever seen for Mysql.
    The Venn-diagrams penetrates right into brains to explain the concept.
    Thanks a lot.
    You rock…..

  33. swordsky1 says:

    this is a sweet lovely mysql website, plz visit it, thx.
    http://swordsky.com/

  34. Anand says:

    Thanks Thanks Thanks Thanks Thanks a lot

    you saved me friend. Best tutorial I have ever seen.
    ……….You really Rock………….

    • jbrinkmann says:

      Thanks a lot for your feedback! We really appreciate if our small contribution to the world is somehow helpful for others :D

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>