All language subtitles for 005 Understanding SQL Relationships, Foreign Keys and Inner Joins_en

So now that we've seen how you would implement all of CRUD using SQL, the next thing to look at is

relationships in SQL.

So firstly I want you to add our pencil record back in

if you have deleted it. And if you have deleted it then you can see this as a challenge.

Pause the video and refer to this table to add back our record of the pencil.

So if you remember the key word is INSERT INTO and then the name of the table and then we specify the

values

which is going to be 2,

Pencil, 0.8.

And now if I hit run then we now have a complete table with two records.

The next thing I'm going to do is I'm going to create a new table and this one is going to be called

orders. This orders table has

a number of fields. It has an id field which is going to be an integer that is not null,

then it'll have a order number field which is going to be an integer and that allow us to keep track of

all of the orders that we ever receive at our shop.

Now the next one is quite interesting.

I'm going to specify something a field called customer_id.

And this is going to be an integer.

But this is the place where I'll store the customer who made this particular order.

So this will be a field that will act as a foreign key to our table which will point to a particular

record in our customers table.

And I'm also going to do that with a product id as well to form a relationship with our products table.

Now as you saw before we can set a primary key for our current table by opening a set of parentheses

and putting in the name of the field that will act as the primary key,

so it will be the field id. But we can also specify a foreign key.

So the foreign key is going to be the key that's going to link all tables together.

This is what's going to establish the relationship. As they show you here they have customers and they

have orders.

And in the orders table if you have a person with id 3 you have a field that is the foreign key.

So for the orders table we know that this order was made by somebody in the persons table with an id

of 3.

So at a later point we can retrieve all of the data in that record that's associated with this order.

And this is how you establish relationships using SQL.

So in our case we can set our customer_id field as a foreign key which references the customers

table,

so this is going to be the name the table, and then inside some parentheses is going to be the name of

the field inside the customers table

that is the primary key. That's going to be the id field.

And finally we're going to do the same for our other field as well which is the product id field and

this is going to reference the products table again with its primary field which is called id. If the

concept of primary key and foreign key are foreign to you

then I recommend having a read of this documentation on w3schools to get to grips with the idea of

how you establish relationships using SQL via these keys.

So now if I hit run, I get an error and it tells me that I accidentally put in a closing curly brace

instead of a round bracket.

So now that's fixed.

Let's hit run again.

And we now have a orders table.

Now again orders table is completely empty but if we right click on it and we click show schema then

you can see it has only four fields.

First one the id is the primary key for this orders table and then two other fields act as foreign keys

which link this table orders with the customers table as well as the products table.

So now I'm going to create my first order record inside my orders table. It'll have an id of 1 an order

number of 4362 and the customer who bought it had an id in the customers table of 2

and the product had an id of 1.

If you take a look, in our customers table the customer with an id of 2 is me,

Angela Yu at 12 Sunset Drive and the product with an id of 1 is a Pen.

By establishing those relationships I can later create a much larger table where I join together all

of the relevant records and all of the useful columns from all three tables that I need.

So let me show you how that works by creating the first record.

So we'll use INSERT INTO the orders table and the values we want to inserts are 1 as the order id

the order number is 4362, the customer id is 2 and the product id is 1.

So now I'm going to go ahead and create my first order that's gone through.

So basically Angela Yu has bought a pencil that's all this represents.

But now we get to use something that's really powerful from SQL which is a join. So we can join our

tables together using the key word of join.

Now there's a whole bunch of different Joins left join, right join, full join.

But the most commonly used one is the inner join and that's the one that we're going to use.

We're going to join together the parts of our tables where a particular find key matches.

This is the syntax.

And if I copied over to our sqlliteonline.com, we're going to select the order number from the orders

table.

So we would say something like orders.the name of the field.

In our case our orders table is lowercase, orders.order_number.

The other field that we want is from our customers table and that's the first name, last name and address.

So we would write customers.first_name customers.last_name and customers

.address. Those are all the fields that we want to join together in a new table we're going to

create and it's going to be from the foreign keys inside our orders table.

So that's what we'll say here.

From the orders table is where you'll find this particular foreign key match.

So the next thing is inner join and the table we want to join is our customers table. And after the key

word on is going to be the fields that will match. It's going to be the foreign key in our orders table

that's called customer_id and it's going to match with the primary key on our customers

table which is called simply id.

Now if I hit run you get a join table where you've got the order number, the first name, last name, address

and you can see that these fields come from different tables.

But we've now managed to search through all of our tables and records and we've managed to match up

the orders and join them in a new table which is way more useful for us.

So for example if we wanted to dispatch order number 4362 then we'll know who is going to be sent

to and what their address is. Next

ss it going to be a challenge for you.

You can see that currently we only have the order joined with the customer. As a challenge

I want you to join the order with the product that the order is relating to. Pause the video now and

try to change the code so that you can complete this challenge.

OK so in this case we're no longer joining the orders table with the customers table.

Instead we're joining the orders table with the products table and the fields that we're interested

in is the order number as well as some of the fields from our products table.

What does our product table have?

Well it's got the name, the price and stock quantity.

So let's go and join those fields together.

It'll be products.name products.price products.stock.

And we're joining from the foreign keys in the orders table, we're performing a inner join and we're

joining it to the products table and the fields that have to match is orders.product_

id and in the products table the field is simply called id.

So products with an 's' is the name of our table

as you can see here and it has a field called id which will match with the foreign key in our orders

table called product id.

So now if we hit run

you can see we get the order number but we now join the orders table with the products table and we

now have a brand new table that's created based off this inner join.

So that is a little bit on the magic of SQL database relationships.

And you can see how flexible and how powerful these relationships are when you are searching through

your database and you're trying to assemble all of your data from various different tables.

Now if you had any problems following along with the tutorial so far or if you just want to have a copy

of this completed table then in the resources for this lesson there is a link to a SQL online version

where it contains everything that we've done so far including all of the data in our orders, customers

and products tables.

So you can try out different bits of code from w3schools or anywhere else on it and have a play around

with SQL lite.