Understanding SQL Joins: A Beginner's Guide
Tags: sql, beginners, database, programming
Published: 2023-04-30 23:42:32


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56t02pcm8iia9615sjdy.jpg)

There are three main types of joins in SQL: **INNER**, **OUTER**, and **CROSS** joins. Each join involves two tables, referred to as the LEFT and RIGHT tables, respectively. 

By combining these concepts, you can create different types of joins such as inner, left outer, right outer, and full outer joins. 

It's crucial to keep in mind that when you use the JOIN keyword without specifying the type of join, it will be treated as an INNER JOIN by default. To perform an outer join, you need to specify whether it's a RIGHT, LEFT, or FULL join. Moreover, it's worth noting that the INNER and OUTER keywords are optional with INNER being the default. 

For presentational purposes I created two DB tables (I use SQL Server and Azure Data Studio visual tool):

**_PaymentSystems Table:_**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ujj0ute5cijbili6kj4p.png)

**_Customers Table:_**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q7vbytgh5y8g1sbgw0ww.png)

Let's examine how the various types of joins can impact the outcome of a query using the example of these two tables. 

<u>**Inner type of joins**</u>

**1. INNER JOIN (or simply JOIN)**

The diagram for INNER JOIN, depicts the overlapping area between two circles, which represents the rows in each table that have a common value. When this type of join is used, only the rows that have matching values in both tables will be selected and returned. In other words, INNER JOIN returns only the data that is common to both tables.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/60fmqpuec250zvihil8k.png)

Let’s assume the following code: 

```
SELECT * FROM dbo.PaymentSystems S
INNER JOIN dbo.Customers C
ON S.Id = C.PaymentSystemId
```

Running the above code will produce the following result: 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fn5uf2u8isjlynlbo5on.png)

As shown in the result, the INNER JOIN did not include certain rows from the Customers table in the final output, as those rows did not have an Id that matched the condition specified in the ON clause: S.Id = C.PaymentSystemId.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e4kd3j0jt4dl70e75tiy.png)

<u>**Outer type of joins**</u>

**2. LEFT OUTER JOIN (or simply LEFT JOIN)**

It retrieves all the rows from the left table and any matching rows from the right table. If there are no matching rows in the right table, NULL values are used instead. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tcp6cbfrqa53e6qrkfir.png)

Let's execute this query and observe the result:

```
SELECT * FROM dbo.Customers C
LEFT JOIN dbo.PaymentSystems S
ON C.PaymentSystemId = S.Id
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v8gtj91rny1fl45ktrag.png)

As shown in the result, the LEFT OUTER JOIN retrieved all the rows from the Customers table, but only those rows from the PaymentSystems table that matched the condition specified in the ON clause: C.PaymentSystemId = S.Id. In cases where there was no match found, the missing values were replaced with NULL.

**3. RIGHT OUTER JOIN (or simply RIGHT JOIN)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6k9u8xl9qgh17lfpfa3i.png)

It returns all the rows from the right table and matching rows from the left table. If there are no matches in the left table, NULL values are returned. It's similar to LEFT OUTER JOIN, but the tables are reversed, with the right table being the primary table.

```
SELECT * FROM dbo.PaymentSystems S
RIGHT JOIN dbo.Customers C
ON S.Id = C.PaymentSystemId
```

The outcome of the query will be as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v5nye6eyte3jauvi434v.png)

The Customers table has been included completely, but PaymentSystems table is only included where it met the condition S.Id = C.PaymentSystemId.

**4. FULL OUTER JOIN (or simply FULL JOIN)**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j71tpbd160ddcg71lvjo.png)

It retrieves all rows from both tables, and if there are any unmatched rows, it fills in the columns with NULL values.

```
SELECT * FROM dbo.PaymentSystems S
FULL OUTER JOIN dbo.Customers C
ON S.Id = C.PaymentSystemId
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zq7x882qbr9qm8mk41xx.png)

As you can see, this code returns a result set that includes all the rows from both the PaymentSystems and Customers tables, regardless of whether there is a matching row in the other table. The result set will have NULL values in any columns where there is no match between the two tables. 

<u>**Cartesian type of joins**</u>

**5. CROSS JOIN**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f7ymrz2r5qwp7sbg1tz9.png)

A CROSS JOIN produces a result set that combines each row from the first table with each row from the second table, resulting in a [Cartesian product](https://en.wikipedia.org/wiki/Cartesian_product) of the two tables. It should be used with caution as it can generate a large number of rows and cause performance issues. Cross joins are appropriate when no other join can be used, and when the WHERE clause can limit the result set to a manageable size. Although a cross join doesn't make sense for my particular tables, we can still use it to illustrate how it works: 

```
SELECT TOP 14 * FROM  dbo.Customers C
CROSS JOIN dbo.PaymentSystems S
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r4kfom998aydwfa1o491.png)

The resulting output displays all the possible combinations of rows from the Customers and PaymentSystems tables, as generated by the cross join operation. As you can see, I have limited the output by using the TOP 14 clause in order to restrict the number of rows returned by the query.

<u>**No obvious category**</u>

**6. SELF JOIN**

There is no specific keyword in SQL for a self join. Self join doesn't belong to any of the traditional join categories such as inner, outer, left, right, or full outer join. It's a type of a instruction where a table is joined with itself. In other words, a self join is a regular join, but the table is used as both the left and right table in the join. This can be useful when you have a table that contains a hierarchy or a relationship between rows, and you need to join rows within the same table based on that hierarchy or relationship. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7gl0z7pammwr46v1usja.png)

A self join is utilised when a table needs to reference data within itself, for example: employees, multi-level marketing, machine parts, comment tree where each comment references the next one, and so on. 

The following example demonstrates a simple implementation of a self join with the help of LEFT JOIN:

```
SELECT *
FROM dbo.Employees E
LEFT JOIN dbo.Employees MA
ON MA.EmployeeId = E.ManagerId
```
Alternatively, you can also use the following query with a WHERE clause:

```
Select * FROM dbo.Employees E, dbo.Employees MA
WHERE MA.EmployeeId = E.ManagerId
```

Another way to obtain the same result is by using an INNER JOIN : 

```
SELECT * FROM dbo.Employees E
INNER JOIN dbo.Employees MA
ON MA.EmployeeId = E.ManagerId
```

The three queries shown above are examples of self-joins where the Employees table is joined with itself.

_In conclusion, SQL joins provide a powerful way to combine data from multiple tables. Understanding the differences between joins is essential for building complex queries and retrieving the data you need. By mastering SQL joins, you can unlock the full potential of your database and gain deeper insights into your data._