OK so we've got this data in our database we have two separate tables that are distinct.
They have a foreign key.
The orders has a foreign key referencing customer ID but so far we haven't actually done any queries
you haven't worked with our data.
So let's try it now.
So back over in cloud 9.
Let's start with a simple question.
Let's look at our customers actually look at our orders and I have five different orders here.
Let's say I want to find the orders that have been placed by boy by Boy George.
So how do we do it and what the orders placed by Boy George.
Well we could do it as a two step process.
So I would first say basically find the customer find the ID of the customer whose name is boy George.
So we could do select start from customers where and then we would do something like where the last
name equals George.
OK so we get Boy George George Jim calm and his e-mail exceeded his ID is one.
OK.
Then we take that and we go to our orders table and we try and find OK where is in the Orders table
is customer id referencing one.
So we're just going to do a select star from customers where excuse me it's like start from orders where
customer ID equals one.
And there we go.
These are the two orders placed by Boy George.
So that was a two step process and we could simplify that or we could at least do it all at once actually
using something that we touched on.
You could use a subquery if you'd like to try and do that.
Can take a moment to figure that out but I'm just going to write it down here.
We would do something like this.
The two things we just did we we let's just copy this one.
We did this right here.
So let's start from orders were customer ideas one and then we also had like start from customers.
So to put them together what we basically want to do is find the the idea of the customer where last
name is George.
Then plug that into here.
So rather than hard coding it us one so it will look it's going to be a bit long.
It's going to be something like select star from orders where and then where is going to be long.
So it's going to be a sub query and we want to do where select ID from customers where last name equals
George.
OK so let's take a look.
We need to fix one thing actually like star from orders where customer ID equals the result of this.
OK so let's step through it.
Start with this middle part.
This runs it selects ID from customers where last name is George.
Which is what we did up here.
So when we run that we're only selecting ID so actually we could change that because we're on the sighting
ID and it gives us one so you could think of this whole thing.
It's replaced with one.
Actually it's more like this whole thing is replaced with one which leaves us with select star from
orders where customer ID equals one.
So let's test it out and you can see we get the same result but this is still not ideal it's a bit cumbersome
and this is only if we're working with one user anyways.
What if I wanted to see a synthesis of my data if I wanted to see next to every order.
So something like this go up.
Let's do so next start from orders next to every order.
I wanted it not just to say customer id I wanted it to say the name of who made the order.
So boy George.
George Michael Bette Davis and I wanted that to be printed here.
So how do we do that.
And this leads us to our next topic which is joint so joins allow us to accomplish that.
The whole point of joints which is sort of this buzz word the maybe intimidating mysterious thing to
some of you.
The whole point of it is that it takes two tables and we can can join them in a couple different configurations
that will talk about what those are.
That's the next few videos.
But we can can join them basically take the data from one and take the data from another and stick them
together in usually a meaningful way.
But I'm going to show you one way that is not meaningful to start.
And this will be our first most basic join that you probably will never use.
I never have used it but it's good to know.
And it just looks like this.
Select star from orders.
We can do customers first.
Doesn't matter.
Customers comma orders and that's it.
So if we just select from both of them what do you think will happen when I hit enter here and we get
this monstrosity.
It's very long.
And what we're actually looking at is called a cross join.
I think I've also heard it called a cartesian join.
Like I said it's pretty useless but it's interesting to see what it does is it takes every customer.
So we have boy boy George George Michael David Bowie blue steel.
Bette Davis.
But then notice that let's ignore the right half just just the customers.
It goes from one to five and then it repeats.
Boy George.
David Bowie.
Blue Steel.
Bette Davis.
And then boy George George Michael David Bowie.
And it keeps going and what it's doing is taking every customer and conjoining it with every order they
give it is almost multiplying them.
So it's taking the first order which is $99 with an order ID of one and it's sticking that next to Boy
George Order ID one.
George Michael or her ID one.
David Bowie same order blue steel same order Bette Davis same order and then it moves on to the next
order.
So then we go back.
Boy George stuck with the second order second order second order it basically.
Hopefully you can see what's happening and a lot of text to look at but it's taking these two tables
and just jamming every row every possible combination next to each other which is meaningless.
There's no reason to do it but I just wanted to show you it because it's the first and most basic type
of joint.
So don't make much of the fact that you can do it.
It's just good to know that you can.
But we're much more interested in basically whittling this down to the stuff that we care about so the
more.
Maybe the most obvious and meaningful way would be to whittle it down so that we only see where there's
actually overlap.
So where the idea of the customer is the same as the customer ID so this would be a match right.
This is an order placed by Boy George.
But then none of these are actually placed by the customer they're next to.
But then we get down here and we have.
OK here's George Michael with an idea of two and he placed this order.
So how do we whittle it down to that.
And that's what we'll see in the next video.
Sorry for the cliffhanger.