Using Inner Joins in SQL

Sometimes, arrays and objects just aren't what you need to store data. If you find yourself in such a situation, it might be time to consider using a database management system, such as MySql. MySql is a database management system that is accessed using a standardized language, Structured Query Language. MySql is relational, meaning that it stores data in several tables, instead of one big table. This allows for a more flexible programming environment, where users are able to "make their own rules" about how the data in these tables relate to each other. In this post, I'm going to talk a little bit about using a feature of SQL known as "joins" to better visualize (and utilize!) these relationships. I'm also going to assume that you have a basic understanding of SQL, at least to the point that you can create tables. Lets get started!

Why?

As previously mentioned, MySql is relational, meaning that data is stored in different tables. If we want to link these tables together, we need to use something known as "foreign key columns". These are columns that tell one table exactly where to reference another table for the associated value they're looking for. Storing data this way means that values only have to be updated once. Lets assume we've got two tables, one that has some information about hospitals, and another that has some information about doctors. Lets look at the (extremely simplified) schema for these tables might look something like this, as well as the data contained in each:

In a practical application, it would be assumed that each table would have several more rows and columns in addition to the ones listed here. If we wanted to know what hospital each doctor worked at, we simply use the link we've created between doctors and hospitals using the id_hospital variable. To accomplish this, we can use an inner join!

Inner Joins

In MySql, the inner join clause can be used to conditionally join two tables together based on some criteria, known as a join predicate. To accomplish this, each row from the first table is sequentially compared to each row from the second table, and if the join predicate evaluates to true for both of them, then the requested columns from the two tables for both rows are used to form a new row and placed in the final set. Lets look at a join statement for our sample data:

select 
  d.name, 
  h.name 
from 
  doctors d 
inner join hospitals h 
  on d.id_hospital = h.id_hospital;

In the above code, we're asking for the name column from doctors and hospitals for all entries in both tables where the id_hospital column matches. A quick note on one of the useful shortcuts we've used above: while you normally would ask for the name column from the doctors table like "doctors.name", we have done the same thing using d.name. We are able to do this because after we asked for the doctors table in the from block, we told SQL that "d" can be used anywhere in our code to refer to the doctors table. Note that we did the same thing using "h" and the hospitals table in the inner join block. Our output should look something like this:

If the two columns you're using to join on happen to share the same name, you can shorten your code even further by using "where (columnname)" instead of the "on" line, although this only works when using the equality operator. You can also use all of the other mathematical operators, such as > and < to create your join predicate. You can even use inner join on three or more tables! Lets take a look!

Three or more tables

Lets assume we have another table of patients that has foreign keys denoting the doctors they're being treated by and the hospitals they're being treated at. Lets also assume that our doctors table no longer has a foreign key for hospital. We can still draw information from all three of these tables using just the foreign keys in the patients table and inner join commands!

select 
  p.patientname, 
  d.doctorname, 
  h.hospitalname 
from 
  patients p 
inner join doctors d 
  on p.id_doctor = d.id_doctor 
inner join hospitals h 
  on p.id_hospital = h.id_hospital;

Here we have followed almost the exact same syntax, only we have tacked on another inner join at the end. Notice how both inner joins are joined using one of the foreign id keys in the patients table, which allows us to tie together two tables that might not share a foreign key to a third table. We might expect the following results from the above code:

Conclusion

Relational databases can be used to store data in multiple tables. These tables can still reference each other using foreign keys. These foreign keys, combined with the inner join command, allow you to view tables using data from different tables!