Welcome back in the previous video we've created a login table now that we have these two tables together.
I want to show you the real power of relational databases.
Going back to our drawing the most important part of relational databases is this idea of schemas, of
having tables that are just concerned about storing information specific to them such as users, tweets
and following and connecting them through a primary key and a foreign key. I'm going to show you how
to do that.
Well we've already done it.
But how do we grab information from here. If we go back to our table
we remember that we ran our query,
we created the Create login table here and we created a name variable that when we planned this was
going to match whatever the users table had and we can use something called JOINs.
now with our queries. And this is a new word in SQL and probably one of the most powerful, because we
don't necessarily want to have one massive table with hundreds of columns, hundreds of rows.
Databases aren't designed to just have- just like an Excel sheet tons of rows and columns and have all
that information in one place because it's not efficient.
It'll be a lot slower,
it'll be really, really hard to manage.
And the bigger and bigger your company grows it's going to be really, really tough.
A good way to design a database is to have these small little tables that are just concerned, just like
separation of concerns, concerned with their minimal needed information.
And if something else is needed we can just join tables together to grab information.
Let me show you what I mean.
We have over here our test database and let's do \d to see.
All right we have three files here.
We have the login and users which we created.
But what is the login ID sequence.
Remember how we created a primary key. The way relational databases work is when you create a primary
key it creates another file that contains this primary key. And you can see that the type is sequence.
Now this is a file that most likely we won't access but internally Postgres is going to make sure because
we set this primary key it's gonna be really, really fast to grab data from the login table when we
request it.
But let's say I wanted to get information from login and users. Perhaps I want to get Sally's information
but I need her birthday, score, age as well as her secret. By using JOINs we can do this.
I can say SELECT star,
So everything FROM users.
And now if I do JOIN, I can say another table name that I want to join with the users.
In our case login and we have to say ON what are we going to join or not.
How is it going to know what matches with Login.
Because if I go back here, how is the database going to know that I want to match the first line with
Andrei,
the second line with Sally, a third line with John. The ON key word allows us to make sure that we match
on something.
In our case it's the name their primary key here and the foreign key here.
So let's do that ON users
.name equals login.name
semicolon.
Let's do that.
And look at that.
We have name, age, birthday, score but also ID, secret, name altogether.
We've just joined these values and they all match because we've done login.name and users.name, and
with JOINs
comes great power now, we can have small little tables based on what we actually need and any time something
specific is needed we just join tables and we can do multiple tables, multiple JOINs in order to select
the information that we need.
All right I think we've done a really really good job understanding SQL and the power of relational
databases.
I'll see you in the next one.
And good job getting this far, bye-bye.