How to use LEFT, RIGHT, INNER, OUTER, FULL, and SELF JOINs in SQL? Example Tutorial

What are joins and why are they required?

Hello, everyone. Have you ever faced any issues while generating reports or loading data but were using data of just one table was not enough? One table's data is typically insufficient to provide important insights when doing sophisticated analytical processing and data discovery, therefore merging numerous tables is required. SQL, as a method for communicating with relational databases, allows you to create relationships between tables

.

This article explains how to join tables using SQL. If you want to learn more about the fundamentals of SQL, check out my first article on how to learn SQL in a common language. It provides a thorough introduction to SQL for complete novices.

Type of SQL JOINs

Left join, inner join, complete join, self join, and cross join are the other five primary join kinds. In order to connect with the database, we must provide the join type in the statement, either explicitly or implicitly. 

This is accomplished by using terms such as "LEFT JOIN," "INNER JOIN," and "FULL OUTER JOIN," among others. Each category has its own set of applications. Hopefully, the comparison chart below may assist you in recognizing their little variances.

To demonstrate different joins, we will use the below tables as an example.

1. LEFT JOIN

The right-hand table might be thought of as a reference table or a dictionary, from which we are extending the information in the left-hand table. As a result, a left join is used to retrieve all entries from the left table while referencing the values from the right table. 

Left join can also provide us more information about why some variables didn't match. E.g. Is it because of incomplete data in the right table, or is it because of inaccurate records or typos in the left table?

A LEFT JOIN query would look something like this : 

SELECT criteria_id, country_code, country_name, fips_code
FROM Google_Ads_GeoTargets gt
LEFT JOIN Country_Code cc
ON gt.country_code = cc.fips_code;

The result would look something like this : 

2. INNER JOIN

When we want to look at the intersection of two tables, we may use an inner join. Inner join generally returns the fewest rows since it only reveals the records that exist in both tables. All null values are filtered out, as indicated in the result below.

The INNER JOIN keyword is used to identify this sort of join:

SELECT criteria_id, country_code, country_name, fips_code
FROM Google_Ads_GeoTargets gt
INNER JOIN Country_Code cc
ON gt.country_code = cc.fips_code;

The result would look something like this : 

3. FULL JOIN

Whether or whether a match is discovered, a full outer join encompasses every row from both tables. It's utilized to get a complete picture of the data in both tables and spot any discrepancies. Because there is no match for fips code = "AZ" in the Country Code table, the first entry for country name and fips code is null in this case. 

The last row, on the other hand, has no criteria id and country code since the Google Ads GeoTargets database has no criteria with country code = "ZA."

A FULL JOIN query would look something like this : 

SELECT criteria_id, country_code, country_name, fips_code
FROM Google_Ads_GeoTargets gt
FULL OUTER JOIN Country_Code cc
ON gt.country_code = cc.fips_code;

The result would look something like this : 

4. RIGHT JOIN

Right joins are identical to left joins, except that the RIGHT JOIN clause returns all rows from the table, whereas the FROM clause returns just matching rows from the table. Because the results of a RIGHT JOIN may be achieved by just swapping the two connected table names in a LEFT JOIN, the RIGHT JOIN is rarely utilized.

A RIGHT JOIN query would look something like this : 

SELECT criteria_id, country_code, country_name, fips_code
FROM Google_Ads_GeoTargets gt
RIGHT JOIN Country_Code cc
ON gt.country_code = cc.fips_code;

Now, as you guys have already gone through the LEFT JOIN. Let's do an exercise and you guys can comment and give answers for the right join for the above statement. SO! what's the wait? Let's get your answers!

5. SELF JOIN

We use self join to handle unary relationships since it links the table to itself. The ability to build hierarchical connections, such as between employees and managers, categories and subcategories, and so on, is quite useful. In the end, it's still possible to perceive it as a way to connect two tables.

A SELF JOIN query would look something like this : 

SELECT e.employeeID AS employee ID, e.name AS name, m.name as manager
FROM Employee e
LEFT JOIN Employee m
ON e.managerID = m.employeeID

Consider the below table for explaining SELF JOIN. Consider an employee table as below : 

Now, the query explained above will yield the results as follows : 

Conclusion

The most important takeaway from this essay is that SQL joins can be broken down into three steps:

1. Choose the table and characteristics that you want to use.

2. determine the status of the join

3. Choose from left join, inner join, self join, and full join as the suitable join type.

I hope that by reading this post, you will be able to improve your fundamental SQL abilities and perform more complex analysis by merging tables.

SQL Joins is a very important topic not just for your day-to-day work but also for programming job interviews. You will also ways find one of two questions on SQL JOIN concepts during the interview. I have shared a few SQL join questions on my earlier article about SQL Query interview questions, you can also take a look at it after going through this article.