Astha Sharma on 2021-08-04
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.
To read more about it, check out our article or this infographic!
A SQL Join is a great way to combine data between a few tables.
A good comparison is thinking of a SQL Join as a Zoom Call.
In Zoom, you can add several people either at the start or while the call is in process. You are saying everything to a group of people at once, instead of individually having to call them.
Similarly, a SQL Join lets you query rows and columns from multiple tables through a single SQL command. You don't need to write a separate SQL statement for each of your tables.
A join filter allows a table to be filtered based on how a related table in the publication is filtered.
With SQL Join, you can filter data between tables. You have the ability to apply filter criteria on a table prior to joining.
Read this Microsoft Doc for an example.
You have 100 tables of all the employees in your company sorted by different departments. Technically some positions fall under multiple departments.
You can use a SQL Join to compare the name or ID of the employee through the tables and remove any duplicates you see.
Compared to using a subquery, SQL Join executes faster. You can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.
SQL Join is also faster than writing an IN statement.
As mentioned above, the main Join types are: Inner Join, Right Join, Left Join, and Full Join.
An inner join results in a table with rows where the values in the joining column are in both tables.
A left join will include all of the rows in the left table (file one), regardless of if they’re in the right table.
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.
A full join will combine all the data from both spreadsheets while merging the rows that can be merged.
Each of these serve a specific purpose that is useful for different use cases.
More info!With SQL Join, you can control the order in which tables are scanned.
This is good to use in situations where a large number of rows on the left hand input, and a much lower number on the right hand.
Or when a significant percentage of the overall query execution time spent in the join operator.
Read more in this example here.
The Join syntax has a clear purpose of the query and the columns that are used.
It follows the modularity rule about using strict typing whenever possible. Explicit is almost universally better.
These were just a few reasons why you should use SQL Join.
Make sure to check out Merge Spreadsheets to automatically join your spreadsheets!
Please feel free to email us at admin@lovespreadsheets.com for any questions, suggestions or concerns. :)
