Tip:
Highlight text to annotate it
X
Let's move on to a new topic that we're promptly going to forget.
This is called Joins.
So this is a type of SQL query that you can use that involves multiple tables.
Remember we have our Link table.
We've been working with that this entire lecture.
It looks something like this.
It's got the columns--ID and Votes and the User ID Submitted,
Data, Title, URL.
So let's make up some values for these.
Now, what I want to talk about right now is this User ID column.
Remember I mentioned before
that if we had another table called Users
that looks something like this--
it's got a column for ID and a column for Name and Password and Date--
the User ID in the Links table
refers to the ID column of the User table.
This should always match up,
because you want everything in your system to be consistent.
So for every unique User ID in the Links table,
there should be a valid user in the User table.
Now, one of the things we can do in most SQL databases is something called a Join,
which is a SQL statement that involves two tables.
So we have a basic SQL statement that looks something like this.
So select from the Link table,
where user ID equals 22.
So that would return all of the links submitted by this user,
assuming there's more than one.
What if we didn't know the user's ID?
What if instead we wanted to select from the link table
all of the links submitted by users with the name Spez?
Well, there's a couple of ways you could do that.
You could first do a lookup for
what is the user ID of the person named Spez,
grab this user ID, then run the SQL query.
So you could do two queries. It might look something like this.
So I could run one query to get the ID from the user table
for the user whose name equals Spez,
and then we can use that ID in a second query.
But there's another way of doing this query.
I could, instead of running two queries,
combine this into one query.
So instead of From Link, we can change this to Link, User--
which will scan both tables.
And we don't want to get all of the attributes.
We want to preserve the same results from the query,
so we don't want to select * from link, user--
we want to select link.*,
which will return just the columns from the link table.
Then we want to change our Where clause
to link these two tables.
So where user_id=user.id.
I could have also said link.user_id
so we include the table names in all of our properties.
That will make things more clear.
I need to add another clause to this.
And user.name = Spez.
So what does this do?
Select all of the columns from link--link.*--
from the link table and the user table,
where user.name=Spez
and link.userid=user.id.
And what this does is what's called a Join Query.
And your SQL engine will decide
which of these tables to scan first,
and in this case, it will probably do the user table first.
So it will find the username Spez, and then it will find all the links
whose user_id property
matches the ID field in the User table.
So this is a handy thing.
Now, the reason I said we were going to learn something we were promptly going to forget
is because we don't use joins very often
when writing web software.
It's something you'll see in almost every SQL tutorial,
but for reasons we'll explain later in this lecture,
joins don't work very well for the types of problems we're going to be solving
writing web stuff.
But you should be familiar with them; you should know what they are.
You may be writing something that requires this,
but they're often--well, as I said,
we'll get into this later in the lecture--why we don't want to use joins.
Let's move on to something new.