Many times you need to use information from multiple tables to produce a report or answer a question. For example, how much of a product have we sold?
We need fields from two tables to answer this.
To make this work, we need a SQL join. Joins are not difficult, as long as you understand what you’re trying to accomplish.
Joins require a common key that is shared between tables, in this case product_id. Once you know the fields you want to SELECT and the common key field, you are ready to create your query.
SELECT products.ProductName, Quantity
FROM Products
INNER JOIN "Order Details"
ON Products.ProductID = "Order Details".ProductID
LIMIT 5| ProductName | Quantity |
|---|---|
| Queso Cabrales | 12 |
| Singaporean Hokkien Fried Mee | 10 |
| Mozzarella di Giovanni | 5 |
| Tofu | 9 |
| Manjimup Dried Apples | 40 |
Because order details has a two word name, you MUST use quotes when referring to it (“Order Details”). Otherwise you will encounter an error. Sometimes you encounter bad table or field names that are hard to work with. You can always rename any variable you want using an alias with AS. Here is the same query using AS od to mean the order details table. Once you set the alias one time, you can use it any time later in the query.
SELECT products.ProductName, Quantity
FROM Products
INNER JOIN "Order Details" AS od
ON Products.ProductID = od.ProductID
LIMIT 5Here is a breakdown of each part of the JOIN:
products.ProductName.This selects the ProductName column from the products table. The products. prefix is used to explicitly state which table the field comes from. Using the table prefix is good practice, especially when you have columns with the same name in different tables like we have here. If you don’t use it, you will encounter “ambigious column” errors, because SQL won’t know which table you want to pull the data from if the field appears in two tables used in your JOIN.
INNER JOIN “Order Details”.This is the INNER JOIN clause. It combines rows from the products table with the “Order Details” table. An INNER JOIN only returns rows where there is a match in both tables based on the shared key field. There are other types of JOINS, but INNER JOIN is the only one we will use.
ON products.productid = od.productid. This is the ON clause, which explicitly states the shared the key field. It tells the database to link rows from the products table to rows from order details where the productid’s match.
A join can have any number of tables. To join more tables, just keep adding INNER JOIN and ON clauses. Here, I join our previous query to the orders table to also add the freight weight for the orders. This works because both “Order Details” and orders share a common ID, in this case orderid
SELECT orders.orderid, products.ProductName, Quantity, Freight
FROM Products
INNER JOIN "Order Details" AS od
ON Products.ProductID = od.ProductID
INNER JOIN orders
ON od.orderid = orders.orderid
LIMIT 5| OrderID | ProductName | Quantity | Freight |
|---|---|---|---|
| 10248 | Queso Cabrales | 12 | 16.75 |
| 10248 | Singaporean Hokkien Fried Mee | 10 | 16.75 |
| 10248 | Mozzarella di Giovanni | 5 | 16.75 |
| 10249 | Tofu | 9 | 22.25 |
| 10249 | Manjimup Dried Apples | 40 | 22.25 |
Bar charts (geom_bar()) like we used last week are great for counting qualitative data (names, cities etc.). Quantitative data though (like the freight and quantity above) can be shown effectively using a geom_boxplot().
Boxplots are a nice go-to for quantitative data. Notice that the only thing that is really different in the code is geom_boxplot() instead of geom_bar(). They are sort of demanding on your audience though. People need to know some statistics to understand boxplots. Things like interquartile range, medians, etc. Plus with grouped data it can be tough to explain whether and how groups differ.
Here are a couple examples using the double join query above, but limiting the results to only include varieties of Lager we have sold with (WHERE ProductName LIKE “%lager%”)
ggplot(data = myquery1,
aes(Quantity)) +
geom_boxplot() +
labs(title = "Boxplot showing quantities for all lager products")
ggplot(data = myquery1,
aes(x = Quantity, y = ProductName)) +
geom_boxplot() +
labs(title = "Distinct boxplots for each Lager variety")
A boxplot displays a five-number summary of data:
Finally, here is a nice trick you can do with a geom_bar(). A “stacked bar” chart lets you show two qualitative variables on the same bar chart. Anytime you have two text variables you want to show, a stacked bar can be a good option.
Here’s an example using data from the suppliers table. It uses a “fill” variable in the aesthetic to show how many suppliers are in each region and country in Asia.
ggplot(data = myquery3,
aes(y = Country, fill = Region)) +
geom_bar() +
labs(title = "Stacked bar showing both the region and country for each supplier.")
Now that you’ve had a chance to read about JOINS in SQL, open your 393_practice2.qmd file to start practicing on your own. Remember that your visualizations for your report should use concepts from the practice, but show how you have learned to extend the practices, not merely copy them.