So let's suppose we're inheriting someone else's database and they decided not to use foreign.
They didn't know about foreign key constraints so they didn't use it.
And then they had users.
They used to have all the way up until a hundred and nine users are customers.
And then a lot of people left they just deleted their accounts that say something bad happened there
was a hack a data breach.
Everyone got scared away and then all we are left with are these customers.
It's pretty sad.
So I mean it's a great bunch of customers to have honestly but still only five compared to 109.
Keep in mind this is all done made up story.
But what we could do is use our right join to basically identify all of our customer and orders and
their associated orders as well as any orders that don't have an associated customer.
So we could fill in No.
So if we do our joint that we had done earlier just you know scroll up to save times or save time if
we do this join here which is an inner join or that actually a left join.
Let's do this one right here.
Nothing changes right.
We still have Boy George and his associated two orders.
George Michael has two orders.
Patty Davis her one order and then if I did a left join which we could just do by simply changing that
one inner to the left or we can just do this here.
And I'm I'm only choosing this one because rather than Star this has a smaller subset of data that's
easier to look at.
OK so now we end up with this which still is unchanged from when we did it in the last video because
there are orders that have no customers but they're not here because we did a left join.
And the left join takes everything from customers and any associated matching data from orders.
But now if we do the same thing down here you can hear my cat me.
And we just change it to be right join.
So we're still just doing first name last name order date and amount from customers on the left or from
on the left as long term from customers first right joined with orders so that we take everything from
orders like this everything from orders and any overlap from customers.
Let's try it and you can see exactly what we expected happened.
So we still have all of her orders that match customers and orders but then we have those two orphans
that we added in.
Where there is no matching customer.
So we end up with no first name no last name no first name no last name.
So that's basically all we want to show you with.
Right.
Joines But like I said we will have actually relevant useful examples later but the last thing I wanted
to do was hit you with a whole bunch of set up brand new data just so that you could see how a right
joining works.
So there is you know this is a hypothetical situation that could happen.
We could have orphan data like this.
So I'm going to show you two things now.
The first one is that we can still just do any you know anything that we want with this data like we
did earlier we can do more complex selections.
And I'm also going to show you a new constraint.
You can add to your schema so that when something is deleted that has a foreign key depending on it
it will automatically delete the dependent record.
But to start I just wanted to show we can still do things like group by.
Or we can do things like order by so we can add that in here.
You order by.
And let's do first name just like that
OK.
What about if we tried to group by but if we did group by.
And what if we tried to group by customer ID.
What happens then.
So it doesn't look like all that much right but we can still do our same.
You know we have Boy George was condensed.
George Michael condensed but we can do a sum for amounts just like we've been doing and we can see how
much everyone has spent or George 135 George Michael 8:13 Bette Davis for 50.
We still have these two Knolles.
So this is where we can start to make decisions and do things like OK if first name is no then let's
replace it with something.
So I'm going to format this a bit as we start to get more and more and more replace first name and last
name with if no first name.
Remember how this works.
First name is no.
Then it will be replaced with whatever we pass in.
So we'll say the same missing.
And then we can do the same thing for last name if no last name comma will say use her.
So we'll end up with missing user if we do it that way.
If we don't have an error extra comma here that would do it.
And let's use aliases as the first as last all write I like going on.
OK so now we have missing user and then say we want to see what the total of our orders are where we
don't have a user.
So basically the orphans we want to group those together as well.
Right now we're grouping right customer ID.
But what if I wanted to group these to what we could do is change customer ID to be a first name and
we'd want to do Khama last name rather than just first name because we may have multiple people with
the same first name.
So if we do this now
you'll see that we end up with missing user has 801 total spent then Bette Davis 450 and so on.
And we can order it I think right now you understand what I'm trying to show that these tables aren't
special.
You just have to be comfortable working with NO and if no it's kind of your best friend when you're
doing that.
OK.
So then the very last thing I wanted to show I keep doing that.
Go away.
The last thing I wanted to show was how we do what's known as on the beat cascade.
So this is what I was talking about when we we if we have a foreign key relationship when we delete
a parent.
So if we delete Customers table not the whole table but let's say we delete Bette Davis.
We want her order to also be deleted.
So it's not just totally on its own.
We don't end up with this situation.
So how do we do that.
Well I'll show you right now if you're not interested then go ahead and skip this.
This isn't about Joines but I figured it's a good place to put it.
I'm going to actually drop our tables again.
I'm going to redo.
So grab our original schema from way up here.
Just copy.
How much do we want down to here.
And we still have our foreign key there and when and done to submit and all we do is add on delete cascade
and all that does is it says when a customer is deleted that has a corresponding order to read the order
as well.
So let's try it.
I'm going to copy this in.
Let's make sure we don't have any tables right now.
Perfect.
So let's copy it then let's copy orders in and then let's insert our data and insert our data again.
OK.
So as we know we have data that is joined I'm not going to spend time going over the joined part let's
just use select start from customers.
Okay.
And then we'll do a select star from orders and let's say we want to delete Boy George.
Boy George has two orders these first two.
So if I had to lead boy george these two should now disappear first.
And before a couple of minutes go on we tried it.
We had a problem.
It's not really a problem but we we ran into trouble that we put in there ourselves because a foreign
key constraint didn't allow us to delete it.
We weren't allowed to delete a customer unless we deleted the order first.
So we're going to do that now.
Just try deleting based off of ID or we could do it based off of first name let's do e-mail.
OK.
So we're going to delete from customers where e-mail equals George at gmail dot com and we don't get
that scary error.
Now if we look at our customers we're missing boy George and if we look at our orders we're only down
to three.
All right now we're about to move on to our exercises.
So you're going to get some practice on your own.
Hopefully that's a little.
We have new data so hopefully it's a little more interesting rather than having to work with the same
old data over and over.
And then we're going to keep moving on.
All right.