2148588 - What is a Join?

This is a foundation article, provided to explain the concept of a join.  Recommended to provide assistance for understanding joins in Reporting tools.

* Workforce Analytics

 

What is a Join?

 

A join is a simple connection of two items.  In relation to data, it is the joining of two data tables (sets of information)  via a common set of data (information).

Think back to Venn Diagrams, which a lot of us learnt about in Maths at school.

 

Circle ‘A’ is one set of information.  Circle ‘B’ is another set of information.  Intersection ‘C’ is the common information found in both sets.

This is the same principle of a join between two data tables.  You are finding ‘C’ to join the two tables.

 

In the examples provided below, there are two tables – CUSTOMER and ORDERS.

CUSTOMER

CUSTID         NAME        AGE        ADDRESS          

1                    Rodger       25           Annondale      

2                    Cillian         36           Fairfield

3                    Alex            44           Moraben

4                    Paul            56           Kingsley

 

ORDERS

ORDERID      DATEORDERED     CUSTID        AMOUNT

65                  23/03/2015           1                    500.00

66                  23/03/2015           2                  2000.00  

67                  24/03/2015           4                  5000.00

 

For this example, to join the CUSTOMER and ORDERS tables, you would use the ID from the CUSTOMER (CUSTID) table and (CUSTID) from the ORDERS table to join the two sets of data.

 

Selecting the "fields" you require:  (A field being a piece of information/data)

* CUSTID - Customer ID from CUSTOMER table

* NAME - Customer Name - from CUSTOMER table

* DATE - Data of Order - from ORDERS table

* AMOUNT - Amount - from ORDERS table

 

 

You would see the following result:

 

CUSTID       NAME                          DATE                                    AMOUNT

 

 

1                 Rodger                       23/03/2015                              500.00

 

2                 Cillian                         23/03/2015                           2000.00  

 

4                 Paul                            24/03/2015                           5000.00

 

 

 

This output is the combination of the two tables, by working with CUSTID to combine the information, and selecting the required fields.

Going back to the Venn diagram above, CUSTID from the CUSTOMER table and CUSTID from the ORDERS table, would the the 'C' - common set of information

Why use a JOIN?
Working with JOINs creates more flexibility in the data you can achieve and share via reports.  It allows you to work with smaller tables (sets of information) to allow for greater speed in processing information.

 

One thing to be aware of in regards to JOINS, is that there are different 'types' of joins. 

 

For more informaton on the 'types' of joins, refer to this website:

http://www.tutorialspoint.com/sql/sql-using-joins.htm  - SQL Join Types.

 

 

 

KBA , LOD-SF-WFA-DES , Report Designer , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, YouCalc, ORD) , How To

SAP SuccessFactors Workforce Analytics all versions