Use the JOIN command to ask questions that involve more than one table.
- [Instructor] One advantage of working with databases, rather than just spreadsheets, is that it can be easier to use data for more than one table. Of course, you can work with data across many spreadsheets using a lookup function, and in a database, to accomplish the same thing, we use the JOIN keyword. There are a few kinds of JOINs, actually, but we'll just look at one in this video. The idea behind a JOIN is that we're asking for pieces of data, or records, that somehow associate with each other, that connect in some way, with some common piece of information that we tell the database to match up from one table to the next.
In our sample database here, the common element is the abbreviation for the state name. In one table I have information about some people, and in another table, called states, I have some information about states in the U.S. I have their name, their abbreviation, and what census region and division they're part of. We could perhaps use this information to print up custom shirts for people who participated in our contest, with mountains on the shirt for people in the mountain division, and maybe a nice lighthouse logo for the New Englanders.
Of course, what you do with your data will be different, but everybody likes a free T-shirt. Let's take a quick look at the states table so we know what we're working with. In the browse data tab, I'll choose states from the table dropdown. Here's the abbreviation of a state, the full name, and the census region and division. I have 50 records here, one for each of the 50 states.
To start out with, let's get a list of the first names from the people table, and the abbreviation of the state they're from. I'll write SELECT first_name, state FROM people. Alright, and then I'll connect that to the states table, and associate the census division to each record as well. To do that, I'll use the JOIN keyword. I'll make a new clause here so we can see it better.
After the FROM statement, I'll write JOIN states. That tells the database that we're going to be associating the states table in this statement, and that we'll be selecting from people JOIN to states. And if I run this, it will work, or more specifically, it won't cause an error. But it'll return us 50,000 records, because we haven't been specific enough, so we'll get back a copy of all the records in the states table, for each record in the people table.
Not too helpful. To be more specific, we need to tell the database what pieces of information we want to associate from one table to the next. To do that I'll add ON people.state =states.state_abbreviation. This dot notation with a table and a period followed by the field name, helps us avoid confusion by telling the database which table to use to find each field.
Nothing in my database has the same field name across different tables, but if it did I would definitely need to be specific, or I'd get an error. So it's a good practice to just use this notation all the time. This statement now says, whenever you return a record from the people table for the associated record in the states table, use the state value in the people table and match it to the state abbreviation value in the states table. These two values need to be identical, otherwise the database won't be able to match them up.
Okay, we have a way of looking up values, so let's add one of the values from the states table to our results here, so we can see that it's working the way we expect. I'll add states.division to the SELECT clause and to keep with best practices, I'll add the people table name to the front of the other fields. People.state, and people.first_name. Again, in this case it would work without them, but it's a good idea to be thorough and specific.
And there we go. We have two columns from the people table, and one column from the states table. Or, if we wanted to have some fun, we could just use a star operator to see all of the values, every record from people joined up with its associated record from the states table. Because this is one dataset that gets returned, we can treat it like it's all one table, too, and make queries based on any of the columns here. So I could look for people whose names start with J and who are in the south.
I'll add WHERE people.first_name LIKE 'j%' AND states.region='South' It looks like we have 65 people whose names start with J and who are in a state that's considered to be in the southern region. You can join across more than two tables, but that's not something we're going to do here.
It works the same, though, and you just need to add another JOIN clause before your WHERE clause.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.