Zum Inhalt springen
L

Das Video kommt von YouTube: erst beim Abspielen verbindet sich die Seite mit YouTube (Google).

6 SQL Joins you MUST know! (Animated + Practice)

Anton Putra9:47 627.967 Aufrufe veröffentlicht Auf YouTube

Das Wichtigste aus dem Video

Tipp auf eine Zeit – das Video springt genau dorthin.

Transkriptautomatisch erstellt · 47 Zeilen
Herunterladen
  1. 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
  2. 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,
  3. 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
  4. 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,
  5. 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
  6. 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
  7. 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
  8. 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,
  9. 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
  10. 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,
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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,
  16. 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
  17. 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.
  18. 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
  19. 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
  20. 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
  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
  22. 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
  23. 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
  24. 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,
  25. 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
  26. 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
  27. 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,
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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,
  33. 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,
  34. 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
  35. 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
  36. 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
  37. 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.
  38. 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
  39. 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
  40. 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,
  41. SQL will take all possible values and deduplicate them. On the other hand, the UNION ALL operator selects fields
  42. 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.
  43. 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,
  44. 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.
  45. 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,
  46. 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,
  47. statefulset, and daemonset. Thank you for watching, and I'll see you in the next video.

Zum Nachlesen

Join (SQL)Ein SQL-Join (deutsch: Verbund) bildet aus den Datensätzen zweier Tabellen einer relationalen Datenbank eine Ergebnistabelle, deren Datensätze Attribute beider … Relationale AlgebraIn der Theorie der Datenbanken versteht man unter einer relationalen Algebra oder Relationenalgebra eine Menge von Operationen zur Manipulation von … Selektivität (Informatik)Selektivität ist ein Maß, das in der Informatik bei Datenbankabfragen auf Datenbanktabellen in relationalen Datenbankensystemen gebraucht wird; sie bestimmt … Relation (Datenbank)Eine Relation besteht aus Tupeln, jedes Tupel wird durch Attribute beschrieben, die den Typ (mögliche Attributwerte) festlegen und mit einem Attributnamen …