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:

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