• Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar
  • Skip to footer
  • Articles
  • News
  • Events
  • Advertize
  • Jobs
  • Courses
  • Contact
  • (0)
  • LoginRegister
    • Facebook
    • LinkedIn
    • RSS
      Articles
      News
      Events
      Job Posts
    • Twitter
Datafloq

Datafloq

Data and Technology Insights

  • Categories
    • Big Data
    • Blockchain
    • Cloud
    • Internet Of Things
    • Metaverse
    • Robotics
    • Cybersecurity
    • Startups
    • Strategy
    • Technical
  • Big Data
  • Blockchain
  • Cloud
  • Metaverse
  • Internet Of Things
  • Robotics
  • Cybersecurity
  • Startups
  • Strategy
  • Technical

Joining Multiple Sources 101: Inner and Outer Joins

Eran Levy / 4 min read.
February 12, 2015
Datafloq AI Score
×

Datafloq AI Score: 56.33

Datafloq enables anyone to contribute articles, but we value high-quality content. This means that we do not accept SEO link building content, spammy articles, clickbait, articles written by bots and especially not misinformation. Therefore, we have developed an AI, built using multiple built open-source and proprietary tools to instantly define whether an article is written by a human or a bot and determine the level of bias, objectivity, whether it is fact-based or not, sentiment and overall quality.

Articles published on Datafloq need to have a minimum AI score of 60% and we provide this graph to give more detailed information on how we rate this article. Please note that this is a work in progress and if you have any suggestions, feel free to contact us.

floq.to/GhnkZ

Mashing up multiple data sources to generate a single source of truth is an integral part of data analysis. It allows you to compare and cross-reference records stored in different formats and locations, and to perform queries and calculations. This article will run you through some basic concepts in data analysis that you should become familiar with when joining data stored in multiple tables.

Why do I need to know this?

Short answer: In most cases, you dont. If youre using a strong business intelligence tool and working with simple datasets, the bulk of the data preparation process should be handled by the software itself. However, its useful to understand how your data is transformed in the process and how the new, combined data will look in tabular form. Additionally, you might need this info when working with more complex data.

So lets get started!

Whats a Join? And isnt that a verb?

In the context of SQL and database management, a join is a way of combining records from multiple tables. A join requires common fields between the two tables in order to form a logical connection, and is the basis for combining different data sources and an integral part of data analysis.

Inner Joins

Used for connecting identical fields

An inner join is used to connect two or more tables that contain fields with identical records. For example, if we look at an example of two tables:
image1
These two tables would be combined via an inner join based on the common field Product. The result would be one table which contains data from the previous two:
image2
If one of the tables contained a record that does not appear in the other (e.g. if the Product-Stock table would contain a fourth row with details of banana stock), that data would be disregarded.

Outer Joins

Used for connecting common, but not identical fields

Outer joins are divided into left, right and full joins. To understand the difference between the two, lets once again look at two tables:
image2.1
As you can see, there is no column with completely matching records between these two tables. There are three main ways we can go about combining this data:


Interested in what the future will bring? Download our 2023 Technology Trends eBook for free.

Consent

image2.2

image3

  • Left join: Generates a table that contains all the records from the lefthand table, along with any matching records found on the righthand one. In our example:
  • Right join: Same as left join, but will contain all records from the righthand table.
  • Full outer join: Will essentially perform both a left and right join, combining the two tables despite any records not matching, e.g.:

These are the main types of joins youre likely to encounter. Each might be used in different scenarios, depending on the analyses you will want to perform on the combined data.

Row Level Joins vs. Summarized Views

Business Intelligence software tools have two different ways of joining multiple tables:
A row level join means that after two data sets are combined, the original records are still kept within the data model and accessible. This, once again, is a question of the strength and sophistication of the engine that powers your analytics tool: A robust one should be smart enough to understand the data and model it in such a way that will allow you to drill down into the full granularity of your data, as it was before the tables were joined.
In contrast, BI software that relies on a less powerful back-end will instead create a summarized view of the data by aggregating it, thus compromising its granularity. The guiding line should be this: can you easily access the original, row-level details of your data after the join is performed? If the answer is no, your BI software might be lacking in terms of its mash-up capabilities.

Illustration:

image4

Categories: Technical
Tags: Big Data, outer

About Eran Levy

Tech writer, blogger and content manager at Sisense, the business analytics and dashboard software company that's taking the world by storm. Passionate about technology, innovation and start-up culture.

Primary Sidebar

E-mail Newsletter

Sign up to receive email updates daily and to hear what's going on with us!

Publish
AN Article
Submit
a press release
List
AN Event
Create
A Job Post

Related Articles

How BlaBlaCar Built a Practical Data Mesh to Support Self-Service Analytics at Scale

March 23, 2023 By Barr Moses

How to leverage novel technology to achieve compliance in pharma

March 23, 2023 By Terry Wilson

Top 6 Cybersecurity Certification Programs in 2023

March 22, 2023 By Lucia Adams

Related Jobs

  • Software Engineer | South Yorkshire, GB - February 07, 2023
  • Software Engineer with C# .net Investment House | London, GB - February 07, 2023
  • Senior Java Developer | London, GB - February 07, 2023
  • Software Engineer – Growing Digital Media Company | London, GB - February 07, 2023
  • LBG Returners – Senior Data Analyst | Chester Moor, GB - February 07, 2023
More Jobs

Tags

AI Amazon analysis analytics application Artificial Intelligence BI Big Data business China Cloud Companies company costs crypto Data design development digital engineer environment experience finance financial future Google+ government Group health information machine learning market mobile news public research security services share skills social social media software strategy technology

Related Events

  • 6th Middle East Banking AI & Analytics Summit 2023 | Riyadh, Saudi Arabia - May 10, 2023
  • Data Science Salon NYC: AI & Machine Learning in Finance & Technology | The Theater Center - December 7, 2022
  • Big Data LDN 2023 | Olympia London - September 20, 2023
More events

Related Online Courses

  • Standardisation & Technology
  • Forming, Funding, & Launching a Startup Company
  • Game Design and Development 5: Capstone Project
More courses

Footer


Datafloq is the one-stop source for big data, blockchain and artificial intelligence. We offer information, insights and opportunities to drive innovation with emerging technologies.

  • Facebook
  • LinkedIn
  • RSS
  • Twitter

Recent

  • Explaining data products lifecycle and their scope in management
  • Microsoft Power BI -The Future of Healthcare’s Most Important Breakthrough
  • The Big Crunch of 2025: Is Your Data Safe from Quantum Computing?
  • From Data to Reality: Leveraging the Metaverse for Business Growth
  • How BlaBlaCar Built a Practical Data Mesh to Support Self-Service Analytics at Scale

Search

Tags

AI Amazon analysis analytics application Artificial Intelligence BI Big Data business China Cloud Companies company costs crypto Data design development digital engineer environment experience finance financial future Google+ government Group health information machine learning market mobile news public research security services share skills social social media software strategy technology

Copyright © 2023 Datafloq
HTML Sitemap| Privacy| Terms| Cookies

  • Facebook
  • Twitter
  • LinkedIn
  • WhatsApp

In order to optimize the website and to continuously improve Datafloq, we use cookies. For more information click here.

settings

Dear visitor,
Thank you for visiting Datafloq. If you find our content interesting, please subscribe to our weekly newsletter:

Did you know that you can publish job posts for free on Datafloq? You can start immediately and find the best candidates for free! Click here to get started.

Not Now Subscribe

Thanks for visiting Datafloq
If you enjoyed our content on emerging technologies, why not subscribe to our weekly newsletter to receive the latest news straight into your mailbox?

Subscribe

No thanks

Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

Marketing cookies

This website uses Google Analytics to collect anonymous information such as the number of visitors to the site, and the most popular pages.

Keeping this cookie enabled helps us to improve our website.

Please enable Strictly Necessary Cookies first so that we can save your preferences!