Okay so we hopefully survived the last couple of videos on joins we had cross join the use this one
then we have inner join and then left join now we have the sister or brother of left join which is right
to join.
So it's actually this would be a pretty short video.
It's very similar to left join in that it takes everything from one column along with excuse me it takes
everything with from one table along with the intersection, along with wherever the join condition
is met.
But it just does it from the other table.
So in this case, we've been doing customers and then orders.
So if we did a right join, it will take every record from orders along with any matching records from
Table A, which is our customers.
Now the thing is, and you may have maybe thought about this already, we don't have any orders that
don't have an existing customer, so we're actually not going to see a difference.
But I'm going to come up with a way I'll show you a way for us to see a difference.
But to start, let me just show you the syntax.
So let's hop over to Cloud nine and here's our simplest left join that we wrote.
Remember, it does this here where it includes everything from customers.
David Bowie and Blue still included, even though they don't have any matching records.
And then it also includes all matching records.
But rather than that, I'm now going to go and retype it down here as a right join.
So we'll do a select and we'll just do star from customers.
And then we'll do join orders on customers ID equals orders dot customer ID just like that.
However, if we leave it like this, remember, if we don't explicitly say what type of join, it's
implied to be an inner join.
So we can actually start with that because it will be good to see what that looks like and compare it
to what we get when we do a right to join.
So when we did left, which is right here, we have these nulls and we have let's see, one, two,
three, four, five, six, seven entries.
Versus when we do an inner join, we have five.
And if we make it a right joint, all we have to do is change that.
If I can type.
Right.
There we go.
Let's try this one.
And we have five again.
One, two, three, four, five.
And actually, it's identical to what we got when we did the inner join.
And the reason that's happening, I kind of already went over this briefly, but the reason it's happening
is that we don't have any orders that don't have a match with a customer.
So everything we have, if we go back here, everything we have in the orders table has a matching customer.
So there is something to join it on.
So for every order we actually have five this table and it has four, but we have five in our database.
Then there's only going to be five joints when we are going to be five records that are joined.
When we do a right join.
When we did a left, we had a bunch of well, not a bunch.
We had a couple of users who didn't have any records, who didn't have any orders.
So these customers, like David Bowie and Blue Steel didn't have a match, but we still included them.
So how can we actually see something different?
How can we tweak our data so that a right join actually results in something distinct?
Well, there's a simple thing we can do, which is to screw with our data or excuse me to mess with
our data.
So that's what I'm going to do here.
I don't necessarily recommend that you follow along exactly, because I'm going to be messing up some
of the data.
I'll fix it towards the end of the video.
But I don't want you to have to go through that process if you don't want to.
So it's non-essential.
What is essential is how the join will work, which we'll get to.
So the way that we would have a difference, we'd have a unique result between right and inner is if
there's something that we have on the right table, which for us has been orders in the orders table.
If there's something in the order table that doesn't have a match over on the left that there's no join.
So that would mean that there's no customer.
And right now it doesn't make sense, honestly, to have an order without a customer, but maybe let's
say someone accidentally deleted stuff from our database or maybe there's some a wrong ID, a wrong
customer ID in in our data, we would be able to spot that using a right join.
So what I'm going to do is hop back to cloud nine and before I do anything, I'm just going to rerun
this early inner join we did.
So as you can see, of course, on the left we have customers.
On the right we have orders and we have Boy George who has two.
Let's say I were to delete Boy George and then I ran an inner join.
Well, these would be gone and we would only have three customer orders, right?
But if I did a right join, we would have order, order and null and null over here.
So it takes all orders regardless as to whether they have a matching customer or not, and they'll fill
in null if there isn't a customer and then if there is, the appropriate information will be filled
in just like it was with left except the opposite direction.
So let's try that.
Let's try deleting a user or a customer.
Let's delete Boy George It's been a while, but that's going to be delete from customers.
You keep calling it users where and we could do it based off of ID.
Let's do it based off of first name.
First name equals boy.
Just like that.
Now, what do you think will happen?
Oh, no, an error.
And he may have anticipated it being a syntax error based off of my typing proficiency, but it's not.
It's a deeper error.
It's an error that has to do with that foreign key constraint we set up.
Because if we try and delete a customer that in order is referencing, we have two orders referencing
it.
That's a problem according to our foreign key constraint that we set up up here.
Remember that this doesn't let us create an order with a faulty or nonexistent customer ID, so it also
won't let us delete a customer up here if it's dependent on down here.
And I kind of did this on purpose here to show you a solution to this, not to let us delete things
willy nilly, but how could we if we deleted a customer, how could we automatically have it delete
all orders associated with it, which is a pretty common thing.
If you have data that's dependent on another piece of data, let's say we have books and reviews.
Well, if that book is deleted, for some reason we don't want all these reviews that are just pointing
to an empty or gone book, so we would want to link them.
So if I delete the book, all associated reviews get deleted and I'll show you how to do that.
But the first thing I'm going to do is delete these two tables.
I'm going to remake them.
And this is the part I was saying you do not need to follow along.
So let's do that now.
Drop table and we have another little surprise in store for us.
If we try and drop table and let's just do customers, what do you think will happen?
Same issue.
We can't delete customers because orders depends on it.
So what we can do is delete orders first and then customers, or we can do it in one line like this
orders common customers.
Well, if I put an SE there and now of course I messed it up because orders is already gone.
So if I had type that correctly, it would have worked.
But we'll just delete customers separately.
Now, if I do show tables, there's nothing there.
So the reason I did that is I want to remake our two tables very simply.
So I'm going to copy this.
And this is why I was saying you do not need to do this yourself necessarily, and I'm going to change
one line.
I'm going to get rid of the foreign key constraint just like that.
Everything else should work exactly the same.
So I'm going to remake the customers table, remake the orders table, I'm going to insert our customers
and I'm going to insert our orders just like this.
So if we do a select star from customers, let's say we have the same data, same thing from orders,
but now what I'm going to do that I couldn't do before is I'm going to insert a new order.
So insert into orders and I'm going to insert what we have order, date, amount and customer ID and
I'll do two of them.
And so the first one will be something ordered, let's say 2017 slash 11, slash oh five and the amount
was $23.45.
And the customer ID, this is the important part that I'm trying to highlight here.
I'm going to put something in that doesn't exist in our table.
So let's say 45 and I'll do one more.
I'll do rather than this.
Let's get a little practice.
Remember, using cur date gives us the current date.
Let's do that.
So we'll do a date here, comma.
And this purchase was 477 seven 777.77 and that was made by someone with ID 109, which also doesn't
exist.
Perfect.
So now let's insert those.
Didn't seem to have any problems.
Let's look at orders.
Yeah.
It's here.
Right.
We have customer ID of 45 and 109.
And the reason this works is we got rid of that foreign key constraint, which I'm not saying you should
do, by the way.
I'm doing this to illustrate and make a difference in our data.