Understanding SQL Joins Through Simple Examples

Sometimes, you have data that is stored in different tables, but you want to see it together. For example, you might have a table of employees and a table of projects, and you want to see which employee is working on what project. To do this, you can use joins. Joins are a way to connect data from different tables based on some common values, like IDs or names. By using joins, you can get more information from your data and answer more questions.

In SQL, there are primarily four basic types of joins, which can be specified using the JOIN keyword. These are:

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)

However, when considering the broader usage and variations, including how joins are implemented or described, you might encounter additional terms or concepts that are often discussed in the context of SQL joins. These include:

CROSS JOIN
SELF JOIN
NATURAL JOIN
EQUI JOIN

Let me explain some of these terms to you. A join is a way to combine data from different tables based on some common values. There are different kinds of joins, but some of them are not really separate types, just different ways to use the same type. For example, a SELF JOIN is when you join a table with itself, using any kind of join. An EQUI JOIN is when you use equal signs to match the values in the join, which you can do with either INNER JOIN or OUTER JOIN. An INNER JOIN is when you only get the rows that match in both tables, and an OUTER JOIN is when you get all the rows from one table and some from the other.

Let's understand basic JOINS with some examples:

INNER JOIN: An INNER JOIN is a way to combine data from two tables based on some common values. It’s the most popular way to do this. When you use the word JOIN without saying what kind, it means INNER JOIN. An INNER JOIN only shows you the data that matches in both tables. If there is some data in one table that doesn’t have a match in the other table, it won’t show up.
```
    SELECT employees.first_name, projects.project_name
    FROM employees
    INNER JOIN projects ON employees.employee_id = projects.employee_id;
```
This example shows you how to use an INNER JOIN. It gets the first names of employees and the names of projects they are working on. It does this by joining two tables: employees and projects. The common value is the employee_id, which is the same in both tables. This means it only shows you the employees who have projects (i.e., there is a matching employee_id in both tables). Each line in the result will have an employee’s first name and a project name.
LEFT JOIN (or LEFT OUTER JOIN): A LEFT JOIN is a way to combine data from two tables based on some common values, but it also shows you the data that doesn’t match in the right table. It’s like saying, “show me everything from the left table, and if there is something related in the right table, show me that too. If not, just leave it blank.”
```
    SELECT employees.first_name, projects.project_name
    FROM employees
    LEFT JOIN projects ON employees.employee_id = projects.employee_id;
```
This example shows you how to use a LEFT JOIN. It gets the first names of employees and the names of projects they are working on from two tables: employees and projects. It uses a LEFT JOIN to show you all employees, even the ones who don’t have any projects. If an employee has no project, the project name will be null in the result. The common value is the employee_id, which is the same in both tables. This means it only shows you the projects that belong to the employees.
RIGHT JOIN (or RIGHT OUTER JOIN): A RIGHT JOIN is a way to combine data from two tables based on some common values, but it also shows you the data that doesn’t match in the left table. It’s like saying, “show me everything from the right table, and if there is something related in the left table, show me that too. If not, just leave it blank.”
```
    SELECT employees.first_name, projects.project_name
    FROM employees
    RIGHT JOIN projects ON employees.employee_id = projects.employee_id;
```
This example shows you how to use a RIGHT JOIN. It gets the first names of employees and the names of projects they are working on from two tables: employees and projects. It uses a RIGHT JOIN to show you all projects, even the ones that don’t have any employees. If a project has no employee, the employee’s first name will be null in the result. The common value is the employee_id, which is the same in both tables. This means it only shows you the employees that are assigned to the projects.
FULL JOIN (FULL OUTER JOIN): A FULL JOIN is a way to combine data from two tables based on some common values, but it also shows you the data that doesn’t match in either table. It’s like saying, “show me everything from both tables, and if there is something related in both tables, show me that too. If not, just leave it blank.”
```
    SELECT employees.first_name, projects.project_name
    FROM employees
    FULL OUTER JOIN projects ON employees.employee_id = projects.employee_id;
```
This example shows you how to use a FULL JOIN. It gets the first names of employees and the names of projects they are working on from two tables: employees and projects. It uses a FULL JOIN to show you all the data from both tables. This means it will show you all employees, even the ones who have no projects, and all projects, even the ones that have no employees. If there is no match between the employee_id in the employees table and the employee_id in the projects table, the result will have nulls for the data that is missing. This type of join makes sure that no data from either table is left out from the result, giving you a complete picture of both employees and projects, no matter if they are connected or not.

Conclusion

When you have data in different tables, you need to know how to join them together to get the information you want. There are different ways to join data, and each one has a different use. By using the right join for your question, you can get the best answer from your data.