Quick Tip: Working with MySQL and INNER JOIN
videos

Quick Tip: Working with MySQL and INNER JOIN

on Jul 5th 2010 with 61 comments
Tutorial Details
  • Topic: SQL JOINs
  • Difficulty: Moderate
  • Estimated Completion Time: 5 Minutes

In today’s video quick tip, we’ll go over when and why to use JOINs when working with your databases.

Premium Members: Download this Video ( Must be logged in)

Subscribe to our YouTube page to watch all of the video tutorials!
Prefer to watch this video on Screenr?

 
Enjoyed this Post?

Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!

By

Rollover to read this author's bio or click through to see a full list of posts by this author.

I'm the editor of Nettuts+, and love making the process of building for the web as easy as possible. Also, I'm finishing up my latest book on Laravel Testing! If you want be friends, say hi on Twitter.  

Plus Premium
 
 
Note: Want to add some source code? Type <pre><code> before it and </code></pre> after it. Find out more
  • http://robertwojo.com Robert

    Found this really helpful. Thanks a lot.

  • http://wyome.com John Cox

    Heh, another lost reference:). Nice job.

    • http://www.jeffrey-way.com Jeffrey Way
      Author

      Nope. John, Kate, and Sawyer were totally random.

      • http://mediagearhead.com Giles

        I think this quick tip made me crap myself a little… So many applications for this and I always wanted to know if something like this was available and now it is. And in youtube format like the winners do it.

        4 8 15 16 23 42

      • http://net.tutsplus.com Jeffrey Way

        Haha on the numbers.

  • http://www.ecustom.ca/ Damon Bridges

    Lol, I had just read about this, and thought, man, I wish there was a quick tip for this.

    So I logged on to Nettuts+ to search for it, and it was right here, on the front page.

    Thanks Jeff!

    • http://www.ecustom.ca/ Damon Bridges

      I read about it in the SitePoint “Build Your Own Database Driven Web Site Using PHP & MySQL”, a book I recommend to PHP beginners, as it’s very helpful. I wouldn’t call myself a beginner though, it’s got some nice theories applied, that can be brought elsewhere quite easily.

    • Edgar

      Me too!! Just reading about JOIN, and I thought, “Let´s go to internet”.

      Suprise, in my bookmarked Net.Tuts. LIke magic.

  • http://zackperdue.com Zack

    I was literally struggling with joins last night coding an app and look what shows up on tutsplus the next day. Just in time! how ironic. This is going to help me out a lot i hope. it seems like these types of queries are mal-documented.

    Thanks a lot nettuts!

    • http://zackperdue.com Zack

      I think this is a great starting point for a tutorial, but it would be wonderful if you could make a longer more in depth look at all the types of joins and when and why to use them.

      It doesn’t even have to be a video, as long as it is thoroughly explain because im getting a lot of error like ambiguous ids and col name “blah” doesn’t exist.

      I look forward to more about these join tutorials.

      Thanks Jeff!

      • http://www.jeffrey-way.com Jeffrey Way
        Author

        Hey Zach – If it says “ambiguous id,” it’s because both tables have the same column name: “id.” Instead, use “myTable.id.” It uses the word “ambiguous” because it doesn’t know which “id” you’re referring to. :)

      • http://zackperdue.com Zack

        Yeah, I thought that, but i was trying to use a full join, which in my thinking basically thought it would work no matter what… not sure exactly though, so i switched to an inner join and that worked, but i would like to throw in a where statement if thats at all possible.

      • Christopher

        There is a tutorial on database relationships already on nettuts!

        http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/

        It’s very thorough and well explained.

        And thank you for an very nice quick tip Jeffrey.

  • http://tech.gtaero.net/ Navarr

    Is doing something like

    SELECT `tasks`.`id`,`title`,`description`,`users`.`name` FROM `tasks`,`users` WHERE `tasks`.`user_id`=`user`.`id`; wrong?

    Is one better than the other?

    • Juan

      Using JOIN is faster, obviously you won’t notice it in a simple query like that, but in a longer, meaner and complex query you propably see the difference.

    • http://www.phpandstuff.com/ Burak

      What you did is technically an OUTER JOIN followed by a WHERE clause. I would expect MySQL would optimize a simple query like this so it wouldn’t take any longer than the INNER JOIN, however, it would be a better practice to utilize the ON clause.

    • Daniel

      I guess it depends on what you mean by “better”. I think mySQL will convert this to an INNER JOIN internally, so it might be a tad slower if that’s what you’re asking.

      I always hesitate to say something is “wrong” if it solves the problem at hand. I, personally, would not want to do it this way because it’s to easy to read past the code not realizing that you’re actually doing a JOIN and not a simple SELECT.

  • http://www.urbanvideos.tv alan

    Nice job,This is what I was looking for..
    @Damon Bridges,

    thanks for recommendation !:)

  • http://pixelcoder.co.uk Alistair

    Hey Jeff, thanks for this quicktip! Very very useful. INNER JOIN is something we studied at college although I could make it work from instruction from Lecturers it never really sunk in ya know. Over 2 years actually of MySQL tuition. Yet your way of explaining has really helped it. A simple “on the condition” really drove it home, and it’s power.

    Many thanks

    Alistair

  • KalebAustin

    Nice Tut, Jeffrey, however….

    Its “My S-Q-L”.

    =)

    • http://www.jeffrey-way.com Jeffrey Way
      Author

      heh – It comes from a background with SQL Server. :)

  • Nick

    Great tip, but would have been good to do a quick mention of outer joins as well. Inner joins only return exact records matched from both tables, whereas outer joins (left or right) include all records from one or the other tables whether there is a match between them or not.

  • Eric

    I’ve created a third table, basically a join table that just assigns task_id and user_id. Is there any benefit to doing it that way or am I just wasting time?

    • Ali Baba

      If you want assign same task to more than one user that’s they way to do it.

  • http://www.codeforest.net Codeforest

    I really admire how Jeffrey can say so much in under 5 minutes video. Great job

    • Arnaud

      I agree with you, Codeforest !

      • http://net.tutsplus.com Jeffrye Way

        Thanks, guys. It’s really difficulty actually. :) Had to record this one 4 times to fit everything in.

  • arnold

    nice jefff , this is something new you do in “quick tips”

  • Julius

    Hey Jeffrey,
    nice quick tip.
    Could you also explain the difference between Inner, Left and Right Join?
    That would be really great.

    • Monk

      Inner join – query compares all row of both columns to find which pair satisfy the join-predicate, these pairs are put in the resultset

      Left (outer) join:
      - resultset contains all the records from the left column, and adds the values from the right column wich satisfy the join-precidate.
      - if join-predicate isn’t satisfied, it contains the record for the left column and adds “null” for the right column
      - if more than one record satisfies the predicate, it repeats the record from the left column, adding the different records from the right column

      Right (outer) join:
      - the same as left (outer) join, but switch the columns

      Full outer join:
      - resultset contains all records for both columns
      - if join-predicate isn’t satisfied, records for left or right column will be containing “null”
      - if join-predicate is satisfied, records for left and right column are combined

    • http://net.tutsplus.com Jeffrey Way

      I had planned to; however, one of our writers, John Cox, will be submitting a more in depth overview of JOINs in the next week or so. That should cover everything.

      • Josh

        Can not wait for this, the app that im creating is hinging on me figuring out JOINS thats Jeff :)

    • Peter

      Julius, see http://www.pafumi.net/SQL_Joins.html for a good visual-aided description (it also appears to be where the image associated with this article was borrowed from).

  • http://silwing.dk Silwing

    Clean and simple introduction to joins! Just what I needed to get it ^^ thanks a lot.

  • http://www.darrnick.com Darren

    Wow, never really did anything with mysql, but that quick tip got me kind of excited about it lol

  • http://www.jordanwalker.net Jordan Walker

    Inner joins are great for bringing tables together, thanks for the tut.

  • http://matt-bridges.com/ Matt Bridges

    You never cease to amaze me, Jeffrey. Great job! Excellent!

  • michael

    nice tut, exactly what i was looking for right now ;)

    jeffrey, how do you group your icons in your bottom-bar?

    • adrian

      there’s a terminal command you can use to put a separator on the left or right side (applications or stacks side) and then you can move it like a regular app. I also separate my apps and I use a prefpane called secrets http://secrets.blacktree.com/ that has a database of all kinds of terminal commands like that.

  • adrian

    woah, i had no clue mysql was so powerful

    • easy

      lol, that’s just one of the basic features

  • http://nspirehq.com Ross

    Jeffrey, do you know if there is a Snippets program like the one you used in the video for Windows? I am unfortunately stuck using Windows for my web development, but I would love to use a program like that.

  • vanderson

    You have been paid to speak to mysql?

  • http://spotdex.com Davidmoreen

    Joins are the effing best. Oddly enough I only learned about them three months ago. Before I learned about joining I would always do one query, get it’s array and do the second query…

  • Peter

    Oh my god! Joins?! That’s so awesome, never heard about this before! Unbelivable they inventies this now!

  • Anouck

    Nice Tut!!!

    Now we need one for Left and Right joins, outer joins

  • Brad

    Its so much easier to learn things like this, to actually see them performed. Thanks Jeff

  • http://blog.simonedamico.com Simone D’Amico

    Hi Jeffrey,
    fantastic tip! :)

    Maybe I’m OT but I’d like to know which client you use in the tutorial.

    Thanks :)

  • http://www.kieru.com Rob

    Joins constantly give me grief in MySQL. I would love to see further coverage on the various types of joins and how to effectively use them. My biggest request though; would be a tutorial over a screen cast (or perhaps in addition).

    Granted; it’s not really a quick tip at that point :D

  • http://www.antonagestam.se/ Anton Agestam

    What’s the difference between using inner join and selecting from multiple tables? Like this for example:

    SELECT tasks.id,tasks.title,tasks.details,users.name
    FROM tasks,users
    WHERE users.id = tasks.user_id

    • Ali Baba

      No difference. Some people prefer INNER JOIN because it’s cleaner and easy to read

  • http://www.syamey.com/ Syahmi

    Cool! Really helpful.

  • Muhammad Omar

    Thanks Jeff. But i have a totally different question, which database is better, SQL server or MySQL? and why?

  • rickycheers

    Thank’s a lot! simple and useful..

    It’s allways cool to learn the basics this way ;)

  • Elan

    where i can download Querious?

  • http://www.gooogle.in niks

    Hey, Really Nice tutorial on mysql. Thanks for sharing it. Really useful.

  • Andrew-David

    Let’s say I want to fetch rows from two tables, linked by “user_id”, where user_id=1 only.
    But I want to order the results based on the “order” row which they both have.
    Example:

    table1:
    user_id | order | text
    1 | 1 | blah
    1 | 3 | lorem
    2 | 1 | info

    table 2:
    user_id | order | text
    1 | 2 | ipsum
    1 | 4 | dolor
    2 | 2 | text

    And I want to return:
    -blah
    -ipsum
    -lorem
    -dolor

    What would the query for that be?

  • David

    Sorry guys, cannot open this tutorial…

    I do pay regular premium subscription but as soon as I log in, the only thing I can do with this tut is to download the sources and read the header, nothing else… using Firefox…

    any help?

    Thanks a lot

    David

  • http://www.tenaxtechnologies.com java software developer

    Nice article, thanks

  • http://www.arlexclocks.com Alex Aguilar
  • Fratyr

    Not good. You had to tell what are the INNER JOINs are, and that there are other joins, the specific mission for this particular join pros/cons and a link to other joins.

    Yep.