What Is A Join
In the context of SQL, a programming language designed for coders to process information in a database, a Join is a way to connect two or more different tables that share data. One way we can illustrate this is by using the example of artists in a museum; an artist (table 1), may have many art pieces (table 2). Once an artist creates a piece of art, their connection to that art is immutable. However, artists also have names and art styles amongst many other personal traits. If an artist creates a piece of art, does it also take on these attributes? No, the art piece has its own traits such as a name, description, and time of completion. So in the context of a SQL database, how do we make this connection?
ID Association
The conventional way of doing this is to assign an ID number to each instance of an artist or art piece. This is easy to do within a database table because you can assign the row number as a database entry's ID number. But how would we use this to connect artists to their art? The key to doing this is establishing a "has many/belongs to" relationship between the two. Since the relationship between an artist and their art is immutable, we can say that an artist has many art pieces while an art piece can only belong to one artist. Knowing this, we can add a column to the art pieces table that correlates to the ID number of the artist that created it. Now that we've established the visual connection, all we need to do is join the tables using code.
Inner Joins
The most common kind of Join in SQL is called an Inner Join. An inner join connects two tables and returns data that is shared between the two. Currently, in our artist/art example, the only connection we have between the two tables is the artist ID association. When using an inner join though, this information is all we need. Let's say that we have a list of every single art piece on display at a museum. However, this list is totally unorganized and we need to find all the art that is associated with a specific artist. In this table full of every different art piece we can execute this bit of code.
SELECT art_pieces.id, artists.name
FROM art_pieces
INNER JOIN artists ON art_pieces.artist_id = artist.id
What does this do for us? This bit of code finds every single instance in the art_pieces table where the artist_id column of the art_pieces table matches the id column of the artists table. From there it gives us the id of the art piece as well as the name of the associated artist for every matching example. Now that we know where in the table our art pieces are located, we can now pass this information along to someone (or something) else that will sort this out for us.
Outer Joins
The other kind of common join is called an Outer Join. While there are a few different kinds of outer joins, the most used amongst them are the Left Outer Join and Right Outer Join.
Left Join
The left join, like the inner join, pulls information that two tables share. However, it can also retrieves information from one table that is unrelated to the second. Using our museum example, we can set up a third table in our database called section. We can set up a section with attributes like name and sponsor. Since a section can also have many artists, let's add a section_id column to artists so we can make the connection. What we can do with this information is set up a left join that might look something like this.
SELECT sections.name, section.sponsor, artists.name
FROM sections
LEFT JOIN sections ON sections.id = artists.section_id
This gives us all of the queried information in the left table, sections. Meaning that even if a section doesn't have a sponsor, it will still include that table entry in the result as NULL. This could be used to get a complete set of data regarding a museum's sections while only getting the associated data from the artists table.
Right Join
The last of the commonly used joins, the right join, functions in a similar way to the left join. The left and right joins function in a similar way so they also look the same in practice.
SELECT sections.name, sections.sponsor, artists.name, artists.style
FROM sections
RIGHT JOIN sections ON sections.id = artists.id
This join, unlike the left join, extracts information from the right table, artists, that may not have matches in sections. Meaning that any entry where sections does not have a sponsor will now not be included in the resulting join table while any entry in artists that does not have a style will now be featured as NULL in the new table.
Other Joins
There are many other kinds of joins that are utilized by SQL coders such as the full outer join, self join, and cross join. However, these are more niche and require a little more understanding of SQL and its use cases.
Top comments (0)