Understanding SQL Joins

As a beginner, I struggled with understanding SQL joins. I often felt confused about which type of join to use and when to apply it.

Here’s a beginner-friendly guide to help you understand SQL joins.

What Are SQL Joins and Why to use ?

In relational databases, SQL joins are a powerful tool used to combine data from multiple tables based on related columns. They allow you to create more complex queries and extract meaningful information from your data.

In simple terms, SQL joins allow you to retrieve data from multiple tables using a single query.

SQL joins form the backbone of relational database queries, offering the flexibility and power needed to extract meaningful insights from your data.

Types of Joins

There are primarily four types of joins in SQL:

1. Inner Join: Returns rows that have matching values in both tables.

2. Left Outer Join: Returns all rows from the left table, even if there are no matches in the right table.  

3. Right Outer Join: Returns all rows from the right table, even if there are no matches in the left table.

4. Full Outer Join: Returns all rows when there is a match in either left or right table.

These are commonly used joins there are another joins also like Self Join and Cross Join.

Let's Discuss each type with example of Employees and Departments

Inner Join

Inner Join only returns matching value in both Tables

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

You only see two rows because department IDs 101 and 102 exist in the Employees table. This is why the values match, and the query returns those results.

Left Outer Join

Left Join Returns all rows from the left table (Employees), even if there are no matches in the right table (Departments).

Essentially, the table you define first is referred to as the left table, and the table specified after the JOIN keyword is called the right table.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

All rows from Employees are returned. For Charlie and David, there is no matching department, so NULL is displayed in place of the department name.

Right Outer Join

A right join returns all rows from the right table Departments, regardless of whether there are matches in the left table Employees.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

All rows from the Departments table are returned. For the "Marketing" department, since there is no corresponding employee, NULL is shown in place of the employee name.

Full Outer Join

A full outer join returns all rows when there is a match in either the left table Employees or the right table Departments.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

All rows from both the Employees and Departments tables are returned. If there is no match, NULL is displayed for the missing data, such as for Charlie, David, and the "Marketing" department.

Try applying these examples to your tables to see how you can optimize your queries.

I hope you enjoyed the blog! I’ll be uploading a post soon about relationships in SQL, so stay tuned!