1. All about joins
Welcome. My name is Amy McCarty, I am a data analyst, and I work in SQL to gather data for analysis to drive business insight. Sometimes this data comes from multiple sources, and sometimes there's a lot. In this course, you will learn to elegantly and efficiently combine data.
2. Course overview
We will cover choices in query structure, including join types, subqueries, and temporary tables.
We will discuss limiting and aggregating data from large tables.
We will cover database storage properties and how these characteristics impact the available optimization tools.
Finally, we will view each concept through the lens of the query planner.
3. Query planner
Imagine you are at a restaurant. Your order is the query or SQL instructions. You SELECT your food FROM the menu. The waiter takes the request to the kitchen.
The cooks determine how to best execute it. The cooks create the actual steps that return food.
They transform your order into a meal that arrives quickly with the entree ready before the dessert but after the appetizer. The cooks create the optimal execution plan to translate your query into a meal.
4. Query planner
Sometimes you order an entree then drink, and have the drink arrive first. Similarly, queries also are not processed in the given, written order.
SELECT statements provide a list of ingredients. FROM statements provide the directions to these ingredients. We will explore how different ordering and structures impact the query plan and execution.
5. About joins
We will start with joins. Joins are one way to combine data from different tables.
6. About joins
A table on sales orders may contain a customer number, but the detailed customer information is stored in a separate customer information LOOKUP table.
Joins are a way to combine these tables, combining data to build a more comprehensive dataset.
Joins fall into two categories: inner and outer. We will explore inner joins first.
7. Inner joins
An inner join uses a common column to link two tables. The final result has records only where this common column exists in both tables.
This query SELECTs all the data FROM the athletes table on the left and JOINs it to the countries table on the right using the common "Country" column. Notice that the Country column is duplicated since it is returned from each table.
8. Inner joins
The final table has three records. It is missing the bolded entries from each source table because Mexico and Brazil only exist in the athletes and countries tables, respectively.
9. USING inner joins
Since the linking column has the same name in each table, the join condition can be simplified with the USING statement.
While this query executes the same as the prior INNER JOIN, it has the advantage that the joining column is only returned one time.
10. USING inner joins
As before, the final table is missing the bolded entries. If you wanted to see every athlete, including Javier from Mexico, and every country, including Brazil, an inner join would not be a good choice.
11. Left outer join
Instead, you can choose a LEFT JOIN, the most common join type.
Left joining countries to the athletes table, or left table, returns a results set that includes all the athletes, even the previously excluded Javier. The country information for Javier is blank since Mexico is not in the countries table.
12. Left outer join
Brazil from the countries table is still not included, but you now have a results set that includes all the athletes.
13. Right outer join
Right joining countries to the athletes table returns a results set that instead includes all the countries, even the previously excluded Brazil. Brazil is not in the athletes table, so athlete information is blank.
14. Right outer join
Javier from the athletes table is no longer included. You now have a results set that includes all of the countries.
15. Full outer join
The FULL OUTER JOIN returns all records from both tables.
Recall that the query planner is similar to restaurant cooks translating an order into a meal.
Full outer joins are telling your waiter that you need tea after the appetizer but before the entree. They constrain the cooks' ability to gather ingredients in the optimal order.
Full outer joins put an order constraint on the query planner that other join types do not. Using them may slow the query execution.
16. Let's practice!
Now it is time for you to practice the different joins.