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.
We haven't worked with our data, so let's try it now.
So back over in Cloud nine, let's start with a simple question.
Let's look at our customers.
Well, actually, let's 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 by Boy George.
So how do I do that?
I want 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 star from customers where and then we would do something like where last name
equals George.
So we get Boy George.
George at gmail.com and his email excuse me, his ID is one.
So then we take that and we go to our orders table and we try and find where is in the order table is
customer ID referencing one.
So we're just going to do a select star from customers where excuse me, select star 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.
We could use a subquery.
If you'd like to try and do that, you can take a moment to figure that out, but I'm just going to
write it down here.
We would do something like this.
So the two things we just did, we let's just copy this one.
We did this right here, select star from orders where customer ID is one.
And then we also had.
Select start from customers.
So to put them together.
What we basically want to do is find the the ID of the customer where last name is George, then plug
that in to here.
So rather than hard coded as one so it would 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.
Okay.
So let's take a look.
We need to fix one thing.
Actually select star from orders where customer ID equals the result of this.
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 only selecting ID and it gives us one.
So you can think of this whole thing is 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.
If I go up, let's do select star 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 joints.
So joints allow us to accomplish that.
The whole point of joints, which is sort of this buzzworthy, maybe intimidating, mysterious thing
to some of you.
The whole point of it is that it takes two tables and we can conjoin them in a couple of different configurations.
So we'll talk about what those are.
That's the next few videos, but we can conjoin 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.
It 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?
I'm going to 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 1 to 5, 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.
Think of it as 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.
Order ID one.
David Bowie.
Same order.
Blue Steel, the 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.
Basically.
Hopefully you can see what's happening.
It's 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 join.
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 this 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 there next to.
But then we get down here and we have 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.