The next post that I want to do on SQL is on how to join tables together. The reason that I think this is important for anyone that wants to automate office is that one of the most annoying things to do in Excel is a vlookup. If you are doing multiple vlookups over several datasets then Excel struggles and crashes, index match can also be used but again this has its limitations.
If you import data into a database, like I have shown before using python, then you can use a database to connect the dots. This is a much more powerful way of dealing with data compared to using Excel, you could use access but the querying is much more fiddly and limited and like Excel much more likely to crash.
Below shows many of the typical joins that you can do, it is probably recommended by most to use a standard ANSI standard join over the newer style syntax so this is what I have detailed below. Though in this post I am going to stick to the 2 main types of joins you are likely to want to use, other joins will be discussed else where.
Inner Join:
Join the 2 (or more) datasets together and only show results where there is a matching row in both sources. As shown in the example below, the row containing Value3 on Table 1 is not returned if you use an inner join on Table1 and Table2.
Outer Join (left):
An outer join is the same as an inner join except that the row for Value3 will be returned if the left hand table on the join is Table1. Note that there are some things you need to be careful if you put filters in the where clause that are on Table2 you need to specify this in the join section otherwise you basically end up with an inner join.
The code:
No comments:
Post a Comment