We have one more type of joint to cover, which is the right joint.
So one more beautiful diagram.
We've got our left table and our right table.
When we do a join where we have the left, the right and we do a right join, we will take all the rows
from the right side and then include them, plus any overlap from the left side, meaning that we would
also maybe have null values.
The problem that we have right now, if I do customers on the left and orders on the right as a right
joint, it's not really a problem, but it's a problem from a teaching perspective.
The problem is that we won't see anything different.
So if I write a basic inner join one more time, let's do first name, last name, date.
I think it's order date, isn't it?
And amount.
From.
And we'll go with customers as the left join borders on the right.
And this is an inner join by default on and then customers that ID equals orders DOT customer ID I'm
sure you're sick of that right now or by now we run it.
Here's our inner join.
Great.
No surprises.
We've done this a bunch of times already, but if I tweak this to be a right join, let's see.
Oh, it's the same exact thing.
So the problem, of course, you kind of got into this in the previous video is that every single order
has a customer ID that is by the nature of our schema.
The two tables are set up so that you could have a customer with no orders, but you can't have an order
without customers or a customer, right?
Every single order.
Select Starr from orders has a customer ID, But if I were to try and let's say I had a row in here
that didn't have a customer ID, then things would look different.
So I guess I could do this insert into orders and I'm going to insert an ID that doesn't exist, but
we know that that is not allowed.
It's not going to happen if I try that.
I'll just show you what happens if we do a mount.
Order date.
Customer ID values.
Amount will be $100.
We'll just do the current date and then ID will be customer ID is 99.
We get this error right?
It says foreign key constraint fails.
However, with the way that our schema is set up, if we go back to describe orders, NULL is allowed
for customer ID.
So what if I did that?
What if I said insert a new order that has a value of 100 for amount current date for order date and
let's not do a customer ID.
And what did I.
Oh, I still have this 99 in there that works.
So now if I select star from orders, I have an order in here that does not have a customer ID.
Now, I probably in the real world would set this up so that you were required to have a customer ID
because how can you just have an order with no customer ID?
Who made the order in the real world?
That doesn't really happen.
I mean, I guess you could design it that way.
But this does help us because now if I go back to my right join, which was all the way back here,
here's my right join.
Now what we have is our left side still is customers are right side is orders, and we're taking every
order and including it automatically every single order.
The right side of this diagram and any overlapping information from the left which is going to be customers.
But in the case of this order right here, there is no customer.
So we see no and no.
So simple example, probably not that useful, but it does demonstrate how this works, right?
We have our right side of orders.
We are joining using a right join.
We take every row from orders the right side and then match up anything on the left where we can.
And if we can't match it up, we just get null.
So that's an example of a right join.