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 109 users or customers.
And then a lot of people left.
They just deleted their accounts.
Let's say something bad happened.
There was a hack, a data breach.
Everyone got scared away.
And then all we were 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 a dumb made up story.
But what we could do is use a 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 null.
So if we do our join that we had done earlier, I'm just going to scroll up to save times or save time.
If we do this, join here, which is an inner join or that's 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, his two orders, Bette Davis, her one order.
And then if I did a left join, which we could just do by simply changing that one inner to be left.
Or we can just do this here and I'm only choosing this one because rather than Star, this has a smaller
subset of data that's easier to look at.
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, if you can hear my cat meowing 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 is the wrong term.
But from customer's first right, join with orders so that we take everything from orders like this.
Everything from orders in any overlap from customers.
Let's try it.
And you can see exactly what we expected happened.
So we still have all of our 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 null firstname.
NULL Last name null first name, no last name.
So that's basically all I wanted to show you with right joints.
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 setup brand new data just so that
you could see how a write join works.
So there is 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 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 it.
We can still do things like group by or we can do things like order by.
So we can add that in here.
Let's do order by and let's do first name just like that.
Okay.
What about if we tried to group buy?
What if we did group buy?
And what if we tried to group buy customer ID?
What happens then?
So it doesn't look like all that much.
Right.
But we can still do our same.
We have Boy George was condensed.
George Michael condensed.
But we can do a sum for amount just like we've been doing.
And we can see how much everyone has spent.
Boy George.
135.
George Michael 813.
Bette Davis 450.
We still have these two nulls.
So this is where we can start to make decisions and do things like, okay, if first name is null,
then let's replace it with something.
So I'm going to format this a bit and as we start to get more and more.
And we'll replace first name and last name with if null.
First name.
And remember how this works.
If first name is null, then it will be replaced with whatever we pass in.
So we'll say we'll just say missing.
And then we can do the same thing for last name if null.
Last name, comma, we'll say user.
So we'll end up with missing user if we do it that way.
If we don't have an error or extra comma here, now we do it.
And let's use aliases as first as last.
All right.
Got a lot going on.
So now we have missing user.
And let's 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 by customer ID, but what if I wanted to group these to what we could do is
change customer ID to be first name and we'd want to do comma 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 than Bette Davis 450 and so on and we
could order it, I think by now you understand what I'm trying to show, that these tables aren't special.
You just have to be comfortable working with NULL and if NULL is kind of your best friend when you're
doing that.
Okay.
So then the very last thing I wanted to show, keep doing that.
Go away.
The last thing I wanted to show was how we do what's known as on Delete Cascade.
So this is what I was talking about.
When we delete, if we have a foreign key relationship, when we delete a parent.
So if we delete customer's 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 isn't about joins, 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.
I'm going to indent this a bit and all we do is add on delete cascade.
And all that that does is it says when a customer is deleted, that has a corresponding order to delete
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.
So as we know, we have data that is joined.
I'm not going to spend time going over the joint part.
Let's just do select star 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 delete Boy George.
These two should now disappear versus before a couple of minutes ago.
And 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 the 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 email.
Okay.
So we're going to delete from customers where email equals George at gmail.com.
And we don't get that scary error.
Now, if we look at all 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, it'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.