Data source (Joins)
  • 2 Minutes to read
  • Contributors

    Data source (Joins)


      Article Summary

      An overview of how to join multiple data tables to create a data source

      This data source is made up of two or more data tables joined together using the JOIN concept. The data columns in the respective tables assemble and provide a visual summary of the collective data in the form of rows and columns based on the selected Join Type. 

      What are JOINS? 

      A Join clause is used to join data rows from two or more data tables based on a common field. There are several Join Types in the SQL programming language that you may be familiar with. However, as of today, there are two types of Join clauses in Quixy, as listed below. 

      1. Inner Join 
      2. Left Join 

      Note: More Join clauses, such as Right Join, Full Join, and Multiple Join, are in the works and will be available anytime soon. 

      Let's go over each of the above-mentioned join types one by one, starting with Inner Join. 

      Inner Join

      The INNER JOIN selects records from both tables that have matching values in the common field of the data tables. 

      A sneak peek into the experience of combining data tables with Inner Join to create a data source is provided in the GIF below.  

      3 GIF

      A summary of the above GIF is given below. 

      Data Table 1:

       

      Customer ID 

       

       

      Customer Name 

       

       

      Address 

       

       

      City 

       

       

      Postal Code 

       

       

      Country 

       

       

      Date 

       

      QXY9987 

      Rahul 

      Bihar 

      Muzzarpur 

      998765 

      India 

      01-Apr-2022 

      VVT1001 

      Xing Lo 

      Hunana 

      Hunana 

      667890 

      India 

      16-Apr-2022 

      Data Table 2: 

       

      Order ID 

       

       

      Customer ID 

       

       

      Employee ID 

       

       

      Order Date 

       

      Shipper ID 

      99876544 

      VVT9987 

      QXY0098 

      22-Apr-2022 

      23456787654 

      2345678764 

      44567788 

      2345678 

      19-Apr-2022 

      87656543 

      9987609 

      QXY9987 

      998760 

      11-Mar-2022 

      9876 

      8873409 

      VVT1001 

      554678 

      11-Mar-2022 

      7768653 

      If you closely observe the above two tables, you will notice that Customer ID is common in both of them. Now, if you combine both the above tables using Inner Join and a common field (Customer ID), the resultant set will get the data of the matching records in the common field. Refer to the result-set given below. 

      Resultant Data source: (Data Table 1_Customer ID {Inner Joined} Data Table 2_Customer ID) 

      This table will have only the elected columns (when configuring the Data source) from the above two tables, and the data rows of the matching values in the common column field, as shown below. 

      Customer ID 

      Customer Name 

      Postal Code 

      Country 

      Order ID 

      Employee ID 

      Order Date 

      QXY9987 

      Rahul 

      998765 

      India 

      9987609 

      998760 

      11-Mar-2022 

      VVT1001 

      Xing Lo 

      667890 

      India 

      8873409 

      554678 

      11-Mar-2022 

      Left Join 

      The Left Join clause puts all the rows of the data table on the left side of the join and the matching rows for the data table on the right side of the join. The data rows with no matching records or data on the right will have null in the result-set. The LEFT JOIN is also known as the LEFT OUTER JOIN.

      Observe the tables below carefully to understand the result of the Left Join functionality. 

      Data Table 1: 

       

      Order ID 

       

       

      Customer ID 

       

       

      Employee ID 

       

       

      Order Date 

       

      Shipper ID 

      99876544 

      VVT9987 

      QXY0098 

      22-Apr-2022 

      23456787654 

      2345678764 

      44567788 

      2345678 

      19-Apr-2022 

      87656543 

      9987609 

      QXY9987 

      998760 

      11-Mar-2022 

      9876 

      8873409 

      VVT1001 

      554678 

      11-Mar-2022 

      7768653 

      Data Table 2:  

       

      Customer ID 

       

       

      Customer Name 

       

       

      Address 

       

       

      City 

       

       

      Postal Code 

       

       

      Country 

       

       

      Date 

       

      QXY9987 

      Rahul 

      Bihar 

      Muzzarpur 

      998765 

      India 

      01-Apr-2022 

      VVT1001 

      Xing Lo 

      Hunana 

      Hunana 

      667890 

      India 

      16-Apr-2022 

      Resultant Data source: (Orders table_Customer ID {Left Joined} Customer Info table_Customer ID)

      This table will have only the elected columns (when configuring the Data source) from the above two tables. In this scenario, all the columns from the above two tables are selected. Now, as the definition of Left Join states, all the data rows of the left table will be present in the result-set, but matching values in the common column fields of the right table will be combined in the result-set, as shown below. 

      As stated in the definition of Left Join, you can see the result below. The data discrepancies that you noticed are because the right table didn’t have any matching data. 

      Once you create a data source will have the doors opened to generate Reports, Views, and so on as given in the Data source main article.


      Was this article helpful?