Learn how to join data from two or more tables in MySQL. Joins require a common field shared by both tables. Joins can also be chained in order to pull from three or more tables in a single query.
- [Instructor] Now that all of our data is loaded we need to find the average accident severity for different types of motorcycles. Let's think about what we want to end up with. We want to show the vehicle type, the average accident severity, and the number of accidents of the vehicle type. That data is stored in all three accidents tables, so we'll need to link them up along their matching fields. Accidents 2015 and vehicles 2015 share the accident index field. And vehicles 2015 and vehicle type share the vehicle code field. The process of matching these tables and displaying the matched data in a single query is called joining.
Lets open up an SQL tab and put this into practice. We need to start by selecting the columns I want to display. I want to show the vehicle type, dot v type column. That's the text name of the vehicle. I want to show the average accident severity from the accidents 2015 table. And I want to show the number of accidents in the accidents 2015 table. Count will show the number of values in that column.
I'm going to select from the accidents 2015 table and then we'll need to join the other two tables to accidents 2015. And we do that by using join clause here. And I will join vehicles 2015. And then we need to provide the columns we want to join on. We'll join on accidents 2015 dot accident index equal to vehicles 2015 dot accident index. As join clause will only return rows where the columns in those two tables are identical.
We also need to join the vehicle type table. So I'll add a second join clause here, and join vehicle type on the columns vehicles 2015 dot vehicle type equal to vehicle type dot vcode. Now since this is the first time we've drawn data from multiple table, you might notice I'm referring to columns by both their table name and their column name with a dot in between. MySQL is pretty smart about figuring out what column you're referring to with all of the column names and your query are unique.
But if it can't figure it out, you'll get an ambiguous reference error. Best practice is always to be explicit about which table you're referring to. Now, because we don't want all vehicles in our query, only motorcycles, we need to add a where clause to limit our select statement. So we only want rows, where vehicle type dot vtype contains the word motorcycle. We use the like operative here. The like operator will only select rows where the value in the column we provide matches a string we provide.
So I will provide motorcycle. I'll put it in quotes to indicate that it's a string. And because we don't know what characters, if any, will appear before or after the word motorcycle we need to provide a wild card, to match those characters. In fact, we don't even know whether the m in motorcycle will be capitalized or not. So I will replace the m with the wild card. The percent sign wild card in MySQL just means match any number of characters here. So this where clause will return any rows where the vehicle type column contains the word motorcycle.
The average and count columns in our select statement are called aggregate values. Aggregate values are any values where you're combining the data from multiple row into one value. Here we're saying, show number and average severity, by vehicle type. So we need to include syntax to tell MySQL that using a group by clause. And a group by vehicle type dot vtype. If you use aggregate columns and don't have a group by clause, MySQL will throw an error. It's also worth noting that all of my new lines and tabs here are purely to make the query more readable.
MySQL doesn't care about the middle, remove them when you actually run the query. In fact, the MySQL workbench has a button to make query's more readable. And just adds a bunch of white space. And this is an identical query to what I just had. Now that my queries done, I will click the lightening bolt to execute it. This query seems to be taking a long time. Slow queries are a common problem in MySQL. In the next video, I'll show you how to investigate slow queries and one method for fixing them.
- Strengths and weaknesses of MySQL
- Creating a database
- Joining data sets
- Integrating Python with MySQL
- Searching a database
- CRUD operations
- Performing calculations