Types of SQL Joins

SQL joins might appear daunting, especially for newcomers. If programming is unfamiliar, comprehending SQL joins and the types of SQL joins could be challenging. Yet, as a data science enthusiast or practitioner, understanding these joins is crucial. They empower efficient data retrieval and manipulation across tables. This article simplifies SQL joins’ learning curve, unveiling their simplicity. In this article you will get to know all about the SQL Join and types of SQL joins.

What are SQL Joins?

SQL Joins are used to combine rows from two or more tables based on a related column between them, allowing retrieval of data from multiple tables in a single query. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Also Read: Top 10 SQL Projects for Data Analysis

Different Types Of Joins in SQL

• INNER JOIN
• RIGHT JOIN
• LEFT JOIN
• FULL JOIN
• Natural Join

Want to learn the basics of what SQL is and how it can be applied in data science? Check out the popular course SQL for Data Science.

SQL INNER JOIN

Returns rows only when there is a match in both tables, based on a common column. If there are records in one table that do not have matches in the other table, there would be no result set

This is what we covered in the above section. Inner Join returns records that have matching values in both tables:

Let’s see what the output is using the above example:

SELECT * FROM BOYS INNER JOIN MESSI
ON BOYS.id = MESSI.id;

Output:

The inner join gives the intersection of two tables, i.e. rows which are common in both the tables.

Right or Outer in SQL

Returns all rows from the right table, and the matching rows from the left table. If there is no match in the left table, NULL values are returned for the columns of the left table

Suppose we want ID and Name of all the people who love watching Messi play. Obviously, there are many ways of writing this query but we’ll understand with the help of joins.

Let’s see what the output is:

SELECT  *  FROM BOYS RIGHT JOIN MESSI 
ON BOYS.id = MESSI.id;

Output:

Can you figure out what happened here? The right outer join gives us the rows that are common in both the tables as well as extra rows from the Messi table which are not present in the intersection. In other words, a right join returns all records from the right table and the matched records from the left table.

Left Join in SQL

Returns all rows from the left table, and the matching rows from the right table. If there is no match in the right table, NULL values are returned for the columns of the right table

Let’s say we want the list of all the boys who love watching Messi play as well as not love watching Messi play using joins.

I want you to guess the final output before you read further.

SELECT  *  FROM BOYS LEFT JOIN MESSI
ON BOYS.id = MESSI.id;

OUTPUT:

The left outer join gives us the rows that are common in both the tables as well as extra rows from the Boys table which are not present in the intersection. In other words, a left join returns all records from the left table and the matched records from the right table.

Full Join in SQL

Combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side

Finally, let’s say we want the list of all the people, including boys who love watching Messi play.

SELECT  *  FROM BOYS FULL OUTER JOIN MESSI
ON BOYS.id = MESSI.id;

Output:

Perfect! A full outer join gives us the rows that are common in both the tables as well as extra rows from both tables which are not present in the intersection. We get all records when there is a match on either the left or the right table.

NATURAL JOIN

Matches columns with the same name in both tables. The common columns on which the tables are joined must have the same name and data type across the tables.

Assuming both tables have a column named CustomerID with the same data type:

SELECT *
FROM Customers
NATURAL JOIN Orders;

Output:

When to Use What?

Situation: You’re managing a database for an online store, and you want to analyze sales data along with customer information. The goal is to identify which products are popular among different customer segments.

Explanation (INNER JOIN) 

Use an INNER JOIN when you want to match records that exist in both tables. In this scenario, you’ll retrieve sales data along with customer information for customers who have made purchases.

Example:

SELECT customers.name, products.product_name, sales.quantity

FROM customers

INNER JOIN sales ON customers.id = sales.customer_id

INNER JOIN products ON sales.product_id = products.id;

Explanation (LEFT JOIN) 

Use a LEFT JOIN when you want to retrieve all records from the left table and matching records from the right. This is useful to see which products have been purchased and by whom, including customers who haven’t made any purchases.

Example:

SELECT customers.name, products.product_name, sales.quantity

FROM customers

LEFT JOIN sales ON customers.id = sales.customer_id

LEFT JOIN products ON sales.product_id = products.id;

Explanation (RIGHT JOIN) 

Use a RIGHT JOIN when you want to retrieve all records from the right table and matching records from the left. This can be helpful to identify products that have been purchased and by whom, including products that haven’t been purchased by any customer.

Example:

SELECT customers.name, products.product_name, sales.quantity

FROM customers

RIGHT JOIN sales ON customers.id = sales.customer_id

RIGHT JOIN products ON sales.product_id = products.id;

Explanation (FULL JOIN)

Use a FULL JOIN when you want to retrieve all records from both tables, including products that have been purchased and by whom, and also customers who haven’t made any purchases.

Example:

SELECT customers.name, products.product_name, sales.quantity

FROM customers

FULL JOIN sales ON customers.id = sales.customer_id

FULL JOIN products ON sales.product_id = products.id;

Select the appropriate join type based on your analysis goals and the relationship between tables to obtain the desired insights from your data.

Conclusion

In conclusion, mastering SQL joins opens the door to powerful data manipulation and analysis capabilities. Whether you’re a budding data enthusiast or a seasoned analyst, understanding the nuances of INNER, LEFT, RIGHT, and FULL joins empowers you to extract valuable insights from complex relational databases. By seamlessly combining data from multiple tables, you gain the ability to unravel intricate relationships, spot trends, and make informed decisions. Embrace the flexibility of SQL joins to efficiently explore your data’s potential, turning raw information into actionable knowledge. With this fundamental skill in your toolkit, you’re well-equipped to navigate the intricate landscape of database management and elevate your data-driven journey.

Do you want to learn how SQL can be used in data science? We highly recommend checking out this amazing course – Structured Query Language (SQL) for Data Science.

Frequently Asked Questions

Alakh Sethi

Aspiring Data Scientist with a passion to play and wrangle with data and get insights from it to help the community know the upcoming trends and products for their better future.With an ambition to develop product used by millions which makes their life easier and better.