Download - How Does a Join Work
-
8/9/2019 How Does a Join Work
1/11
How does a join work?
A join operation combines fields from two or more tables in a database and enables us to analysethe resulting data set. The result of a join is a horizontally ‘extended’ data table as the join addsfields (columns) compared to the starting stage. nderstanding the different join types is essentialfor using Tableau. !e at The "nformation #ab are also ad$ocates of Alteryx% an analytics platformpro$iding intuiti$e wor&flow for data blending and ad$anced analytics. As a powerful datapersonalization tool% Alteryx of course offers all the data joining possibilities.
!hene$er " start to learn or explain a concept% " try to find a simple example or analogy and wal&through that. sually what wor&s in small and simple also wor&s on large and complex. 'ence wewill o$er$iew the join types on two small data tables. These are two sheets in an xcel file named
layer*eography. The first table ( layers) contains pool billiard players and their home countrieswhile the other (*eography) lists countries and the respecti$e regions and continents.
Players table Geography table
!e will join these tables by matching the +ountry field in the layers table with the*eography,+ountry field in the *eography table and arri$e to a more informati$e dataset% notonly showing player names and their countries but the region and continent the country belongsto. The join could be performed se$eral ways% depending on the logic we want to follow. #et’s wal&through these join types and see how they are performed in Tableau (-. ) and Alteryx.
A) Inner Join
"t combines only the common records from the tables. The below image is a $isual representationof an "nner /oin on table 0 and table . This image may be familiar from set theory and 1enndiagrams.
-
8/9/2019 How Does a Join Work
2/11
'ow can we do this in Tableau2 The inner join operation is readily a$ailable in the data connectioneditor window after we dragged the two tables onto the can$as. As Tableau shows us the resultingdataset right away at the bottom of the window% we see that in fact we arri$e to the intersection of those two tables.
The /oin tool in Alteryx has two input nodes% a left and a right part. 3otice that it has three outputnodes% the # 4 / 4 5 ones% # standing for a #eft xcluding /oin% / 6 "nner /oin and 5 6 5ight
xcluding /oin.
This modular setup re7uires a little bit different thin&ing compared to Tableau but in case of aninner join the middle output node (/) is just what we need.
-
8/9/2019 How Does a Join Work
3/11
B) Left Join
This will include all records from the first table ( layers) and all matching records from the secondtable.
This is straightforward to setup in Tableau. "t seems that Alex agulayan’s country is not listed inthe *eography table.
!e arri$e to the first point where we ha$e to adjust to the slightly different logic of Alteryx.Actually% when wor&ing with Alteryx you realize that this is e$en more user friendly. The three
-
8/9/2019 How Does a Join Work
4/11
output nodes of the /oin tool (#4/45) altogether add up to a 8ull 9uter /oin. :o the # and / nodesha$e to be unioned for a #eft /oin.
C) ight Join
A ‘5ight /oin’ returns all records from the second data table (*eography) and the matching recordsfrom the first table.
9ur example is built on an xcel file% where a 5ight /oin is only a$ailable upon using the #egacy+onnection. The default Tableau connection does not rely on the ;icrosoft /et data engine dri$erbut in some cases ( li&e a 5ight /oin) re7uire the capabilities of the / T dri$er. The legacyconnection can be accessed when we open the xcel data source file in Tableau.
8or more information on the #egacy and default xcel connections% please $isit this lin&.
!ith a #egacy connection our tas& in Tableau becomes simple
-
8/9/2019 How Does a Join Work
5/11
-
8/9/2019 How Does a Join Work
6/11
!e hit the first instance in Tableau when performing a join is not merely a matter of dragging datatables to the can$as and selecting the re7uired join type. 8or a 8ull 9uter /oin a custom :>#statement has to be written.
#ater on for a #eft xcluding /oin and a 5ight xcluding /oin we will also ha$e to write custom :>#statements and a 8ull 9uter /oin syntax has three parts. "t is built from the #eft xcluding /oin ?the 5ight xcluding /oin ? the "nner /oin. The ‘excluding’ joins are achie$ed by adding the ‘!here’clause at the end of the #eft or 5ight /oin :># bloc&s% thus excluding all those records that do notcomply with the ‘!here’ clause.
Tableau’s profile is $isual analytics% data $isualisation% being able to as& 7uestions from your data$ery 7uic&ly. "t is a front@end tool. Alteryx is a bac&@end tool% enabling fast and flexible data
personalization% data cleaning% etc. :o we expect a solution of just a few clic&s on a 8ull 9uter /oinfrom Alteryx.
-
8/9/2019 How Does a Join Work
7/11
%) Left %&'l#ding Join
This operation returns only those records from the first table that do not ha$e a match in thesecond table.
"n our example this will lea$e only Alex agulayan’s record in the result set. !ell% he met nomatching performance in B when he won the world pool championship.
#et’s see the custom :># for Tableau’s data connection<
-
8/9/2019 How Does a Join Work
8/11
"n Alteryx the desired output is simply node ‘#’ of the /oin tool.
") ight %&'l#ding Join
This is nothing special after the getting familiar with the #eft xcluding /oin. A mirrored image% ine$ery respect.
This still re7uires a custom :># script in Tableau.
The e7ui$alent in Alteryx
-
8/9/2019 How Does a Join Work
9/11
G) $#ter %&'l#ding Join
"n Tableau we can produce this set with a custom :># statement that unions the left and rightparts of the desired set.
-
8/9/2019 How Does a Join Work
10/11
"n Alteryx we ha$e to union the outputs from the # and 5 nodes.
!e ha$e discussed joining on only one &ey field but e$en if the join is executed on a combinationof fields% the logic remains unaffected.
8inally% let’s re$iew the $arious join type solutions in Tableau’s custom :># and Alteryx<
The topic may ha$e been a bit dry if you are not a data gee& as me but " ha$e included a
name who is not a pool player. 8inding that person in the list may be a playful challenge but toaccomplish that you ha$e to ‘"nner /oin’ the ‘ layers’ table with a list of professional poolplayersC. (9D% *oogle will just ta&e you there% too.)
-
8/9/2019 How Does a Join Work
11/11