Joins

The Joins operation in Edilitics allows users to merge data from two different tables into a single, cohesive dataset. This operation is essential for combining related data spread across multiple tables, enabling comprehensive analysis. The no-code Joins functionality makes it easy for users to perform these operations without writing any SQL queries or complex scripts, streamlining the process of data integration.

Steps to Perform a Joins Operation#

1. Select the Field to Join On#

Begin by selecting the field (column) from the pre-selected table (the table chosen at the start of the new transformation flow) that you would like to use for the join operation. This field will act as the key that links the two tables. Typically, this would be a primary key or a foreign key, such as an ID column or any other field that exists in both tables and can be used to match records.

2. Select the Type of Join#

Choose the type of join that best suits your data integration needs. The types of joins available in Edilitics are:

Left Join#

Returns all records from the left table (the pre-selected table) and the matched records from the right table (the table being joined). If there is no match, the result is NULL on the side of the right table.

Right Join#

Returns all records from the right table (the table being joined) and the matched records from the left table (the pre-selected table). If there is no match, the result is NULL on the side of the left table.

Inner Join#

Returns only the records that have matching values in both tables. This join discards any unmatched records.

Outer Join (also known as Full Outer Join)#

Returns all records when there is a match in either the left or right table. Records not matching in both tables will have NULL values in place of missing data.

The choice of join type will determine how the records from the two tables are combined. For example, if you want to keep all records from the first table and only include matching records from the second table, a Left Join is appropriate. If you need all matching records from both tables and are not concerned about non-matching records, an Inner Join is the best choice.

3. Select the Database and Table to Join#

Choose the database from which you want to fetch the second table (the table to be joined with the pre-selected table). After selecting the database, select the specific table and then the field (column) to join on.

• Database Selection: Edilitics allows users to join tables from either the same database as the first table or from completely different databases, as long as the second database has been added as an integration within Edilitics.
• Table Selection: After selecting the database, you will choose the table that you wish to join with your pre-selected table.
• Field Selection: Finally, select the field in the second table that corresponds to the field selected in the first table. This field should have the same or compatible data type as the field in the first table to ensure a successful join operation.

4. Submit#

Once all selections have been made, submit the operation to execute the join.

After submission, Edilitics processes the join and generates a new dataset that combines the data from the two tables based on the specified join criteria. This new dataset can be used for further analysis, transformations, or visualization.

Important Concepts#

Join Types Explained#

1. Left Join:
   • Use Case: When you need to include all records from the first (left) table, regardless of whether there is a matching record in the second (right) table.
   • Example: If you have a table of customers and a table of orders, a Left Join could be used to get a list of all customers, along with their orders, if any. Customers without orders would still be included, but with NULL values for the order details.
2. Right Join:
   • Use Case: When you need to include all records from the second (right) table, even if there is no corresponding record in the first (left) table.
   • Example: If you have a table of products and a table of sales, a Right Join could be used to get a list of all sales, including products that may not yet have been sold. Products not yet sold would appear with NULL values for the sales details.
3. Inner Join:
   • Use Case: When you only want to include records that have matches in both tables.
   • Example: To create a list of orders that only includes customers who have made a purchase, you would use an Inner Join between the customers table and the orders table.
4. Outer Join:
   • Use Case: When you want to include all records from both tables, matching them where possible, and including unmatched records with NULL values.
   • Example: To get a comprehensive list of all customers and orders, including customers without orders and orders without customers, you would use an Outer Join.

Cross-Database Joins#

• Concept: Cross-database joins allow users to combine tables from different databases. This feature is particularly useful when integrating data from multiple sources, such as combining sales data from a SQL database with customer data from a cloud-based database.
• Benefit: This flexibility enhances the ability to perform comprehensive data analyses by breaking down silos and integrating diverse datasets, leading to richer insights and more informed decision-making.

Real-World Applications of Joins#

1. Retail Industry#

Objective: Combine customer and order data for sales analysis.

Scenario:

• Join Type: Left Join
• Tables Involved: Customers (left), Orders (right)
• Use Case: Merge customer data with order records to identify customers who have placed orders and those who haven’t. This helps in targeting marketing efforts effectively.
• New Column Names: customer_id, customer_name, order_id, order_date, order_amount

2. Healthcare Industry#

Objective: Integrate patient records with treatment history.

Scenario:

• Join Type: Inner Join
• Tables Involved: Patients (left), Treatments (right)
• Use Case: Combine patient data with their treatment history to analyze treatment outcomes and ensure comprehensive patient care.
• New Column Names: patient_id, patient_name, treatment_id, treatment_date, treatment_type

3. Finance Industry#

Objective: Merge transaction records with account details for audit purposes.

Scenario:

• Join Type: Right Join
• Tables Involved: Transactions (left), Accounts (right)
• Use Case: Consolidate transaction data with account information to ensure all transactions are accounted for, even if some accounts have no transactions.
• New Column Names: transaction_id, transaction_date, account_id, account_type, account_balance

4. Manufacturing Industry#

Objective: Assess production efficiency by joining batch records with quality checks.

Scenario:

• Join Type: Outer Join
• Tables Involved: Production Batches (left), Quality Checks (right)
• Use Case: Combine production batch data with quality check results to monitor production efficiency and identify batches that passed or failed quality checks.
• New Column Names: batch_id, production_date, quality_check_id, quality_status

5. Education Industry#

Objective: Combine student information, course enrollment, and grades for academic reporting.

Scenario:  

• Join Type: Inner Join and Left Join   \

• Tables Involved: Students (left), Enrollments (middle), Courses (right)

• Use Case:

  • First, perform an Inner Join between the Students and Enrollments tables to include only students who are currently enrolled.       \

  • Then, perform a Left Join between the resulting table and the Courses table to include all enrolled students with their course details.     \

  • This allows for comprehensive academic reporting by combining student details, their enrolled courses, and the grades they received. \

• New Column Names: student_id, student_name, enrollment_id, course_id, course_name, grade

The Joins operation in Edilitics provides a powerful, yet user-friendly way to combine data from multiple tables, whether within the same database or across different databases. By selecting the appropriate join type and fields, users can merge data effectively to create cohesive datasets ready for further analysis or reporting. This no-code operation simplifies complex data integrations, enabling users to focus on uncovering insights and driving data