All right.
So we have our two tables set up.
We have customers, we have orders.
We've got a primary key in each and then a foreign key customer ID references the customer's table ID
inside of orders.
Now, what I want to do is show you how we can work with these tables together.
And we're going to start simple.
I want to write a query that selects all of the orders placed by Boy George.
So Boy George right there, let's say.
I don't know the ID, I don't know Boy George's ID, So if you think you remember it, I mean, it's
only one, two, three, four or five.
I'm pretty sure it's one or two.
But let's ignore the fact that we know it because it's such a simple data set.
We can memorize it, but let's pretend we don't.
Well, there's a couple of options I'm going to show you the simplest, most naive one first, which
is to write two separate queries.
Basically, we're going to try and find the ID of Boy George from the table customers first, and then
after that we'll take that ID and try and find the orders that have that as their customer ID So that
would look something like this.
Our first query would be select and we could do select ID from customers.
Where.
And then we can do let's just do it based off of last name.
Technically, of course, we could have multiple people at the last name of George.
But just to keep our logic short, I'll say where last name is exactly equal to George.
I won't worry about first name, although it probably would be more unique to say where first name is
Boy, But we'll leave that alone so I could run this query first and stay with me here.
This is the basic naive version where I run that and we get back an ID of one.
All right, So then I can take that number one and go select.
Let's just select star from orders.
Where and then what am I looking for?
Where ID is equal to one because we just found out Boy George's ID is one.
So select orders where customer ID is one and that can be a little annoying.
You can mess that up easily because we've got so many IDs, right?
We have the ID on the customer's table, the actual ID on the order table, and the customer ID on the
orders table.
So I want to say where the customer ID in this order's table is set to one which is Boy George's ID,
We just found that out.
So then I would run this OC and these are the orders belonging to Boy George and I can do whatever I
want.
I can sum them up, I can average whatever, but this is clunky and this is only really going to work
if I am looking for one customer.
But what if I wanted to see every single customer next to their orders?
So something like let's just say we see the order date or the order amount and then I want to see the
first name right next to one another.
So we would see like 1050, and then next to that we'd see George and then we see 450.
And next to that we see whoever else we have in there.
I guess if this is first name, this would be boy and this would be David.
But my point is, what if I wanted to see all that information together?
This is not going to get me there.
This will work to get me information about one particular user.
And there is another option.
Instead of doing two separate queries, I could use a sub query.
If you think back to how that works.
Where we use parentheses, I could combine these two and say select ID from customers where the last
name is George and use that as a subquery.
So I would do something like this.
Select star from orders where the customer ID is equal to the result of this other query.
So when we have those perrins, my SCL will run this first and that's going to evaluate to a number
which we've already seen.
It turns into one and then this query runs.
So this is another way of, of running it.
That's or of writing it that is more succinct and we can run it in one step and we should still get
the same two orders that have a customer ID of one, which is Boy George's customer ID.
So there's that.
That's simple.
I guess that's cool.
But we're still not seeing any information from the customers table.
Alongside the information from the orders table.
We have to know that customer ID here, one belongs to Boy George.
But as I was getting it, it would be nice to have that information joined together.
And the focus of the rest of the section is all about joints, which is a particular clause in my SQL.
It's an actual word we write join.
We'll get to that shortly in the next video.
But what I want to show at the end of this video is one type of a SQL join that is particularly, I
won't say useless, but very much less so useful than the other ones.
I'm going to show you this type of joint is something called a cross join.
And it's all I think it's also called a Cartesian join.
A cross join is simply going to take every single row from customers and combine it with every single
row from orders.
We'll get every combination possible.
There's no logic really at all.
It is a dumb join meaning it's not going to take a boy George and combine him with his actual orders.
Instead, it's just going to combine everything it looks like this select star from and then customers
comma orders.
So two different tables that I'm selecting Star from when I run this, this is the end result.
It's a combination of every single customer with every single order.
So you'll see we have, I think I have six rows for orders and I have five rows for customers.
We get 30 total rows.
So take a look here.
In this first chunk, we have every user, Betty Davis or every customer Betty Davis, Blue Steel,
David Bowie, George Michael, Boy George.
And then it repeats again.
And then it repeats again and it repeats again.
But what's different is if you look at the right half of this result, this is the first order order
ID of one.
So we have a combination of Betty Davis with order ID one, Blue Steel Order ID one, David Bowie Order
ID one.
Again, this is not useful.
This doesn't tell us anything, but it is something we can do and it is technically a join.
What we're going to see in the next video is how we can make joints that help us.
That actually makes sense where we join together.
Bette Davis Only with the orders Bette Davis made or David Bowie with the orders David Bowie made,
or if we have anybody who didn't make any orders, then they won't be joined with any orders.
But right now, you know, if we look at customer ID, it's one, two, five, three.
So for the person with ID of four is my cat Blue Steel.
She never made any orders, but she's still sitting right alongside this order here.
And this order over here and this order here.
It makes no sense.
But that's a cross join.
It just takes every bit of information from both tables that we provided.
We just separate them with commas and it basically cross multiplies them.
It says, I'll take this first order and set that alongside the first user.
Here's the same first order, second user, first order, third user, and it just makes these combinations.
So that's technically a join.
It's a simple one.
Next up, we're going to learn about useful joins Hang in there.
It's a lot but it's also really powerful technique.