OK.
So we hopefully survived the last couple of videos unjoined.
We had CROSS JOIN use this one then we have INNER JOIN AND THEN LEFT JOIN.
Now we have the sister brother of LEFT JOIN which is RIGHT JOIN.
So it's actually this should be a pretty short video it's very similar to left join in that it takes
everything from one column along with this excuse me it takes everything 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
Card Table A which is our customers.
Now the thing is and you may have maybe felt 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.
OK.
So let's hop over to cloud 9 and here's our simplest left join that we wrote member does this here where
it includes everything from customers David Bowie and blue steel 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 Well just to 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 JOIN.
So when we did left which is right here we have these NOLs and we have 1 2 3 4 5 6 7 entries.
First when we do an inner join We have five.
And if we make it right Joe I know we have to do is change that if I can type right.
You 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 energy.
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 that 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 festival and it has four but we have five in our database and
there's only going to be five joints when you're just going to be five records that are joined when
we do a right join.
When we did a left we had a bunch of them not a bunch.
We had a couple of users who didn't have any records.
Didn't have any orders so 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 with We'd have a unique result between right and inare 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 Orders table that doesn't have a match over on the left 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
I.D. wrong customer id in our data we would be able to spot that using a right join.
So what what I'm going to do is hop back into cloud 9 and before I do anything I'm just going to rerun
this early inner join we did.
So as we can see of course on the left we have customers on the right we have orders and we have Boy
George who has to.
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 no anole over here.
So it takes all orders regardless as to whether they have a matching customer or not.
And no fill in all.
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 you know the opposite direction.
So let's try that.
Let's try it a leading user or a customer.
Let's delete Boy George.
It's been a while.
That's going to be deleted from customers who keep calling it users where we could do it based off of
ready let's do it based off of the first name first name equals boy.
Just like that.
Now what do you think will happen.
Oh no.
An error.
And you 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 tried to lead a customer that an order is referencing we have two orders referencing
it.
That's a problem according to our foreign key constraint that we set up a peer.
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 depended 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 is in store for us if we try and draw 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 do the orders first and then customers.
Or we can do it in one line like this.
Orders come in customers.
Well if I put an X there and now of course they missed it enough because orders is already gone.
So if I had typed that correctly but it worked.
But just to the customer separately now if I do show tables there's nothing there.
OK.
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 make the customers table make the orders table.
I'm going to insert our customers and I'm going to insert our orders just like this.
OK.
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 to insert
into orders and I'm going to insert what we have ordered date amounts and customer ID and I'll do two
of them.
And so the first one will be something ordered that say 2017 slash Lebon slash 0 5 and the amount was
$23.
Forty five cents 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 forty five and I'll do one more
I'll do rather than this.
Let's get a little practice.
Remember using curred date gives us the current day.
Let's do that.
So it would do curred date here comma and this purchase was four seventy seven seven seven hundred seventy
seven point seventy seven.
And that was made by someone with ID 1 0 9 which also doesn't exist.
Perfect.
So knowledge.
Insert those
didn't seem to have any problems.
Let's look at orders.
Yeah it's here right.
We have customer id a 45 in one or nine.
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.