Of course, we don't need to display all of the columns from the joined table. You can imagine that the joined tables are “linked” to one another with joining conditions: Therefore, it was present in the "virtual table". In our example, the coach table is joined to the virtual table using the coach's ID, which is present in the coach table and in the team table. Important: When we join another table, the join condition should include a column that matches a column from one of the previously joined tables. The columns that belong to the "virtual table" (the join of the tables player and team) are shown in orange the columns from the coach table are shown in blue. So, the final joined table will look like this: player.id Hey, we joined 3 tables in this example query! What happened to the coach table? That's easy! We already have a “virtual table” made of the player and team tables, so it looks like we are left with a join of two tables again – only this time we will join the coach table to the "virtual table". It would look something like this: player.idĪs you can see, this "virtual table" contains all of the columns from both the player and team tables. Okay, but what really happens “under the hood” when two tables are joined? Let's take a closer look at our example.įirst, the JOIN of the player and team tables forms a “virtual table”. We will get the following table as a result: player.name SELECT player.name, team.name, coach.name Now, let's run the query that will list the names of all the players, the name of their team, and the name of their coach: Let's say that we have the following data in our tables: coach If you have trouble remembering the exact syntax for SQL JOINs, make sure to bookmark our amazing SQL JOIN Cheat Sheet! How Joining Multiple Tables Works Likewise, the join between the team and the coach table is ON ach_id = coach.id.
Hence, the first join condition is ON player.team_id = team.id The team_id column in the player table contains the team_id this is the same value as the id column in the team table. To create a join condition, we need both joined tables to have a column that contains the same information. The tables player and team are joined first, then the coach table is joined as well. We have two different JOIN operations here. You can get all of this information using the following query: Let's build a query that will list the names of all players, the name of their team, and the name of their coach. Imagine that you have the following three tables: player, team, and coach. It offers over 90 hands-on exercises that will get you writing many different kinds of JOINs. If you want to master joining tables in SQL, check out our interactive SQL JOINs course. The best way to learn SQL is through practice.
0 kommentar(er)
