Join types in relational databases

Joins in relational databases are a feature that allows you to combine rows from multiple tables into one based on a related column between them. There are several types of joins in relational databases, including inner joins, outer joins, and cross joins. Each type has its own purpose and use cases.

Although joins can be useful, beware of using them with large amounts of data as they can introduce performance problems. Nobody wants performance problems, right?

Let's look at each join type in more detail.

Inner join

An inner join returns only the rows that have matching values in both tables. If there is no match between the two tables, the row is not included in the result set.

Let's assume that we have following two tables:

If we needed to know the department name for each employe, we would use an inner join to combine these tables based on their common column, DepartmentID.

SELECT employees.EmployeeName, departments.DepartmentName
  FROM employees
  INNER JOIN departments ON employees.DepartmentID = departments.DepartmentID;

This query joins the Employees table with the Departments table where their DepartmentID values match. The resulting table is:

Left join

Left join returns all records from the left (first specified) table and the matched records from the right table. If there is no match, a NULL value will be assigned to the right table column.

We will again work with the same tables:

Left join query looks like this:

SELECT employees.EmployeeName, departments.DepartmentName
  FROM employees
  LEFT JOIN departments ON employees.DepartmentID = departments.DepartmentID;

The resulting table would be:

Right join

Right join is the opposite of the left join. It returns all records from the right (second specified) table and the matched records from the left table. If there is no match, a NULL value will be assigned to the left table column.

So, if we were to run the following query with the same tables as before:

SELECT employees.EmployeeName, departments.DepartmentName
  FROM employees
  RIGHT JOIN departments ON employees.DepartmentID = departments.DepartmentID;

the resulting table would look like this:

Notice that we have two rows for the Engineering department. This is because there are two employees assigned to that department.

Self join

A self join is a concept where a table is joined with itself. It might sound unusual at first, but it's quite useful in scenarios where you need to compare rows within the same table or construct hierarchical relationships.

To perform a self join, you can use table aliases to refer to the same table in different roles within the query. This way, you can distinguish between the "left" and "right" instances of the same table.

Let's consider a modified version of the employees table. Here, we have an additional column ManagerID that references EmployeeID column within the same table.

Now, imagine if an angry customer asked Gonzo Baloni to call his manager. To find out who directly reports to whom, we can perform a self join on our table:

SELECT 
      employee.EmployeeName AS Employee, 
      manager.EmployeeName AS Manager
  FROM employees AS employee
  JOIN employees AS manager ON manager.EmployeeID = employee.ManagerID;

In the resulting table, we can easily see that Gonzo reports to John Doe. We don't even know who the guy is, such a generic name.

Cross join

A cross join returns the Cartesian product of two tables. It means that each row from the first table is combined with each row from the second table. This means that if the first table has N rows and the second table has M rows, the resulting table will have N * M rows.

Cross join does not require a join condition, which is a difference from other types of joins.

To demonstrate a cross join, let's imagine these two tables:

If we wanted to list all possible combination of products and colors, we would use a Cross join.

SELECT colors.Color, products.Product
  FROM colors
  CROSS JOIN products;

The resulting table looks like this: