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.


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.


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


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


5/11


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.


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/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


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.


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.)


11/11

how does a join work

Documents