MySQL: Joins

Name: MySQL: Joins
Author: Kati Gordon

How to effectively use joins.

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

In this lesson of advanced SQL, we will discuss using joins. Joins allow you to merge data across tables. The Venn diagram shows us how a left join works. Left join says that I want you to pull rows from table A that have a corresponding value in table B. Now, the way to join those is using a key. Let's look at our table schema to identify the key we need to use. If we want to search out our film and inventory tables, we can join those using our film_id. In our film table, it's a primary key, while in our inventory table, it's a foreign key. Let's see what that statement looks like in our pgAdmin tool. So here is our outer left join in which we're selecting the film_id, the title, and the inventory_id from our film table by left joining it to our inventory table using the film_id key. Let's run that query and see what we get back. Great, so now our results show our film, and title, and inventory_id based on that outer left join. The next type of join we'll discuss is the inner join. For each row in the table A, inner join compares the value in the PKA column with the value and the FKA column of every row in the table B. So, if these values are equal, then a new row is created in the result set, joining the columns from both tables. In cases where the values are not equal, the inner join will ignore them and move to the next row. Let's see how to join three tables using inner join. Here we're selecting data that exists in both the customer payment, as well as the staff table. So we're looking for the customer_id, the customer's first_name, the customer's last_name, along with the staff's first_name, staff's last_name, and the amount spent by the customer along with the payment date. We're gonna look at the customer table, the payment table, and the staff table. Our primary keys will be customer_id on the customer table and staff_id on the staff table. We'll use those to create our join. You see down here after identifying the data elements, we put our FROM customer table and our JOINs below that, INNER JOIN payment. We're using our aliases, so we're using p. We're joining customer_id from the payment table to the customer_id on the customer table. And then we're gonna join the staff_id on the staff table to the staff_id on the payment table. Let's run this query and see what we get back. Okay, so we get our data elements back. And again, it's looking only at data that exists in all three of those tables. So that's using joins to merge data across tables. In our next lesson, we'll look at using aggregate functions.

Advanced Provider : Kati Gordon 4 Lessons · 13m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

This module discusses using joins in PostgreSQL. Joins allow you to merge data across tables. You’ll also learn about aggregate functions, which perform a calculation on a set of rows and return a single row. Then, you’ll learn how to use rollouts and cubes to create subtotals. Cubes and rollups allow you to create subtotals for multiple group sets rather than just one. This module explains this concept in depth. Finally, this module explores the fetch first function.

What You'll Learn

Merge data across tables using joins
Apply aggregate functions to perform a calculation on a set of rows and return a single row
Create subtotals for multiple group sets using rollups and cubes
Distinguish between the cube and rollup operations
Use the fetch first function to limit returned rows

Key Takeaways

Joins allow you to merge data across tables.
Aggregate functions perform a calculation on a set of rows and return a single row.
Cubes and rollups allow you to create subtotals for multiple group sets rather than just one.
The module also explores the fetch first function.

Frequently Asked Questions

What topics does this module cover?

It covers using joins to merge data across tables, aggregate functions, using rollups and cubes to create subtotals, and the fetch first function.

What is the difference between cube and rollup as taught here?

Both cubes and rollups allow you to create subtotals for multiple group sets rather than just one, and the module explains this concept in depth; understanding the difference between cube and rollup is one of its stated learning objectives.

What do aggregate functions do?

According to the module, aggregate functions perform a calculation on a set of rows and return a single row.

What skills are associated with this course?

The associated skills include Database Programming, MySQL, PL/pgSQL, PostGIS, PostgreSQL, and SQL (Programming Language).

What lessons are included?

The lessons are Using Joins, Aggregate Functions, Using Rollups & Cubes to Create Subtotals, and Fetch First.