Join Types

Introduction

One of the key aspects of Tomat 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:

idnamedepartment_id

1

John Smith

10

2

Jane Doe

20

3

Mike Brown

40

departments:

idname

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:

namename

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:

namename

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:

namename

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:

namename

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:

idname

1

Red

2

Blue

3

Green

sizes:

idname

1

Small

2

Medium

3

Large

Result:

color_namesize_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).

Last updated