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.
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 Jeffrey Way
Getting Good with JavaScript
Learn how to liven up your webpages, from start to finish. Veteran Nettuts+ author Andrew Burgess helps you Get Good with JavaScript.
Join our newsletter!
UberMenu - WordPress Mega Me ... only $16.00 
RoyalSlider – Touch-Enable ... only $12.00 
Kult House - More
Web Basix
Just getting started? Start with our basix tutorials on the fundamentals. View All Basix Tutorials
Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
Found this really helpful. Thanks a lot.
Heh, another lost reference:). Nice job.
Nope. John, Kate, and Sawyer were totally random.
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
Haha on the numbers.
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!
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.
Me too!! Just reading about JOIN, and I thought, “Let´s go to internet”.
Suprise, in my bookmarked Net.Tuts. LIke magic.
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!
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!
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. :)
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.
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.
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?
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.
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.
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.
Nice job,This is what I was looking for..
@Damon Bridges,
thanks for recommendation !:)
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
Nice Tut, Jeffrey, however….
Its “My S-Q-L”.
=)
heh – It comes from a background with SQL Server. :)
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.
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?
If you want assign same task to more than one user that’s they way to do it.
I really admire how Jeffrey can say so much in under 5 minutes video. Great job
I agree with you, Codeforest !
Thanks, guys. It’s really difficulty actually. :) Had to record this one 4 times to fit everything in.
nice jefff , this is something new you do in “quick tips”
Hey Jeffrey,
nice quick tip.
Could you also explain the difference between Inner, Left and Right Join?
That would be really great.
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
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.
Can not wait for this, the app that im creating is hinging on me figuring out JOINS thats Jeff :)
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).
Clean and simple introduction to joins! Just what I needed to get it ^^ thanks a lot.
Wow, never really did anything with mysql, but that quick tip got me kind of excited about it lol
Inner joins are great for bringing tables together, thanks for the tut.
You never cease to amaze me, Jeffrey. Great job! Excellent!
nice tut, exactly what i was looking for right now ;)
jeffrey, how do you group your icons in your bottom-bar?
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.
woah, i had no clue mysql was so powerful
lol, that’s just one of the basic features
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.
You have been paid to speak to mysql?
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…
Oh my god! Joins?! That’s so awesome, never heard about this before! Unbelivable they inventies this now!
Nice Tut!!!
Now we need one for Left and Right joins, outer joins
Its so much easier to learn things like this, to actually see them performed. Thanks Jeff
Hi Jeffrey,
fantastic tip! :)
Maybe I’m OT but I’d like to know which client you use in the tutorial.
Thanks :)
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
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
No difference. Some people prefer INNER JOIN because it’s cleaner and easy to read
Cool! Really helpful.
Thanks Jeff. But i have a totally different question, which database is better, SQL server or MySQL? and why?
Thank’s a lot! simple and useful..
It’s allways cool to learn the basics this way ;)
where i can download Querious?
Hey, Really Nice tutorial on mysql. Thanks for sharing it. Really useful.
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?
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
Nice article, thanks
Two more good articles that cover SQL Joins
http://www.developer.com//db/3-sql-join-concepts-to-help-you-choose-the-right-join.html
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html