Hello, in this video, I am going to show you how to use joins in SQL. So what is a join, it basically allows you to combine two or more tables and check your data across it and return rows. So we are going to be looking at the left join. So this returns all of the rows from the left table, which will be another first one that you put in the SQL statement in the query and your return the match records from the right table as well. So this is great if you have let's say, we have an author, by the way, there are the joins as well do the inner join, which returns all rows to have matching values in both tables. The right join, which returns all records from the as you can imagine the right table and you know any matching rows from the left table and the full join, which returns all rows.
When there is basically a match in either left or the right table. I'll provide an extra link so you can see the difference. Between the joints. But if you you know, grasp of what we're covering in this video, you'll be all good to go for the others as well. Okay, if we go to PHP myadmin, and what this table got ID username and type not really going to be concerned with type at the moment. But what we're going to do is create a new table mohab id IDs as the general finish should have, and I want it to be primary and I don't want it to be auto increment, then I'm going to have user underscore ID.
And this will refer to the actual ID in here. And we also know say, maybe this is to do with to call so this is the cause that each person owns. And we'll say, you know, Connie, this needs to be an integer of phone user ID or for an integer basically has to be the same as the datatype of D ID. That You're using and then. And this will be a box chart. So I'll put 30 to Russia more than covering.
Also, I wanted to say what we're basically doing here. If you come from a computer science background and heavily, you know, text based theory, and you haven't implemented code too much, you might know stuff about, you know, foreign keys. This is basically you know, what we're covering. So foreign keys, you can utilize joins to basically do all of it for you, you could, you know, theoretically have two or more SQL statements and want to loop on it, but it's just not very efficient considering the built in functionality to you know, do checks. Okay, so we've got this, click Save, and I need to insert some data in here. And the user ID so what user ID There we go.
So I'm going to say it I I know from the call though, like I like Rory And I want to insert another row so click go and see us writing one again number coral like the BMW 850 CSI This is a little older now. Certain number refer to that. And okay, that's fine. Let's say number two for Batman has a trauma spell the one from the Nolan trilogy yep here Pearson So Tom law and work epsg that's all there is. And I'm going to say that you have to have no vehicles far from is floating the roll, you know thing. Okay, so I've got two vehicles and got two vehicles and using the left joint, what we'll be able to do is Check this ID.
And we'll be able to check in let me reload this column here that we'll be able to check in here. And we'll be able to get the car name. So if we go here, so so far, it literally just returned all the users. To do the join, what we have to do is pretty simple. First of all, we need to say what information we're getting. So we want to get information from the user.
And we want to get the ID, we want to get the username, user's username, but we also want to get some information from from from from the cars table, the only information that we really want to display is the car name. So I'm going to say you just put cause car name. So that's all information that will get in that's fine. It's all good now. And now, we say from users. So that's the left table.
You know, the one that we'll be doing The comparison from the comparison with the PR here you put left to join or whatever type of join you are using. And this is the other table that happens to be cause. And now on, which is basically the check. So it's going to be on user.id. If that equals call dot user underscore ID. And we also want to basically printed out here as well for an input to what do I want to put here on and put quotation marks.
And actually, I'll put a dash here. There's a bit of formatting and here we would put $1 value. And we are going to put Carnegie off in the corner on it. Yeah, probably we have time. Now if we run it, let's see what we got. There we go.
For Han, the Ferrari SST he also the BMW a 50. CSI, Batman has the tumbler and Yoda has nothing. So this is a great way of getting all the information about a particular, you know, person, you could do it not just on an ID you could reform for a particular user. So you can Yeah, you can easily do that as well. So why would you want to do this? Where could you, you know, might want to do this.
So imagine if you have a social networking website, and you have a users table, so all users Dao registered on there, you also have a separate table for maybe posts, you know, like Facebook, the all the posts that the user has made. And then on the main page, or the profile page of a particular user, you want to display all their posts, instead of using two SQL queries. And you know, just again, comparing one against the other. You could just use the join and say basically, on this one, where this ID appears in the posts table, Get all of the posts and then display them. It could be useful to commentary as well. So there might be multiple users, when you know, you know, providing multiple comments, you can easily get that.
So that's how you use joins to basically combine two tables. You can combine more than two table as an extra task only to have a look at how to do that. And also experiment with the outer join the inner join and the right join. If you have any questions, feel free to pop me a message and as usual, I look forward to seeing you in the next video.