6 SQL Joins you MUST know! (Animated + Practice)
Anton Putra
https://www.youtube.com/watch?v=Yh4CrPHVBdE

Transkript (automatisch erstellt)

0:00 If you need to merge two or more datasets in SQL, you can use joins. A JOIN is a SQL instruction that you put in the FROM clause of your query. It is used to identify the tables you are querying
0:13 and how they should be combined. There are multiple ways you can combine datasets so therefore there multiple SQL Joins that you need to learn. Optionally, if you want to practice,
0:24 I’ll explain how you can pull my public Postgres docker image, which already has all the tables and data that we’ll use in this tutorial. But first, let's quickly refresh what primary
0:37 and foreign keys are. Typically in a relational database, data is organized into different tables made of attributes (columns) and records (rows). Now, the primary key in SQL is a single column,
0:50 or sometimes multiple columns, that can uniquely identify a row in a table. This is usually the ID column, which is short for ‘identifier'. Now another column in a table that establishes
1:04 relationship with another table’s primary key via shared values is called a foreign key. Foreign keys are also typically titled IDs but prepended with the name of the referenced table. Now, let's
1:17 go over a real-world example. Let's say you have a customer table. It could contain data from a CRM tool like Salesforce, which includes users who are paid customers. And you may have another event
1:30 table with data from an analytics tool like Mixpanel that tracks all the users' actions. Notice that there's a common column, customer_id, between the two tables. In the Customer Table,
1:44 the customer_id column is the primary key. Now, in the Event table, the customer_id column is the foreign key since that column refers to the customer_id column in the customer table. We
1:57 can use this relationship to join the two tables together to get the customer and events data in one table. Now, for a join operation, technically, you only need two columns of the same type,
2:09 one from each table. It doesn't matter if they are primary or foreign keys. For instance, you can join tables using the 'age' column from both the 'teacher' and 'student' tables. However, you need
2:22 to be very careful when joining tables without foreign keys. For example, you could technically join a 'car' table using the 'model' column and an 'item' table using the 'color' column. They
2:34 have the same type, so it's possible, but in reality, it wouldn't make any sense. Alright, let's go back to the 'customer' and 'event' tables. Each type of join is used to answer a
2:46 specific business question. For example, what if we want to get only active users? In other words, we want to get only users that have performed an action? You would use an Inner Join to join
2:58 the tables together. An inner join combines the columns on a common dimension when possible, and only includes data for the columns that share the same values in the common column. In the example,
3:12 the customer_id would be the common dimension used for the inner join. This is the default type of JOIN in SQL, in fact you do not even need to specify INNER JOIN when writing a query. Only
3:26 writing JOIN is an INNER JOIN. SQL first creates a new table with the columns from both tables you are trying to combine. This mainly happens because we used the asterisk to select all columns.
3:39 However, you can also select only specific columns for the join. It then tries to find values that match between the columns you specify in the ON statement. Putting the table name with a period
3:53 before the column name makes it clear which two columns of the tables SQL will be looking for matches between. SQL then starts with the first value of the specified column in the first
4:06 table (customer.customer_id) and then looks through every value in the specified column of the second table (event.customer_id) for a match. If there is a match it copies the data
4:21 from both the row of the first table and the row of the second table and puts it into the newly created table. SQL will not add any rows that did not have a match. Be sure to know what
4:34 data you want in the final table so that the data left out does not affect your analysis. Another thing to consider is that SQL will join the rows every time there is a match. So if your data in
4:49 the columns you are joining on are not unique you will get duplicate data in the final table. You can use my Docker image and run the same join; you'll get the same result. Now, what if you
5:01 want to get all users from the customer table and only the actions that these users have done? This is the second most common type of JOIN in SQL, where left refers to the first table. Initially,
5:15 SQL will attempt to match all the users from the left table with actions in the right table. This is, again, a conceptual model; in practice, the database performs a more optimized comparison
5:29 that is not visible to the user. However, there is a significant difference in how SQL treats the LEFT table. For any rows in the first table that do not have a match, SQL will still add these rows
5:44 to the new table, placing nulls as the values. Before moving to the next Join, let's slightly modify the source tables. We'll still use the same customer table, but now,
5:55 we'll create a couple more. First of all, we'll dedicate a table to define action types. In the future, you can expand this table to create more actions. The new event_v2 table will contain the
6:09 same information about customer activity, but now we'll use customer_id and action_id foreign keys to establish relationships with the other two tables. It's a common practice in SQL to spread
6:24 data across multiple tables. In this case, we'll use a right join to combine the tables. 'Right' in this context refers to the 'right action' table. So, we aim to get all the action types and only
6:37 associated events. Like a regular join, SQL will first try to find all events and match them with the action id from the right table. Then, since it's a right join, SQL will copy the remaining
6:51 action types from the right table and use Null values for the events. This is one of the rarest types of JOIN in SQL. The reason for this is that any RIGHT JOIN can be rewritten as a LEFT JOIN,
7:05 which is more conventional. Now, for the following example, let’s use two additional source tables. One table is for the teachers and another for the students. As you can see,
7:17 we don’t have any foreign keys, but since the age column in both tables is of integer type, we can use it to join the tables. Let's say you want to have a table that combines data from
7:31 both the teacher and student tables. You would use an Outer Join to merge the tables together. Sometimes it's also referred to as a full join. An outer join combines the columns from all tables
7:44 based on one or more common dimensions, when possible. In this case, that's the age column. It includes all data from all tables. This is the third most common type of JOIN in SQL. This
7:57 query finds matches and adds them to a newly created table, much like a LEFT join. However, after completing the LEFT join of the data, a RIGHT join is essentially performed.
8:09 The following examples will use the same teacher and student tables. The first one is a union. It is the fourth most common type of JOIN
8:19 in SQL. A union does not attach the data from two tables to a single row. Instead, a union stacks two datasets on top of each other
8:29 into a single table. The data types of columns must be the same as well. Here, we'll select a specific age column. When you perform the union operation,
8:39 SQL will take all possible values and deduplicate them. On the other hand, the UNION ALL operator selects fields
8:48 from two or more tables similar to UNION. However, unlike UNION, UNION ALL doesn't ignore duplicate fields. So, you'll get 28 twice in this example.
9:00 And finally, the fifth most common type of JOIN in SQL is a cross join. A cross join does not look for matches between any values in the two datasets. Instead,
9:13 for each row in the first table, every row of the second table will be attached to it and added to the final table one by one.
9:21 In the previous video, we discussed how database sharding is performed, so you may find it interesting as well. Also, if you want to learn more about Kubernetes,
9:31 such as the differences between node port, load balancer, and ingress, you can watch this video. Or maybe you want to learn more about the differences between deployment,
9:42 statefulset, and daemonset. Thank you for watching, and I'll see you in the next video.