Tabula Documentation
Tabula HomeCommunity
  • đź‘‹Introduction to Tabula
  • Getting Started
    • Product Updates
    • Getting Started
      • Installation and Login
    • FAQ
  • Product overview
    • Home Page
    • Exploring Data
      • Data Catalog
      • Exploring Datasets
      • Statistics Panel
    • Designing Flows
      • Creating Flows
      • Flow Designer Guide
        • Working with Canvas
        • Using Groups
        • Working with Table
      • Managing Flows
      • Sharing Flows
      • Demo: Building a Simple Flow
    • Executing Flows
      • Running Flows
      • Jobs overview
    • Building Reports
      • Designing Reports
      • Running Reports
      • Reports Page
    • Connecting Data
  • Integrations
    • Enrichments
      • Waterfall Enrichment
      • How to add your API key in Tabula
      • Collections
        • Search Companies
        • Search People
        • Get Job Listings
      • Data Providers
        • AnymailFinder
        • Apollo
          • How to find Apollo API key
          • Enrich person by LinkedIn
          • Enrich company by domain
        • Bounceban
        • Bouncer
        • Bouncify
        • CaptainVerify
        • Cleanify
        • Clearout
        • CompanyEnrich
        • ContactOut
          • How to find ContactOut API key
          • Enrich person by LinkedIn
          • Enrich person by email
        • Discolike
        • TheCompaniesAPI
        • Findymail
        • Emailable
        • EmailListVerify
        • Enrichley
        • Heybounce
        • Hunter
        • Kickbox
        • Mails
        • MailChecker
        • MillionVerifier
        • NeverBounce
        • Nubela (Proxycurl)
        • PeopleDataLabs
        • Prospeo
        • ZeroBounce
        • ReverseContact
          • How to find Reverse Contact API key
          • Enrich person by LinkedIn
          • Enrich person and company by email
          • Enrich company by domain
          • Enrich company by LinkedIn
        • UpLead
    • Data Sources
      • Configuring Fivetran Integration
    • Data Storages
      • PostgreSQL
      • Snowflake
      • BigQuery
      • ClickHouse
  • Data Transformation
    • Transforms
      • Source
      • New Empty Table
      • Output
      • Chart
      • Enrichment
      • New Column
      • If...Then
      • Rolling Functions
      • Column Type
      • Columns Edit
      • Filter
      • Remove Duplicates
      • Sort
      • Find and Replace Text
      • Split Column
      • Extract Text
      • Match Text
      • Join
      • Union
      • Group By
      • Pivot
      • Unpivot
      • To JSON
      • From JSON
      • API Call
      • AI Column
      • AI Table
    • Formulas
      • What are Formulas?
      • Math Functions
        • Abs
        • Ceiling
        • Exp
        • Floor
        • IsEven
        • IsOdd
        • Ln
        • Log
        • Log10
        • Mod
        • Pi
        • Power
        • Quotient
        • Round
        • RoundDown
        • RoundUp
        • Sign
        • Sqrt
        • Truncate
      • Trigonometric Functions
        • Acos
        • Asin
        • Atan
        • Atan2
        • Cos
        • Cot
        • Degrees
        • Radians
        • Sin
        • Tan
      • String Functions
        • Compare
        • Concat
        • Contains
        • In
        • CountMatches
        • CountMatchesRegexp
        • EndsWith
        • EndsWithRegexp
        • Extract
        • FindMatchOfString
        • FindMatchOfRegexp
        • FindMatchesOfString
        • FindMatchesOfRegexp
        • Left
        • Length
        • Lower
        • Matches
        • Pad
        • ProperCase
        • RemoveSymbols
        • RemoveWhitespaces
        • Repeat
        • Replace
        • ReplaceRegexp
        • Reverse
        • Right
        • Spaces
        • Split
        • SplitRegexp
        • StartsWith
        • StartsWithRegexp
        • Stuff
        • Substring
        • SubstringDelimiter
        • SubstringRegexpDelimiter
        • Trim
        • Upper
      • Date & Time Functions
        • Date
        • DateAdd
        • DateAdd2
        • DateDiff
        • DateDiff2
        • DateFromParts
        • DateTime
        • DateTimeFromParts
        • DateTrunc
        • DayName
        • DayOfMonth
        • DayOfWeek
        • DayOfYear
        • Hour
        • Minute
        • Month
        • MonthName
        • Now
        • Quarter
        • Second
        • Time
        • TimeFromParts
        • Today
        • Week
        • Year
      • Aggregate Functions
        • Any
        • AnyIf
        • Array
        • ArrayIf
        • Avg
        • AvgIf
        • AvgInRow
        • Count
        • CountA
        • CountIf
        • CountUnique
        • Max
        • MaxIf
        • MaxInRow
        • Median
        • MedianIf
        • Min
        • MinIf
        • MinInRow
        • Mode
        • ModeIf
        • Percentile
        • Quartile
        • StdDev
        • StdDevIf
        • Sum
        • SumIf
        • SumProduct
        • Variance
        • VarianceIf
      • Conversion Functions
        • ToArray
        • ArrayToString
        • ToBoolean
        • ToDate
        • ToDateTime
        • ToDecimal
        • ToInteger
        • ToObject
        • ToTime
        • ToString
      • Misc Functions
        • At
        • IsMissing
        • RowNumber
        • Random
        • If
        • Coalesce
        • True
        • False
        • Null
        • $target
      • Window Functions
      • Custom Functions
      • Data Types
      • Supported Date Parts
      • Regex: List of Tokes
  • Pricing & Billing
    • Plans, Subscriptions, and Credits
    • Tabula for Education
  • Tutorials
    • Tabula Use Cases
    • Merge Columns
    • Join Types
    • Union Introduction
    • Window Functions
    • What is Unpivot?
    • JSON Format Tutorial
    • Using Regex
Powered by GitBook
On this page
  • Introduction
  • Join Types

Was this helpful?

  1. Tutorials

Join Types

Introduction

One of the key aspects of Tabula is the ability to join tables together to retrieve information from multiple sources. In this tutorial, we will cover the main types of SQL joins and provide examples for each, using tables with data to illustrate each join type.

What is Join?

Join operation, in simple terms, is a process used to combine information from two or more different tables in a database. This operation is essential when you need to gather data that is spread across multiple tables and create a single, comprehensive view.

Consider you have two tables: 'employees' and 'departments’. You want to get one table with employees’ names and corresponding departments’ names.

Example

employees:

id
name
department_id

1

John Smith

10

2

Jane Doe

20

3

Mike Brown

40

departments:

id
name

10

HR

20

IT

30

Finance

The join operation works by identifying a common "link" or "key" between the tables, such as departament_id in 'employees' or id in 'departments’. It then merges the tables based on this link, creating a new table with the combined information.

Difference with VLOOKUP

Join operation is a method used in databases to combine information from two or more different tables based on a common column or relationship. It is similar to the VLOOKUP function in spreadsheets like Microsoft Excel or Google Sheets, as both are used to merge data from separate sources based on a shared identifier.

VLOOKUP is a function that searches for a specific value in the first column of a table and returns a corresponding value from another column in the same row. It is commonly used to look up and retrieve related information from one table using a shared identifier present in another table.

Join operation, on the other hand, is used in databases like SQL to combine entire tables based on a common column or relationship. Unlike VLOOKUP, join operations can merge multiple columns and rows from two or more tables, providing a more comprehensive view of the combined data.

Join Types

INNER JOIN

An INNER JOIN returns only the rows where there is a match between the columns specified in the join condition from both tables.

Result:

name
name

John Smith

HR

Jane Doe

IT

LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the right table's columns.

Result:

name
name

John Smith

HR

Jane Doe

IT

Mike Brown

NULL

RIGHT JOIN

A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for the left table's columns.

Result:

name
name

John Smith

HR

Jane Doe

IT

NULL

Finance

FULL JOIN

A FULL JOIN (or FULL OUTER JOIN) returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for the columns of the table with no match.

Result:

name
name

John Smith

HR

Jane Doe

IT

Mike Brown

NULL

NULL

Finance

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables, i.e., it combines each row from the first table with each row from the second table. This type of join is not commonly used in practice but can be helpful in specific scenarios.

Example. Consider two tables: 'colors' and 'sizes'.

colors:

id
name

1

Red

2

Blue

3

Green

sizes:

id
name

1

Small

2

Medium

3

Large

Result:

color_name
size_name

Red

Small

Red

Medium

Red

Large

Blue

Small

Blue

Medium

Blue

Large

Green

Small

Green

Medium

Green

Large

In the example above, the CROSS JOIN combines each color with each size, resulting in 9 rows (3 colors * 3 sizes).

PreviousMerge ColumnsNextUnion Introduction

Last updated 1 year ago

Was this helpful?