Clarissa Ng on 2020-08-11
A SQL join describes the process of merging rows in two different tables or files together.
Rows of data are combined based on values in a selected column.
In the example above, this is the Item column.
An Item, Apple, has both a price and a quantity, but in different tables.
Using a SQL join, these values become merged into one row!
In most cases, joining data won’t be as simple as the previous example. Oftentimes, we’ll have rows that can’t be joined because there isn’t the same value in the joining column for both tables.
For instance, what if there wasn’t a quantity for apple in the example above? How we handle what we do with rows like this depends on the type of SQL Join we choose.
There are four main types of joins: inner join, left join, right join, and full join. In this article, I’ll explain to you what each type entails.
Before going into everything, I think it’d be nice to mention that Merge Spreadsheets is an easy to use tool that will easily perform all of this joining for you.
You can experiment with your spreadsheets to learn how these joins will look.
An inner join results in a table with rows where the values in the joining column are in both tables.
To better understand this, let’s take a look at this example being joined on the item column:
Both of the starting tables have a lot of different items, but only Apple and Orange are in both. So, the final result will only include Apple and Orange with their price and quantity values merged into the same row.
A left join will include all of the rows in the left table (file one), regardless of if they’re in the right table.
Let’s take a look at the same tables as before, also being merged on the item column:
Like with inner join, the final product of this join includes Apple and Orange. However, it also includes all the other rows from file one, even if they don’t have quantity values (those cells will be left blank)!
A right join is the exact opposite of a left join; it includes all the values in the right table (file two) regardless of what’s in the first table.
So, in the same example as before, this will include Apple and Orange while also including any other rows in file two!
A full join will combine all the data from both spreadsheets while merging the rows that can be merged.
Full join is definitely the way to go if you don’t want to exclude any data in your final result.
As always, using the same two tables as before will yield a different result when using a full join.
All the rows from both tables are included and Apple and Orange become merged (as those are the only common items)!
It gets a little more complicated. There are a couple special cases that I’ll go into so that you’ll have a better understanding of how joining works!
If you have duplicates in one table, they’ll also show up in your joined table depending on the join type.
If there is a single row in one file with the same value in the joining column as multiple rows in the second file, those data in that single row will be repeated for each instance.
For example, let’s look at the tables below being Inner joined on the Item column. In file one, there are two Orange rows while there’s only one in file two.
Joining this will include both of the unique rows from file one while repeating the data for quantity from file two!
By the way, a LEFT join would also produce the same result
Yes! If you want to join a table based on multiple columns, each grouping will be classified as its own unique entity.
For instance, in the example below, we’ll be Full joining on the Item and Price columns.
The Apple row is the only one that’s merged because it’s the only one with the same Item and Price in both tables.
Instead of both of the Orange rows being merged, they are treated as different rows because their price values are different.
So, everything is the same as before but now we look for the same values in two columns rather than just one.
Each blank value in the column you’re joining on will be treated like any normal value. In other words, you can join on a blank value as usual.
For example, in the tables above, there are blank values in both files in the item column. These will be treated as the same string and become joined! In the case of the example above, all types of joining will result in the same product.
NOTE: While a blank value will be treated as any other string, a NULL value is different. NULL values denote nothing and are typically used in code. Each NULL value is treated as a unique entity and can’t be joined on. For more information, check out this article.
Let’s also take a look at a more comprehensive example with all of these special cases in it.
We will be performing a Left join on the Item and Price columns.
When performing a left join, all the rows from the left file will be in the final product.
The Apple, $1 row is in both files, so the ID# and quantity for them will be merge.
Orange, $2 is only in the left file, so its quantity will remain blank.
Finally, we have a blank/blank row in the left file and two blank/blank rows in the second file. This means the the ID # for blank/blank in file one will be repeated twice to go with each quantity value in the second table!
And the fully blank row at the bottom will be ignored!
Yep! We just went over all the basics of SQL joins so you should be able to join tables with no problems now.
Feel free to experiment with your data in spreadsheets here on Merge Spreadsheets because there is nothing like trying this out to really understand it.
If you still have questions, don’t hesitate to reach out to us at info@lovespreadsheets.com!
Want to learn more about how to Merge Spreadsheets? Check out our ultimate guide here!
Let us know what you think